VBA Sumif change reference range

[xcelnovice]

Hi below is my code. How Can I change this to not only look at B5 but move down & look at B6 & B7 and so on.

Sub Sumif()
 
Dim SumRng1 As Range
Set SumRng1 = Sheets("Sheet1").Range("B:B")
 
Dim SumRng2 As Range
Set SumRng2 = Sheets("Sheet1").Range("E:E")
 
Dim SumRef1 As Range
Set SumRef1 = Sheets("Sheet2").Range("B5")
 
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
 
Dim i As Integer
 
 
For i = 5 To 8
    
     ws.Cells(i, 4) = Application.WorksheetFunction.Sumif(SumRng1, SumRef1, SumRng2)
    
    Next
    
     
End Sub

 

[JoeMo]

Maybe:

Sub Sumif()
 
Dim SumRng1 As Range
Set SumRng1 = Sheets("Sheet1").Range("B:B")
 
Dim SumRng2 As Range
Set SumRng2 = Sheets("Sheet1").Range("E:E")
 
Dim SumRef1 As Range

 
Dim ws As Worksheet
Set ws = Sheets("Sheet2")
 
Dim i As Integer
 
 
For i = 5 To 8
    Set SumRef1 = Sheets("Sheet2").Range("B" & i)
    
     ws.Cells(i, 4) = Application.WorksheetFunction.Sumif(SumRng1, SumRef1, SumRng2)
    
    Next
    
     
End Sub

 

[xcelnovice]

Awesome Joe! Works great. Thank you

 

[JoeMo]

You are welcome - thanks for the reply.