Vlookup Looping through columns and rows returning sum in total column

**edp428** · 10-30-2018, 09:45 AM

First, I would like to thank anyone who can possibly assist me with the VBA for this spreadsheet.

The worksheet consists of 10 columns (A-J) and 13 rows (1-13). The first row is a header row that contains eight codes that I need to look up the value in a table and multiple it by the count in each of the cells below. The result of the vlookup for each column needs to be added together and return a total in column J. I have a formlua that works well, however, I would like to convert the formula to VBA and For Each loops confuse me every time. The formula is:

=SUM(VLOOKUP(B$1,$P$2:$Q$9,2,FALSE)*B2,VLOOKUP(C$1,$P$2:$Q$9,2,FALSE)*C2,VLOOKUP(D$1,$P$2:$Q$9,2,FALSE)*D2,VLOOKUP(E$1,$P$2:$Q$9,2,FALSE)*E2,VLOOKUP(F$1,$P$2:$Q$9,2,FALSE)*F2,VLOOKUP(G$1,$P$2:$Q$9,2,FALSE)*G2,VLOOKUP(H$1,$P$2:$Q$9,2,FALSE)*H2,VLOOKUP(I$1,$P$2:$Q$9,2,FALSE)*I2)

A sample workbook is attached.

Any help is greatly appriciated.

Thank you,
Eric

**Bernie Deitrick** · 10-30-2018, 10:38 AM

Here are two macros - one using your formula, the other using a loop:

Sub Macro1()
   With Range("J2:J13")
    .FormulaR1C1 = "=SUM(" & _
    "VLOOKUP(R1C[-8],R2C16:R9C17,2,FALSE)*RC[-8]," & _
    "VLOOKUP(R1C[-7],R2C16:R9C17,2,FALSE)*RC[-7]," & _
    "VLOOKUP(R1C[-6],R2C16:R9C17,2,FALSE)*RC[-6]," & _
    "VLOOKUP(R1C[-5],R2C16:R9C17,2,FALSE)*RC[-5]," & _
    "VLOOKUP(R1C[-4],R2C16:R9C17,2,FALSE)*RC[-4]," & _
    "VLOOKUP(R1C[-3],R2C16:R9C17,2,FALSE)*RC[-3]," & _
    "VLOOKUP(R1C[-2],R2C16:R9C17,2,FALSE)*RC[-2]," & _
    "VLOOKUP(R1C[-1],R2C16:R9C17,2,FALSE)*RC[-1])"
    .Value = .Value
    End With
End Sub

Sub Macro2()
    Dim c As Range
    Dim lngC As Long
    Dim lngR As Long
    Dim temp As Double
    
    For Each c In Range("J2:J13")
        temp = 0
        For lngC = 2 To 9
            temp = temp + Application.VLookup(Cells(1, lngC).Value, Range("P2:Q9"), 2, False) * Cells(c.Row, lngC).Value
        Next lngC
        c.Value = temp
    Next
    
End Sub

**edp428** · 10-30-2018, 10:44 AM

Macro 2 is perfect, thank you! I really appriciate your help.

**Bernie Deitrick** · 10-30-2018, 10:46 AM

Great! Happy that you liked it...

Vlookup Looping through columns and rows returning sum in total column

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Vlookup Looping through columns and rows returning sum in total column

Re: Vlookup Looping through columns and rows returning sum in total column

Re: Vlookup Looping through columns and rows returning sum in total column

Re: Vlookup Looping through columns and rows returning sum in total column

Thread Information

Users Browsing this Thread

Similar Threads

Looping through rows, finding 2 matching columns, returning the 3rd column as a variable

Looping through rows then columns

[SOLVED] Checking column for values and returning a running total.

[SOLVED] Vlookup column change. columns to rows

Looping Through Columns And Rows

Vlookup several columns then total a different column

[SOLVED] Looping through Columns then Rows

Bookmarks

Bookmarks

Posting Permissions