IF entire range blank THEN ...
 

I end a macro by enableing the AutoFilter so I can stop and check for my
ERROR message. If there is no cell with my error value, then I proceed to
the next step. It has just dawned on me that I can probably code this so
that
IF there is no value in any cell in B2:B700 THEN do this ELSE exit sub

I tried writing
' B2:B7000 is my range; C2 is an empty cell
IF (COUNTIF($B$2:$B$7000,C2)=0 THEN
' next routine
ELSE Exit Sub

but it keeps telling me $ is an invalid character. What am I missing?

Ed

IF entire range blank THEN ...
 

The syntax would be:
IF Application.COUNTIf(Range("B2:B700"),Range("C2"))= 0 THEN
' next routine
ELSE
Exit Sub
End if

but I would use CountA

IF Application.COUNTA(Range("B2:B700"))=0 THEN
' next routine
ELSE
Exit Sub
End if

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
I end a macro by enableing the AutoFilter so I can stop and check for my
ERROR message. If there is no cell with my error value, then I proceed to
the next step. It has just dawned on me that I can probably code this so
that
IF there is no value in any cell in B2:B700 THEN do this ELSE exit sub

I tried writing
' B2:B7000 is my range; C2 is an empty cell
IF (COUNTIF($B$2:$B$7000,C2)=0 THEN
' next routine
ELSE Exit Sub

but it keeps telling me $ is an invalid character. What am I missing?

Ed

IF entire range blank THEN ...
 

Thank you, Tom. It works great!

As for the $ as an invalid character, is that just the dirfference between
writing a formula, and writing VBA? In other words, I shouldn't assume that
just because a formula in a worksheet is good, that's what VBA will
recognize in the same way?

Ed


"Tom Ogilvy" wrote in message
...
The syntax would be:
IF Application.COUNTIf(Range("B2:B700"),Range("C2"))= 0 THEN
' next routine
ELSE
Exit Sub
End if

but I would use CountA

IF Application.COUNTA(Range("B2:B700"))=0 THEN
' next routine
ELSE
Exit Sub
End if

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
I end a macro by enableing the AutoFilter so I can stop and check for my
ERROR message. If there is no cell with my error value, then I proceed
to
the next step. It has just dawned on me that I can probably code this
so
that
IF there is no value in any cell in B2:B700 THEN do this ELSE exit sub

I tried writing
' B2:B7000 is my range; C2 is an empty cell
IF (COUNTIF($B$2:$B$7000,C2)=0 THEN
' next routine
ELSE Exit Sub

but it keeps telling me $ is an invalid character. What am I missing?

Ed

IF entire range blank THEN ...
 

In VBA, you need to pass a range object when a range is need in a formula.
In a worksheet, the functions understand A1:B9 refers to a range - in VBA
you have to use a range object.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
Thank you, Tom. It works great!

As for the $ as an invalid character, is that just the dirfference between
writing a formula, and writing VBA? In other words, I shouldn't assume
that
just because a formula in a worksheet is good, that's what VBA will
recognize in the same way?

Ed


"Tom Ogilvy" wrote in message
...
The syntax would be:
IF Application.COUNTIf(Range("B2:B700"),Range("C2"))= 0 THEN
' next routine
ELSE
Exit Sub
End if

but I would use CountA

IF Application.COUNTA(Range("B2:B700"))=0 THEN
' next routine
ELSE
Exit Sub
End if

--
Regards,
Tom Ogilvy


"Ed" wrote in message
...
I end a macro by enableing the AutoFilter so I can stop and check for
my
ERROR message. If there is no cell with my error value, then I
proceed
to
the next step. It has just dawned on me that I can probably code this
so
that
IF there is no value in any cell in B2:B700 THEN do this ELSE exit sub

I tried writing
' B2:B7000 is my range; C2 is an empty cell
IF (COUNTIF($B$2:$B$7000,C2)=0 THEN
' next routine
ELSE Exit Sub

but it keeps telling me $ is an invalid character. What am I missing?

Ed

IF entire range blank THEN ...
 

Thanks, Tom. I appreciate the info.

Ed


"Tom Ogilvy" wrote in message
...
In VBA, you need to pass a range object when a range is need in a formula.
In a worksheet, the functions understand A1:B9 refers to a range - in VBA
you have to use a range object.

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
Thank you, Tom. It works great!

As for the $ as an invalid character, is that just the dirfference
between
writing a formula, and writing VBA? In other words, I shouldn't assume
that
just because a formula in a worksheet is good, that's what VBA will
recognize in the same way?

Ed
<SNIP
I tried writing
' B2:B7000 is my range; C2 is an empty cell
IF (COUNTIF($B$2:$B$7000,C2)=0 THEN
' next routine
ELSE Exit Sub

but it keeps telling me $ is an invalid character. What am I
missing?