Help with VBA Code
 

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

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

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

In article ,
"Pank" wrote:

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, JE,

Worked a treat as expected.

Many thanks for the quick responses.

Regards

Pank

"JE McGimpsey" wrote:

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

In article ,
"Pank" wrote:

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