Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Blank Cells in a Range in Excel
Hi All, I'm new here and to the wonderful world of coding in VB so please go easy on me! I am trying to teach myself a few bits and pieces that may come in handy when I have to write code "for real", and I seem to be stuck on what is probably a really basic problem! When a link or a command button is pressed, I want it to look at a certain range and see if any of the cells in the range are blank. If they are, even if it is just one, I want a message box to appear saying something like "More Data Needed", but if all have data in, I want a macro to run. The range I would need to look at is B15:K15. Any help in the most basic form so my tiny brain can handle it would be very much appreciated. Many thanks in advance, T8RSP -- T8RSP ------------------------------------------------------------------------ T8RSP's Profile: http://www.excelforum.com/member.php...o&userid=28452 View this thread: http://www.excelforum.com/showthread...hreadid=480546 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Blank Cells in a Range in Excel
Hi T8RSP,
Try assigning the following code to the button: '============ Sub Tester() Dim rng As Range Set rng = ActiveSheet.Range("B15:K15") If Application.CountA(rng) = rng.Count Then 'All cells have entries, so run the needed macro: Call YourMacro Else MsgBox "One or more cells is empty!" End If End Sub '<<============ --- Regards, Norman "T8RSP" wrote in message ... Hi All, I'm new here and to the wonderful world of coding in VB so please go easy on me! I am trying to teach myself a few bits and pieces that may come in handy when I have to write code "for real", and I seem to be stuck on what is probably a really basic problem! When a link or a command button is pressed, I want it to look at a certain range and see if any of the cells in the range are blank. If they are, even if it is just one, I want a message box to appear saying something like "More Data Needed", but if all have data in, I want a macro to run. The range I would need to look at is B15:K15. Any help in the most basic form so my tiny brain can handle it would be very much appreciated. Many thanks in advance, T8RSP -- T8RSP ------------------------------------------------------------------------ T8RSP's Profile: http://www.excelforum.com/member.php...o&userid=28452 View this thread: http://www.excelforum.com/showthread...hreadid=480546 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Blank Cells in a Range in Excel
Hi T8,
Please try this fucntion Sub RangeCheck() Dim rngCell As Range For Each rngCell In Range("B15:K15") If IsEmpty(rngCell.Value) Then MsgBox "More Data Neeeded" Exit Sub End If Next rngCell End Sub Call this function in the OnClick event og your command button Thanks Xcelion "T8RSP" wrote: Hi All, I'm new here and to the wonderful world of coding in VB so please go easy on me! I am trying to teach myself a few bits and pieces that may come in handy when I have to write code "for real", and I seem to be stuck on what is probably a really basic problem! When a link or a command button is pressed, I want it to look at a certain range and see if any of the cells in the range are blank. If they are, even if it is just one, I want a message box to appear saying something like "More Data Needed", but if all have data in, I want a macro to run. The range I would need to look at is B15:K15. Any help in the most basic form so my tiny brain can handle it would be very much appreciated. Many thanks in advance, T8RSP -- T8RSP ------------------------------------------------------------------------ T8RSP's Profile: http://www.excelforum.com/member.php...o&userid=28452 View this thread: http://www.excelforum.com/showthread...hreadid=480546 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Blank Cells in a Range in Excel
One mo
Option Explicit Sub testme01() Dim myRng As Range Set myRng = ActiveSheet.Range("b15:k15") If myRng.Cells.Count < Application.CountA(myRng) Then MsgBox "More data needed in: " & myRng.Address(0, 0) Exit Sub '?? End If End Sub T8RSP wrote: Hi All, I'm new here and to the wonderful world of coding in VB so please go easy on me! I am trying to teach myself a few bits and pieces that may come in handy when I have to write code "for real", and I seem to be stuck on what is probably a really basic problem! When a link or a command button is pressed, I want it to look at a certain range and see if any of the cells in the range are blank. If they are, even if it is just one, I want a message box to appear saying something like "More Data Needed", but if all have data in, I want a macro to run. The range I would need to look at is B15:K15. Any help in the most basic form so my tiny brain can handle it would be very much appreciated. Many thanks in advance, T8RSP -- T8RSP ------------------------------------------------------------------------ T8RSP's Profile: http://www.excelforum.com/member.php...o&userid=28452 View this thread: http://www.excelforum.com/showthread...hreadid=480546 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the position of the i-th non blank cell in a vertical range | Excel Worksheet Functions | |||
Finding the bottom non-blank cell in a range | Excel Discussion (Misc queries) | |||
Finding NExt Blank Cell in Range | Excel Programming | |||
Finding Non-Blank Cells | Excel Programming | |||
finding blank cells in a row in Excel 97 | Excel Programming |