Two of a kind !

vLookup + Average - Excel Macro

I had this wonderful problem that always appears easy with VBA for Excel but always has something different from normal problems !

The problem

For each order on the Orders table, compute the average of two consecutive items with the same article #.

At first glance, it looks like the vLookup will do and then you realize that an If... formula could be used in conjunction with vLookUp and it will work and only then it dawns on you that, hey, this is a logical problem and requires some serious coding !

This is usually the case with Excel problems that, because of the tabular nature of the data references, always seem likely candidates for simple formulas but then VBA has to be used even though macros always raise an eyebrow or two due to security reasons.

The Solution

Dim qu As Integer
Dim curVal As String
Sub AvgOfDeuxItems()
ctr = 0
rowctr = 3

curVal = Cells(rowctr, 6)
For i = 3 To 150
If Cells(rowctr, 6) = Cells(rowctr + 1, 6) Then
        qu = Cells(rowctr, 7) + Cells(rowctr + 1, 7)
        Cells(rowctr + 1, 8) = qu / 2
        qu = 0
            rowctr = rowctr + 2
    Else
    Cells(rowctr, 8) = Cells(rowctr, 7)
        rowctr = rowctr + 1
End If

Next
End Sub

The lookup for the Orders table is just another macro that runs the above macro upon an if...condition that compares the lookup values.