Finding Blank Cells in a Range in Excel

[T8RSP]

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

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Xcelion]

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
>
>

[Dave Peterson]

One more:

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