• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PLEASE HELP! TOPIC - CRITERIA BASED VBA VLOOKUP

ahmed.sange

New Member
Hi ,

I'm looking for some assistance in developing a macro for the below:
  1. I have two workbooks (book1 & book2) with a primary key (i.e. a common column of data - i.e. item code). Both the workbooks are dynamic (i.e. there may be new columns and rows added onto both the workbooks). Book 1 contains the data/table from where I want to vlookup data onto book 2 based on conditions. Please view the attached 2 workbooks.
  2. I want the macro to find the 'month' in book1 and then vlookup that data onto the same month column of book2 based on the item code. (e.g. To vlookup august data on book2, I want the macro to find august on book1 and then vlookup data based on the item code)
  3. This is because there will be new columns (i.e. new month columns) added to book1 on a monthly basis.
Please help as all my attempts have failed.
 

Attachments

  • Book1 - Sample.xlsx
    11.1 KB · Views: 7
  • Book2 - Sample.xlsx
    12.7 KB · Views: 6
Adding on to Deepak's idea, this macro does similar. Be sure to adjust names as needed
Code:
Sub RefreshValues()
Dim sourceName As String
Dim fPath As String
Dim shName As String
Dim lastRow As Long
Dim lastCol As Long

'What is name of source book?
sourceName = "Book1 - Sample.xlsx"
'What is name of sheet with data?
shName = "Sheet1"
'Where if file located? If not the same folder as this workbook, change this line
fPath = ThisWorkbook.Path

If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If

Application.ScreenUpdating = False
'Which sheet in this book gets values?
With Worksheets("Sheet1")
    'Define boundaries on this sheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
   
    With .Range("I3", .Cells(lastRow, lastCol))
        'Use formulas
        .FormulaR1C1 = _
            "=INDEX('" & fPath & "[" & sourceName & "]" & shName & "'!R5C7:R1000C26,MATCH(RC2,'" & _
            fPath & "[" & sourceName & "]" & shName & "'!R5C2:R1000C2,0),MATCH(R1C,'" & fPath & "[" & sourceName & "]" & shName & "'!R2C7:R2C26,0))"
        'Make static values
        .Copy
        .PasteSpecial xlPasteValues
    End With
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
   
End Sub
 

Attachments

  • Query Book.xlsm
    21.3 KB · Views: 10
Adding on to Deepak's idea, this macro does similar. Be sure to adjust names as needed
Code:
Sub RefreshValues()
Dim sourceName As String
Dim fPath As String
Dim shName As String
Dim lastRow As Long
Dim lastCol As Long

'What is name of source book?
sourceName = "Book1 - Sample.xlsx"
'What is name of sheet with data?
shName = "Sheet1"
'Where if file located? If not the same folder as this workbook, change this line
fPath = ThisWorkbook.Path

If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If

Application.ScreenUpdating = False
'Which sheet in this book gets values?
With Worksheets("Sheet1")
    'Define boundaries on this sheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
  
    With .Range("I3", .Cells(lastRow, lastCol))
        'Use formulas
        .FormulaR1C1 = _
            "=INDEX('" & fPath & "[" & sourceName & "]" & shName & "'!R5C7:R1000C26,MATCH(RC2,'" & _
            fPath & "[" & sourceName & "]" & shName & "'!R5C2:R1000C2,0),MATCH(R1C,'" & fPath & "[" & sourceName & "]" & shName & "'!R2C7:R2C26,0))"
        'Make static values
        .Copy
        .PasteSpecial xlPasteValues
    End With
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub

Luke,

Mate. You're a genius. Been working at it for the past two weeks to no avail. You've done it in a flash. I'm a fan of you from today mate. Thanks a ton.

Deepak, Thank you once more mate. You directed me in the right direction.

Glad i joined this community of awesome people.
 
Back
Top