You need to have the variable outside of the string:
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTA(B2:B&" & lLastRow & ")"
--
Regards,
Juan Pablo González
Excel MVP
"Pank" wrote in message
...
I have the following as part of some VBA:-
lLastRow = wks.Range("A1").End(xlDown).Row
wks.Cells(lLastRow + 1, 1).Formula = "TOTAL VALUE"
wks.Cells(lLastRow + 1, 2).Formula = "=COUNTA(B2:B&1LastRow)"
The first line ascertains the last row that has a value.
The second line sets a formula in the last row + 1 (i.e. sets value to
TOTAL
VALUE).
The third line is intended to use the COUNTA function to count all rows
from
B2 to the last row.
When I run this I get, Run Time error 1004, Application defined or object
defined error.
Basically, all I the last line to do is effectively do something like
COUNTA(B2:B383). Please note that 383 will be a different value in
different
sheets.
Any help offered would be most appreciated.
Pank
|