Feasibility - vlookup macro from multiple spreadsheets

[csutaria]

Hello Excel/VBA experts,

I am very inexperienced in VBA and wanted to run an idea across you to see if you could provide input on feasibility or to improve upon it with capabilities I may not be aware of. (I already looked, but if you can find something similar to this in another thread, please direct me there. And if you could provide sample code would be a huge bonus. )


There are two types of workbooks:

Workbook 1:

UID	Qty	Price
0001	15
0005	23
0013	12
0124	12

<tbody>
</tbody>

Workbook 2:

UID	Qty	Price
0001	3	1.25
0005	4	.05

<tbody>
</tbody>

What I'd like to do is for each row in workbook 1, do a vlookup for the corresponding UID in workbook 2 and return the price value. This part is pretty simple.

The parts I'm not sure about:

1) Let's say I had a folder full of workbooks like workbook 2. Can I get a macro to go through each of the workbooks and do the vlookup function? Meaning it would go to Row 1 of workbook 1, look up that UID in workbook 2a return a price. Go to row 2 of workbook 1, lookup that UID in workbook 2a, but not find anything. Then it would try to find the row 2 UID in workbook 2b (located in the same folder as 2a).

2) If there are multiple instances of the UID existing in the workbook 2 folder, can I get a macro to choose the lowest price value or overwrite the higher value that is already in workbook 1 price column?

Any help would be greatly appreciated, thanks in advance!

 

[csutaria]

This is what I've got so far...I open up the spreadsheet from where I want to pull the prices (Workbook 2). For each UID there are 7 different price columns, so it creates a vlookup formula for all 7 columns into Workbook 1.

Now for the hard part...

Sub vlookup()
Dim t As Integer
Dim r As Integer
Dim c As Integer


'Collecting info about currectly active sheet
    myfilename = ActiveWorkbook.Name
    mySheetName = "Purchasing"
    myRangeName = "$I$11:$DU$259"
    
Windows("POACTION8-22.xls TEST DO NOT USE.xlsm").Activate


For x = 0 To 13
    Range("T" & 1).Offset(0, x).Value = myfilename & " " & Workbooks(myfilename).Worksheets("Sales").Range("E" & (x + 21))
Next x


t = 9
        For c = 0 To 14
                For r = 2 To 30
                    Range("T" & r).Offset(0, c).Formula = "=VLOOKUP($N" & r & ",'[" & myfilename & "]" & mySheetName & "'!" & myRangeName & "," & t & ",FALSE)"
                Next r
            t = t + 8
        Next c
End Sub