CODEWITHSUNDEEP
excelloopsvba
A. H.
A. H.

Reputation: 5

Looping different ranges of cell in Excel using macros

I am trying to loop a different set of ranges on an excel sheet. This is my sheet - Example sheet

enter image description here

When I run the macro, I want it to show up 4,6,12 and 14 in column E and F. This is my code. 

Sub auto_updateTest()
Dim rng11 As Range, cell11 As Range
Dim rng12 As Range, cell12 As Range

    Set rng11 = Range("E3:F4")
    Set rng12 = Range("A3:B4")
    For Each cell12 In rng12
        For Each cell11 In rng11
    cell11.Value = cell11.Value + cell12.Value

    Next cell11
    Next cell12


End Sub

What is the mistake here?

Upvotes: 0

Views: 33

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

Currently you are adding every cell in Rng12 to every cell in rng11. We want to loop once through one of the ranges and refer to the corresponding cell in the other.

Sub auto_updateTest()
Dim rng11 As Range, rng12 As Range
Dim i As Long, j As Long

With Worksheets("Sheet1") 'change to your sheet
    Set rng11 = .Range("E3:F4")
    Set rng12 = .Range("A3:B4")
    For i = 1 To rng11.Rows.Count
        For j = 1 To rng11.Columns.Count
            rng11.Cells(i, j).Value = rng11.Cells(i, j).Value + rng12.Cells(i, j).Value

        Next j
    Next i
End with

End Sub

Now with this small of a range it will be quick but as the ranges get larger, consider moving everything to memory arrays. This will limit the number of call to the worksheet and speed up larger number sets:

Sub auto_updateTest()
Dim rng11 As Variant, rng12 As Variant
Dim i As Long, j As Long

With Worksheets("Sheet1") 'change to your sheet
    rng11 = .Range("E3:F4").Value
    rng12 = .Range("A3:B4").Value
    If UBound(rng11, 1) <> UBound(rng12, 1) Or UBound(rng11, 2) <> UBound(rng12, 2) Then
        MsgBox "Ranges not the same size"
        Exit Sub
    End If
    For i = LBound(rng11, 1) To UBound(rng11, 1)
        For j = LBound(rng11, 2) To UBound(rng11, 2)
            rng11(i, j) = rng11(i, j) + rng12(i, j)

        Next j
    Next i
    .Range("E3:F4").Value = rng11

End With

End Sub

Upvotes: 1

Hrothgar
Hrothgar

Reputation: 422

Your inner loop (cell11) adds 1 to each value in rng11, and then the outer loop increments and the inner loop repeats. I calculate that E3 ends up as 17. Try using a single loop, and use offsets.

Upvotes: 0

Related Questions