COUNTA function causing syntax error in macro...
 

A colleague is using XL2003 and has used the following formulae as part of a
macro - he's trying to test for non blank cells in a dynamic range:

ExtractLength=(COUNTA('Blank'!$A:$A)-1)

It's causing a Compile Error:Syntax Error

Is this because the formulae as written contains a circular reference? How
best to advise - any help gratefully accepted; macros really aren't my strong
point!

Amanda

COUNTA function causing syntax error in macro...
 

Try this version:

ExtractLength= Application.COUNTA(Sheets("Blank").Range("A:A"))-1


"Birmangirl" wrote:

A colleague is using XL2003 and has used the following formulae as part of a
macro - he's trying to test for non blank cells in a dynamic range:

ExtractLength=(COUNTA('Blank'!$A:$A)-1)

It's causing a Compile Error:Syntax Error

Is this because the formulae as written contains a circular reference? How
best to advise - any help gratefully accepted; macros really aren't my strong
point!

Amanda

COUNTA function causing syntax error in macro...
 

Try something like this:

ExtractLength=(WorksheetFunction.CountA(Range("'Bl ank'!$A:$A"))-1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Birmangirl" wrote:

A colleague is using XL2003 and has used the following formulae as part of a
macro - he's trying to test for non blank cells in a dynamic range:

ExtractLength=(COUNTA('Blank'!$A:$A)-1)

It's causing a Compile Error:Syntax Error

Is this because the formulae as written contains a circular reference? How
best to advise - any help gratefully accepted; macros really aren't my strong
point!

Amanda

COUNTA function causing syntax error in macro...
 

I am not sure what your colleage is trying to do
but try
exact length = application.worksheetfunction.counta(('Blank'!$A:$ A)-1
counta() is not a VB function

"Birmangirl" wrote:

A colleague is using XL2003 and has used the following formulae as part of a
macro - he's trying to test for non blank cells in a dynamic range:

ExtractLength=(COUNTA('Blank'!$A:$A)-1)

It's causing a Compile Error:Syntax Error

Is this because the formulae as written contains a circular reference? How
best to advise - any help gratefully accepted; macros really aren't my strong
point!

Amanda

COUNTA function causing syntax error in macro...
 

Cheers Ron (and everyone else who responded so quickly). Your answer solved
the problem.

Amanda

"Ron Coderre" wrote:

Try something like this:

ExtractLength=(WorksheetFunction.CountA(Range("'Bl ank'!$A:$A"))-1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Birmangirl" wrote:

A colleague is using XL2003 and has used the following formulae as part of a
macro - he's trying to test for non blank cells in a dynamic range:

ExtractLength=(COUNTA('Blank'!$A:$A)-1)

It's causing a Compile Error:Syntax Error

Is this because the formulae as written contains a circular reference? How
best to advise - any help gratefully accepted; macros really aren't my strong
point!

Amanda