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
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