VBA to use sum formula for cells in same row. Row number will change with each loop

Question

I'm using VBA to extract numbers from a website using a loop. I have these numbers imported into column F and G. I'm trying to include a line in my VBA that would allow me to use the sum formula to add these two cells and post the result in column E. The numbers will always be in the same row, but in columns F and G.

Can i use the (ActiveCell.Row) function in this way? or should just use an If function at the end of my loop.

Range("E" & (ActiveCell.Row)).Formula = "=SUM("Range("F" & (ActiveCell.Row)) & Range("G" & (ActiveCell.Row))")"

Why not the activecell.row method? is there a potential for the code to fail? — TPJ87, Nov 24 '13 at 23:02
`Why not the activecell.row method? is there a potential for the code to fail? – TPJ87 1 min ago` [INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 24 '13 at 23:04

score 3 · Answer 1 · answered Nov 24 '13 at 22:31

3

I have these numbers imported into column F and G.

If you want to insert the formula in Col E then use this. This will enter the formula in all the relevant cells in Col E in one go.

See this example

Sub Sample()
    Dim lRow As Long
    Dim ws As Worksheet

    '~~> Change this to the releavnt sheet name
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find Last row in col E which has data
        lRow = .Range("E" & .Rows.Count).End(xlUp).Row

        '~~> This will put the formula from E1 to E last row
        .Range("E1:E" & lRow).Formula = "=SUM(F1+G1)"
    End With
End Sub

answered Nov 24 '13 at 22:31

Siddharth Rout

147,039
17
206
250

1

neat elegant VBA (which can be a challenge). +1 – hammus Nov 24 '13 at 22:33
@leemo: Thanks :) But why can it be a challenge? – Siddharth Rout Nov 24 '13 at 22:34
1

@SiddarthRout You'll probably have more experience than me, but I always find VBA a difficult language to keep neat – hammus Nov 24 '13 at 22:35
My data begins on row 4. I changed E1 to E4 and F1+G1 to F4+G4. For some reason this method keeps starting at row 3 with the correct formula. – TPJ87 Nov 24 '13 at 22:52
1

Check what is the value of `lRow` ? – Siddharth Rout Nov 24 '13 at 22:54
Nevermind I just got it. My header was messing with the row count. And since I wanted E to be left blank unless there was data in column F and G. I Changed the "E" in the lRow definition to "F" – TPJ87 Nov 24 '13 at 22:58

hammus · Accepted Answer · 2013-11-24T22:38:37.050

1

In the forumla dont use Range() as it will not return a String. And also, you forgot the operator, you need to add "+" or ":" into the Forumla:

Range("E" & (ActiveCell.Row)).Formula = "=SUM(F" & ActiveCell.Row & "+" & "G" & ActiveCell.Row & ")"

Otherwise you'll need to access the Address method on the Ranges in the forumla (which is a waste because you have entered the address anyway)

but for clarity the code below demonstrates what I mean:

Range("E" & (ActiveCell.Row)).Formula = "=SUM(" & Range("F" & (ActiveCell.Row)).Address(False,False) & ":" & Range("G" & (ActiveCell.Row)).Address(False,False) & ")"

edited Nov 24 '13 at 22:38

answered Nov 24 '13 at 22:26

hammus

2,602
2
19
37

When i input this I get an error saying Compile error:expected end of statement. and it highlights Range right after "=Sum(" – TPJ87 Nov 24 '13 at 22:29
Range("E" & (ActiveCell.Row)).Formula = "=SUM("F" & ActiveCell.Row & "+" & "G" & ActiveCell.Row)" Now it highlights the "F" with the same error message – TPJ87 Nov 24 '13 at 22:40
1

@TPJ87 thats because you have an extra quote before the F. Remove the the quote mark immediately before the F. – hammus Nov 24 '13 at 22:41
Cool! thank you, This was definitely the answer I was looking for. – TPJ87 Nov 24 '13 at 23:02

VBA to use sum formula for cells in same row. Row number will change with each loop

2 Answers2