Using worksheet functions in macros in Excel2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

clippan - 17 Nov 2005 23:31 GMT

Hello. I'm trying and trying to use a worksheet function in a macro. What Im
trying
to do is to calculate the number of empty cells in a range of cells in the
worksheet
and then use the answer to jump in the macro. To get the number in a worksheet
I guess I can use CountBlanks or CountIf. For example. =CountIf(B2:J10;"")

Now I'm trying to use this function in a macro and I'm trying to write
something like:

IF (Number of empty cells in range( ) ) = X THEN GOTO .....

I can't figure this out. I've tried writings like:

Dim ... As ......
.
.
IF Application.WorksheetFunction.CountBlank( Worksheets(1).Range("A1:A10"))
= X THEN GOTO ....
.
Nothing works. Lots and lots of different errors.

Frankly, I'm realazing that I'm no good at this. Could someone please write
down what I have to write in my macro and what kind of definitions I also
have to do like Dim ... As.... (if any)

Signature

Many thanks in advance !
Clippan

Reply to this Message

Bob Phillips - 17 Nov 2005 23:53 GMT

Try

If Application.countif(worksheets(1).range("B2:J10"),"")

or

If Application.countblank(worksheets(1).range("B2:J10"))

both worked for me

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> Hello. I'm trying and trying to use a worksheet function in a macro. What Im
> trying
[quoted text clipped - 21 lines]
> down what I have to write in my macro and what kind of definitions I also
> have to do like Dim ... As.... (if any)

Reply to this Message