Thread: Another VLookup Question Across Multiple Worksheets

Hello Everyone,

I have been reading a book and searching the web, however, something little is missing from my code so far that I cant' seem figure out.

Objective:
Assume I have three worksheets, we will call them 1) Received 2) Vendor Prices 3) Retail Prices.

I am looking to go down the list of the 1)Received worksheet, and get the vendor code from that sheet, based on that, look it up and grab the price based on the code in the 2)Vendor Prices, and then transfer that price over the cost column in 3) Retail prices.

My initial plan is to use the vlookup function within a loop for the range of cells in the first column of the Received, save the value returned in a variable, and then a find and replace type custom function. I'm taking it step by step, but ran into a problem, it simply doesn't find anything, here is what I have so far...

Dim currentCellNumber As Integer
Dim currentCellNumberToString As String ' Needed to covert currentCellNumber to String
Dim currentCell As Variant             ' while preserving original number
Dim cellValue As String
Dim Cell As Object
Dim currentValue As Integer
Dim matchValue As Integer
Dim lookupCheck As Variant
currentCellNumber = 0

For Each Cell In Range("A1:A6")
    
    currentCellNumber = currentCellNumber + 1
    currentCellNumberToString = Trim(Str(currentCellNumber))
    currentCell = "A" + currentCellNumberToString
    cellValue = Range(currentCell).Value
  
   
    On Error Resume Next
    lookupCheck = Application.WorksheetFunction.VLookup(cellValue, Worksheets(Sheet2).Range("A1:D30"), 3, True)
    
   If Err.Number <> 0 Then
       'not found
      Else
       MsgBox ("Found it" + lookupCheck)
        'found it
    End If

   
Next Cell

I know it seems strange using the method I have created to go down the cells instead of using the offsets, and I feel like I had a good reason, although I have forgotten why and unfortunately didn't comment about it! I know that it goes through all the cells properly because when I created a message box in the loop after getting cellValue to display all the values and they are all correct.

Thank you so much for your time!

I wonder whether I have understood completely.

suppose the data is as in the attached file "strungmind.xls"
three sheets are there and see data in each of the sheet

try this macro

Sub test()
Dim r As Range, cfind As Range, code As String, price As Double
Dim cfind1 As Range
With Worksheets("received")
Set r = .Range("A2")
code = r.Value
With Worksheets("vendor").Columns("A:A")
Set cfind = .Cells.Find(what:=code, lookat:=xlWhole)
If Not cfind Is Nothing Then
price = cfind.Offset(0, 1)
With Worksheets("retail").Columns("A:A")
Set cfind1 = .Cells.Find(what:=code, lookat:=xlWhole)
If Not cfind Is Nothing Then
cfind1.Offset(0, 1) = price
Else
MsgBox "that code is not available in received sheet"
End If
End With 'retail
Else
MsgBox "that code isnot available in vendor sheet"
End If
End With 'vendor
End With 'received
End Sub

is this what you want

the code is "d" in "receied"
it finds this code in "vendor" and find its price
it finds this code in "retail" and copies the price in the appropriate location

That works perfectly!!! Thank you so much, now the only think I have left to do which I am working on at this moment is looping to go through the column of received to automatically do each one. I'm sure their are plenty of examples on the web, if I get that working correctly soon I'll post the full code up.

Thank you again so so so much!