PDA

View Full Version : VLOOKUP VBA FORMULA



Cuscino
03-27-2018, 10:47 AM
Hi Everyone, VBA noob here (and new to this forum)

I m looking for a particular VBA vlookup that seeks multiple values in one cell separated by a comma (name of the product eg. FHGH, RDSD, etc), in another file.

In this last one, we have: in the first column the values to find in each single row, but they´re incorporated in a full sentence (eg. FHGH Product 12 6volt)

I would like the formula to return to me the sum of the values that are in the second column (where there are the prices of each product)

I hope you could help me https://www.mrexcel.com/forum/images/smilies/icon_wink.gif

Thanks

SamT
03-28-2018, 11:22 AM
IN the workbook with with the list of multiple product names, Insert a Module.

PLace this code in that module
Option Explicit

Function TotalOfAllInCell(Cel As Range) As Currency

Const PriceWorkbookNames As String = "???" 'Edit to suit
Const PriceSheetName As String = "???" 'Edit to suit

Dim PriceSht As Worksheet
Dim Found As Range
Dim TotalPrice As Currency
Dim Products As Variant
Dim i As Long

Set PriceSht = Workbooks(PriceWorkbookNames).Sheets(PriceSheetName)

Products = Split(Cel, ",") 'The Product name separator is a comma. 'To make an array

If Not IsArray(Products) Then 'Only one Product name in Cel
Set Found = PriceSht.Columns("A").Find(Products) 'Edit to correct Product name column
If Found Is Nothing Then
TotalOfAllInCell = 0
Else
TotalOfAllInCell = Found.Offset(, 1)
Exit Function
End If

'More than one product in Cel
Else
For i = LBound(Products) To UBound(Products)
Set Found = PriceSht.Columns("A").Find(Trim(Products(i))) 'Trim to remove excess Spaces
If Not Found Is Nothing Then TotalPrice = TotalPrice + Found.Offset(, 1)
Next i
TotalOfAllInCell = TotalPrice
End If
End Function

In the cell of the Column that will contain the total prices of all the product in the Products Cell of the same Row, (I am assuming that the products column is in "A" and the first products cell is "A2") use this formula.

=TotalOfAllInCell(A2)Then copy it down as needed

Note that the Code does compile, but it is not tested against your data

Cuscino
04-09-2018, 01:03 AM
Great!
It works, but if I have the two file in two different directories How can I do it?

Thanks