Hi

I get an error when I use RowNum=COUNTA(Sheet1!$A:$A)
"Invalid Character , Compile error", Does not like $.
When I remove the $ I get an error "Expected : List or
seperator )"

Tks again

-----Original Message-----
Hi

Define the number of rows as named value, i.e
RowNum=COUNTA(Sheet1!$A:$A)

In VBA, you can always refer to named value:
....
varNumberOfRows=[RowNum]
....

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Gerrym" wrote in
message
...
Can I set a variable using COUNTA to count non blank
rows
and then use the variable in a macro with
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],LCCUS!
R2C1:VARIABLE,2)"

I need this as the number of rows in the sheet LCCUS can
vary.

TKS


.