• 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.

vba macro vlookup from another workbook

sumit Madaan

New Member
I want to lookup between two workbooks sheets through vba macros on basis of common field between both. i am using below code and it is showing error, snapshot attached for your ready reference.

please help and advise. Thanks in advance.



I need result in sample macro2 file and need to do lookup from BB_Finacle ID-Mar'19 file.

Code:
Sub test()
Application.ScreenUpdating = False

Dim sw As Workbook
Dim dw As Workbook
Dim srng As Range

swname$ = "BB_Finacle ID-Mar'19.xlsb"
swpath$ = ThisWorkbook.Path & "\" & swname
Set dw = ThisWorkbook
c& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Workbooks.Open (swpath)
Set sw = Workbooks(swname)
Set srng = sw.Sheets(1).Range("B:R")

    Sheet1.Range("B2:B" & c).Formula = "=VLookup(A2,[BB_Finacle ID-Mar'19.xlsb]SHEET1!" & srng.Address & ", 3, False)"
  
  
    Sheet1.Range("B2:B" & c).Copy
    Sheet1.Range("B2").PasteSpecial xlValues
    Application.CutCopyMode = False
sw.Close

Application.ScreenUpdating = True
End Sub
0ddec2d9-ed5d-4bd6-a19b-c190a2c0b7f2
 

Attachments

  • upload_2019-3-7_10-40-46.png
    upload_2019-3-7_10-40-46.png
    207.5 KB · Views: 56
  • BB_Finacle ID-Mar'19.xlsb
    11 KB · Views: 50
  • Sample Macro 2.xlsm
    25.8 KB · Views: 52
Last edited by a moderator:
Hi Sumit,

Try copying this code and run the macro.

Code:
Sub vlookup_macro()
'
' vlookup_macro Macro
'

'
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C2,2,0)"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C3,3,0)"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-3],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C4,4,0)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C5,5,0)"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-5],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C6,6,0)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C7,7,0)"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-7],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C8,8,0)"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-8],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C9,9,0)"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-9],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C10,10,0)"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-10],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C11,11,0)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-11],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C12,12,0)"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-12],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C13,13,0)"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-13],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C14,14,0)"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-14],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C15,15,0)"
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("B12").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("B2:O12").Select
    Range("B12").Activate
    Selection.FillDown
    ActiveWorkbook.Save
End Sub

Thanks
Jaya
 
Last edited by a moderator:
I want to lookup between two workbooks sheets through vba macros on basis of common field between both. i am using below code and it is showing error, snapshot attached for your ready reference.

please help and advise. Thanks in advance.



I need result in sample macro2 file and need to do lookup from BB_Finacle ID-Mar'19 file.

Sub test()
Application.ScreenUpdating = False

Dim sw As Workbook
Dim dw As Workbook
Dim srng As Range

swname$ = "BB_Finacle ID-Mar'19.xlsb"
swpath$ = ThisWorkbook.Path & "\" & swname
Set dw = ThisWorkbook
c& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Workbooks.Open (swpath)
Set sw = Workbooks(swname)
Set srng = sw.Sheets(1).Range("B:R")

Sheet1.Range("B2:B" & c).Formula = "=VLookup(A2,[BB_Finacle ID-Mar'19.xlsb]SHEET1!" & srng.Address & ", 3, False)"


Sheet1.Range("B2:B" & c).Copy
Sheet1.Range("B2").PasteSpecial xlValues
Application.CutCopyMode = False
sw.Close

Application.ScreenUpdating = True
End Sub

0ddec2d9-ed5d-4bd6-a19b-c190a2c0b7f2
Hi Sumit,

Try copying this code and run the macro.

Code:
Sub vlookup_macro()
'
' vlookup_macro Macro
'

'
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C2,2,0)"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-2],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C3,3,0)"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-3],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C4,4,0)"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C5,5,0)"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-5],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C6,6,0)"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-6],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C7,7,0)"
    Range("H2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-7],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C8,8,0)"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-8],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C9,9,0)"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-9],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C10,10,0)"
    Range("K2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-10],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C11,11,0)"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-11],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C12,12,0)"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-12],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C13,13,0)"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-13],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C14,14,0)"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-14],'[BB_Finacle ID-Mar''19 (1).xlsb]CIF sheet'!C1:C15,15,0)"
    Range("A2").Select
    Selection.End(xlDown).Select
    Range("B12").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("B2:O12").Select
    Range("B12").Activate
    Selection.FillDown
    ActiveWorkbook.Save
End Sub

Thanks
Jaya

Thanks Jaya for this code.

But actually it is not solving my problem. I need code which shows result in sample macro2 file and need to do lookup from BB_Finacle ID-Mar'19 file.

when i am running code given by you, it is showing one tab to select the file and press ok and same repeat again and again till the time code would not finished.

i have already attached the sample files, request you to please check once from your end too.
 
Thanks Jaya for this code.

But actually it is not solving my problem. I need code which shows result in sample macro2 file and need to do lookup from BB_Finacle ID-Mar'19 file.

when i am running code given by you, it is showing one tab to select the file and press ok and same repeat again and again till the time code would not finished.

i have already attached the sample files, request you to please check once from your end too.

Hi Sumit,

PFA the file with the code where i worked upon.

Let me know if you require help.

Thanks
Jaya
 

Attachments

  • Sample Macro 2 (1).xlsm
    33.1 KB · Views: 90
Back
Top