VBA code to add cell programatically

Question

Hi and Thank as well as sorry for asking such a newbie question How do I create a VBA code for adding A1 + B1 = C1 then next row which is A2 + B2 = C2 then so on and so forth. Please help me. I know how to create formulas but this one i dont know. Any help will be greatly appreciated. Thanks. Just a simple macro is all I ned.

Here's had came up but to no avail.

Worksheets("Total").Activate
Set MyRange = Range("H3:H100")
For Each mycell in MyRange
H3 = A3+B3+C3
(so if the next loop it will look like H4 = A4+B4+C4 and so on)
Next mycell
End sub

score 1 · Answer 1 · edited May 23 '17 at 11:50

You don't need any loop here. There is more efficient way:

With Worksheets("Total").Range("H3:H100")
    .Formula = "=A3+B3+C3" ' or "=SUM(A3:C3)"
    .Value = .Value
End With

Explanation:

1) line .Formula = "=A3+B3+C3" writes formula in each cell. Excel automatically adjust this formula for each row:

in H3 formula would be =A3+B3+C3
in H4 formula would be =A4+B4+C4
.....................
in H100 formula would be =A100+B100+C100

You can also use this formula instead: .Formula = "=SUM(A3:C3)" - result would be the same.

2) line .Value = .Value rewrites formulae with their result values. If you want to leave formulae, you can remove this line.

If you're still interesting how to do the same thing using loop, look at the following code:

Dim MyRange As Range, c As Range

With Worksheets("Total")
    Set MyRange = .Range("H3:H100")
    For Each c In MyRange
        c.Value = .Range("A" & c.Row) + .Range("B" & c.Row) + .Range("C" & c.Row)
    Next mycell
End With

code above loops through each cell in range H3:H100 and calculates sum of corresponding values from columns A,B,C.
You can see how I do it here: .Range("A" & c.Row). Here c.Row returns row number of "current" cell in range H3:H100 and "A" & c.Row gives you A3 when c referce to H3, A4 - when c referce to H4 and so on.

You may also want to read this: How to avoid using Select/Active statements

VBA code to add cell programatically

1 Answers1