CODEWITHSUNDEEP
vbaexcelformula
Brett Nelson
Brett Nelson

Reputation: 113

Excel 2010 VBA formula with variable messing up

I have this code:

LastLine = Range("C" & Rows.Count).End(xlUp).Row
LastLine = "C" & LastLine
Range(LastLine).Select
ActiveCell.FormulaR1C1 = "=SUM(R4C3:R[-1]C)"
Range("E13").FormulaR1C1 = "=if(R12C5 - " & LastLine & " <> 0,R12C5 - " & LastLine & ","""")"

everything works expect for the formula the outcome for that is

"=IF($E$12 - $Z:$Z <> 0,$E$12 - $Z:$Z,"")"

Can anyone see what I'm doing wrong? also when I try

LastLine = Range(LastLine).address

It gives me an error

Upvotes: 0

Views: 80

Answers (2)

teylyn
teylyn

Reputation: 35915

The variable LastLine is not typed and you change its type throughout your code. First, it is a number (the number of the row), then you assign it text, the letter C combined with the row number, for example "C3". Next you use that in an R1C1 reference, where C3 means Column 3, so the row number from the first LastLine assignment will end up as the column in the formula.

Your code would be cleaner if your variables were dimmed to specific types and you would not mix A1 notation with R1C1.

Sub test()
Dim LastLine As Long
Dim LastLineCell As String

LastLine = Range("C" & Rows.Count).End(xlUp).Row
LastLineCell = "C" & LastLine
Range(LastLineCell).Select
ActiveCell.FormulaR1C1 = "=SUM(R4C3:R[-1]C)"
Range("E13").FormulaR1C1 = "=if(R12C5 - R" & LastLine & "C26 <> 0,R12C5 - R" & LastLine & "C26,"""")"
End Sub

Upvotes: 2

user3598756
user3598756

Reputation: 29421

Since you are using R1C1 notation you have to translate CsomeRow to RsomeRowC3

Range("E13").FormulaR1C1 = "=if(R12C5 - R" & LastLine & "C3 <> 0,R12C5 - R" & LastLine & "C3,"""")"

Upvotes: 1

Related Questions