VBA complex vlookup between worksheets to get average of relative cells

Question

I have a workbook with 2 worksheets. On Sheet1 is a list of names in ColC, and on Sheet2 in column C is the same list of names, but spaced out with data in Column D relating to each name almost as a heading. i.e.

Ben         678

            700

            450

            200

Janet       9

            23

So I need a vlookup function to Look up the name in ColC Sheet1, and then find the corresponding name in ColC Sheet2, and do an average of the values for that name in ColD until the value in ColC changes (and the next name appears). The number of values in ColD per name changes between 1 and 100 so theres no set range.

(I'm looking for a solution to calculate the average of the last 6 values per name before the next appears - but I can try to modify that later on by myself once I have a structure.)

I am familiar with VBA but no expert, and this is just beyond my ability - I have tried a few things for a few hours and no luck. I have also this code that does a similar thing (I found it on a forum) but only pastes one value and I am not able to modify it enough to suit my needs - it uses VBA to put formulas in specific cells. (it's pretty useless but I thought it was a start)

Sub MCInternet()
 'CODE OFF WEB FOR RETURNING VALUE IN COL ... AFTER A LOOKUP OF VALUE IN RANGE - DOESNT ADDRESS  RANGE JUST SINGLE CELL

Dim Cll As Range
 Dim lngLastRow As Long
 lngLastRow = Cells(rows.count, "C:C").End(xlUp).Row
    'Sheets("Unpaid List").Range("H2:H" & lngLastRow).ClearContents

For Each Cll In Sheets("Sheet2").Range("C1:C" & Sheets("Sheet2").Range("C1").End(xlDown).Row)
     'Cll.Offset(, 6).Formula = "=Vlookup(" & Cll.Address & ", " & Sheets("Sheet1").Name & "!A:C,1,False)"
     Cll.Offset(, 6).Formula = "=Vlookup(" & Cll.Address & ", " & Sheets(Sheets.count).Name & "!A:C,1,False)"

Next Cll

End Sub

user3514930 user3514930 · Accepted Answer · 2014-08-07T10:53:38

I think it's better to define in a new module a Public Function like:

Public Function FindP(xx As Range) As Long
    Application.Volatile
    Dim FoundIndex
    Dim SumFound, i As Long

    Set FoundIndex = Sheets("Sheet2").Range("C:C").Find(xx.Value)
    If (FoundIndex Is Nothing) = True Then
        FindP = 0
        Exit Function
    Else
        SumFound = 0
        For i = 0 To 100
            If (FoundIndex.Offset(i, 0) = "") Or (FoundIndex.Offset(i, 0) = xx.Value) Then
                SumFound = SumFound + FoundIndex.Offset(i, 1).Value
            Else
                Exit For
            End If
        Next
        FindP = SumFound
    End If
End Function

and in every cells in the sheet1:

D1 -> =FindP(C1)

and autocomplete.
The function search in the column C of the sheet2 the name, after loop to sum every value if the name in column C it's equal (1st line) or empty (2nd ... n line).

VBA complex vlookup between worksheets to get average of relative cells

1 Answers