CODEWITHSUNDEEP
arraysexcelvbaloopsforeach
Oliver Theseira
Oliver Theseira

Reputation: 57

Why does my For Each loop skip over some items in my array?

My full code is as below:

FirstRow = Columns("B").Find("B").MergeArea.Row
LastRow = Columns("B").Find("B").MergeArea.Row + Columns("B").Find("B").MergeArea.Rows.Count - 1

FirstRow2 = Columns("B").Find("N").MergeArea.Row
LastRow2 = Columns("B").Find("N").MergeArea.Row + Columns("B").Find("N").MergeArea.Rows.Count - 1

Range("A" & LastRow + 1).EntireRow.Insert
Range("A" & LastRow2 + 2).EntireRow.Insert

Range("Q" & LastRow + 1) = 50
Range("Q" & LastRow2 + 2) = 100

Dim Col, ColArr, CalcRow, CalcRowArr, FRow, FirstRowArr, LRow, LastRowArr
ColArr = Array("R", "W", "Y")
CalcRowArr = Array(LastRow + 1, LastRow2 + 2)
FirstRowArr = Array(FirstRow, FirstRow2 + 1)
LastRowArr = Array(LastRow, LastRow2 + 1)

For Each Col In ColArr
For Each CalcRow In CalcRowArr
For Each FRow In FirstRowArr
For Each LRow In LastRowArr

Range(Col & CalcRow).Formula = "=SUM(" & Col & FRow & ":" & Col & LRow & ")"

Next LRow
Next FRow
Next CalcRow
Next Col

Basically I am trying to convert these 6 lines of code:

Range("R" & LastRow + 1).Formula = "=SUM(R" & FirstRow & ":R" & LastRow & ")"
Range("W" & LastRow + 1).Formula = "=SUM(W" & FirstRow & ":W" & LastRow & ")"
Range("Y" & LastRow + 1).Formula = "=SUM(Y" & FirstRow & ":Y" & LastRow & ")"

Range("R" & LastRow2 + 2).Formula = "=SUM(R" & FirstRow2 + 1 & ":R" & LastRow2 + 1 & ")"
Range("W" & LastRow2 + 2).Formula = "=SUM(W" & FirstRow2 + 1 & ":W" & LastRow2 + 1 & ")"
Range("Y" & LastRow2 + 2).Formula = "=SUM(Y" & FirstRow2 + 1 & ":Y" & LastRow2 + 1 & ")"

into the array loop in my code above:

Dim Col, ColArr, CalcRow, CalcRowArr, FRow, FirstRowArr, LRow, LastRowArr
ColArr = Array("R", "W", "Y")
CalcRowArr = Array(LastRow + 1, LastRow2 + 2)
FirstRowArr = Array(FirstRow, FirstRow2 + 1)
LastRowArr = Array(LastRow, LastRow2 + 1)

For Each Col In ColArr
For Each CalcRow In CalcRowArr
For Each FRow In FirstRowArr
For Each LRow In LastRowArr

Range(Col & CalcRow).Formula = "=SUM(" & Col & FRow & ":" & Col & LRow & ")"

Next LRow
Next FRow
Next CalcRow
Next Col

However, my end result is skipping over the first items (FirstRow and LastRow) in FirstRowArr and LastRowArr respectively, meaning that my first set of 3 values ("R", "W", "Y" & LastRow + 1) becomes a duplicate of my second set of 3 values ("R", "W", "Y" & LastRow2 + 2).

Upvotes: 1

Views: 126

Answers (2)

Rory
Rory

Reputation: 34045

Per my earlier comment, you only really want two loops:

For Each Col In ColArr
   Dim n as long
   For n = lbound(calcrowarr) to ubound(calcrowarr)
      range(col & calcrowarr(n)).formula = "=SUM(" & Col & FirstRowArr(n) & ":" & Col & LastRowArr(n) & ")"
   next n
Next Col

Upvotes: 1

Naresh
Naresh

Reputation: 3034

Try this .. Edited your code a little. Instead of arrays and loops you can insert the formula in 4 lines starting from CR = LR + 1 using relative referenced formula (R1C1)

Sub Macro1()
Dim FR, LR, FR2, LR2, CR, CR2
FR = Columns("B").Find("B").MergeArea.Row
LR = Columns("B").Find("B").MergeArea.Row + _
    Columns("B").Find("B").MergeArea.Rows.Count - 1

FR2 = Columns("B").Find("N").MergeArea.Row
LR2 = Columns("B").Find("N").MergeArea.Row + _
    Columns("B").Find("N").MergeArea.Rows.Count - 1

Range("A" & LR + 1).EntireRow.Insert
Range("A" & LR2 + 2).EntireRow.Insert

Range("Q" & LR + 1) = 50
Range("Q" & LR2 + 2) = 100

CR = LR + 1
Range("R" & CR & ",W" & CR & ",Y" & CR).FormulaR1C1 = _
    "=SUM(R[" & FR - CR & "]C:R[" & LR - CR & "]C)"
CR2 = LR2 + 2
Range("R" & CR2 & ",W" & CR2 & ",Y" & CR2).FormulaR1C1 = _
    "=SUM(R[" & FR2 - CR2 & "]C:R[" & LR2 - CR2 & "]C)"

End Sub

Edit .. Added as per comment below.

Sub Macro1()

Dim findArr, FR, LR, CR, i As Long
findArr = Array("B", "N") 'Zero based array

For i = LBound(findArr) To UBound(findArr)

    FR = Columns("B").Find(findArr(i)).MergeArea.Row
    LR = Columns("B").Find(findArr(i)).MergeArea.Row + _
        Columns("B").Find(findArr(i)).MergeArea.Rows.Count - 1
    
    Range("A" & LR + 1).EntireRow.Insert
    Range("Q" & LR + 1) = 50 * (i + 1)
    
    CR = LR + 1
    Range("R" & CR & ",W" & CR & ",Y" & CR).FormulaR1C1 = _
        "=SUM(R[" & FR - CR & "]C:R[" & LR - CR & "]C)"

Next i
End Sub

Upvotes: 0

Related Questions