Home Sum every odd row in a single Column VBA
Reply: 1

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#
Absinthe Reply to 2017-12-07 18:18:12Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.323802 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO