Home Sum every odd row in a single Column VBA

# Sum every odd row in a single Column VBA

CptGoodar
1#
CptGoodar Published in 2017-12-07 15:33:35Z
 I have a code that searches an excel fill for the word distance, takes the value of the cell next to it, paste it into a new cell, then sums all the cells up. Which works great, but i now need to find a way to only sum ever even row number. Does that make sense? Sub Distance_Check() Dim DistanceCheck As String Dim DistanceNumber As String Dim DistanceSum As String Dim DistanceTotal As String DistanceCheck = MsgBox("Would you like to check the distance?", vbYesNo) If DistanceCheck = vbYes Then If IsArray(fname) Then Workbooks.OpenText fname(1) i = 1 findStr = "Distance" Set foundCel = Range("A:A").Find(what:=findStr) If Not foundCel Is Nothing Then firstAddress = foundCel.Address Do Range("J" & i).Value = foundCel.Offset(0, 1).Value Set foundCel = Range("A:A").FindNext(foundCel) i = i + 1 Loop While Not foundCel Is Nothing And foundCel.Address <> firstAddress End If Set wkbSourceBook = ActiveWorkbook DistanceNumber = i - 2 DistanceSum = WorksheetFunction.Sum(Range(Cells(2, 15), (Cells(DistanceNumber + 1, 15)))) DistanceTotal = DistanceSum / DistanceNumber If DistanceNumber = Cells(2, 12) Then MsgBox ("No error found wihin distance") Else MsgBox ("Error found with distance") End If Else End If Call Save_Data End Sub  Would the way youd go about this be using a for loop on the cells(DistanceNumber(j,+1)  Where j = 0, j = j +2 , Until j > DistanceNumber, Would that work? If so how would you go about it? Thanks
Absinthe
2#
 A quick way to step through a loop in the desired increments is to use the Mod operator which divides two numbers and returns any remainder (e.g. 7 mod 2 = 1, as two sixes fit into seven, leaving one). You can use the row property of the range you identify with the Find method, and since you want to jump by two the modulo should be zero: If foundcel.Row Mod 2 = 0 Then Range("J" & i).value = foundcel.Offset(0, 1).Value  That said, there is a 'built in' way to step through a loop if using a For loop like this For x = 2 to 10 Step 2 ' Do stuff Next x  You can also use this method to step backwards, e.g. For x = 100 to 0 Step -10 ' Do stuff backwards! Next x