| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Running a Macro that enters a function that returns #VALUE! | Excel Discussion (Misc queries) | |||
| User Defined Function Causing Problems | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| My first macro - doesnt work - function not recognised | Excel Worksheet Functions | |||
| Macro with Array Function Problems | Excel Worksheet Functions | |||
Linear Mode
