Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change case...help please | Excel Worksheet Functions | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions | |||
Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Opening a file with code without a set file name | Excel Discussion (Misc queries) |