Help with VBA Code

[Guest]

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

 

[Juan Pablo González]

You need to have the variable outside of the string:

wks.Cells(lLastRow + 1, 2).Formula = "=COUNTA(B2:B&" & lLastRow & ")"

 

[JE McGimpsey]

one way:

With wks
lLastRow = .Range("A1").End(xlDown).Row
.Cells(lLastRow + 1, 1).Value = "TOTAL VALUE"
.Cells(lLastRow + 1, 2).Formula = "=COUNTA(B2:B" & lLastRow & ")"
End With

 

[Guest]

Juan, JE,

Worked a treat as expected.

Many thanks for the quick responses.

Regards

Pank