Search code examples
excelvbanested-loopsdo-whilesumifs

VBA nested if-do-while to sum values

What I'm attempting to do:

Loop through 2 worksheets (same workbook), comparing 2 criteria (farm and batch). If criteria match, then sum the values in the 'No. of Samples" cells and paste to "Total Samples"

What actually happens:

When both criteria are met, the first 2 rows of values are summed and pasted to the correct cell. But only the first 2 rows. So the Do While is not... doing while. Or else it has reason to skip cells or exit the loop early. I suspect I have too many iterations going on. And Do Loops can easily devolve into infinite loops, as I have found out.

Also, my immediate window shows the first iteration in row 1815 and not 3. (s = row# in Source page)

New Sum: 190

s, Num Samples: 1815, 95

I'm not sure exactly where to go from here, any insight would be appreciated.

Sub ProjectDataCalcs()

Dim Src As Worksheet: Set Src = Sheets("Plate Analysis")
Dim Dest As Worksheet: Set Dest = Sheets("Project Analysis")

Dim Sum As Long
Sum = 0
Dim s As Long, d As Long, LR As long, LR2 As Long

LR = Dest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LR2 = Src.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    ' effectively a SUMIFS loop
        For s = 3 To LR2
        Sum = Src.Cells(s, "D").Value ' Number of Samples per Plate
        Debug.Print "s, Num Samples: " & s & ", " & (Sum)
         For d = 3 To LR
        'while batch = batch and farm = farm,
        'sum cell value (not add row count) number of samples per plate
         Do While Src.Cells(s, "H").Value = Dest.Cells(d, "E").Value And _
         Src.Cells(s, "I").Value = Dest.Cells(d, "D")

            'Add value in cell to existing value;
            'add Number of Samples in line to running total of samples
            Sum = Sum + Src.Cells(s, "D").Value

            Debug.Print "New Sum: " & (Sum)
            'Sum to Total Samples cell in col L
            Dest.Cells(d, "L").Value = Sum
            Exit Do
          Loop
         Next d
        Next s

End Sub

Source Page

Destination Page

Solution

  • I don't think you need anything more than the basic nested loop: (updated from your sample data)

    Sub ProjectDataCalcs()

    Dim Src As Worksheet, Dest As Worksheet
    Dim Sum As Long, batch, farm
    Dim srw As Long, drw As Long, LRD As Long, LRS As Long

    Set Src = Sheets("Plate Analysis")
    Set Dest = Sheets("Project Analysis")
    
    LRD = Dest.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LRS = Src.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            
    ' effectively a SUMIFS loop
    For drw = 3 To LRD
        batch = Dest.Cells(drw, "E").Value 'populate these outside the inner loop...
        farm = Dest.Cells(drw, "D").Value
        Sum = 0 'reset sum
        Debug.Print drw, "Batch", batch, "Farm", farm
        
        For srw = 3 To LRS
            If Src.Cells(srw, "H").Value = batch And Src.Cells(srw, "I") = farm Then
                Sum = Sum + Src.Cells(srw, "D").Value
            End If
        Next srw
        
        With Dest.Cells(drw, "L") 'add sum of any values found
            .Value = .Value + Sum
        End With
    Next drw
End Sub