Using worksheet functions in macros in Excel2000
 

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)


--
Many thanks in advance !
Clippan

Using worksheet functions in macros in Excel2000
 

Try

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

or

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

both worked for me

--

HTH

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


"clippan" wrote in message
...
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)


--
Many thanks in advance !
Clippan