VBA code for Vlookup between two worksheet

[patx6]

Hi

I am a self-learner and very new for VBA. I am creating simple worksheet and part of my macro is using VLookup?? at Request to Dispatch date from another worksheet. Could you please assist me on this?

Sub VlookMultipleWorkbooks()
Dim lookFor As Range
Dim srchRange As Range
Dim book1 As Workbook
Dim book2 As Workbook
Dim book2Name As String
book2Name = "Submission1.xlsm"
Set book1 = ThisWorkbook
Set book2 = Workbooks(book2Name)
Set lookFor = book1.Sheets("Sheet1").Range("A2:A5")
Set srchRange = book2.Sheets("Sheet1").Range("K:F")
lookFor.Offset(0, 6).Value = Application.Vlookup(lookFor, srchRange, -5, False)
End Sub
It seems to work only the results turn up with #N/A. Files are attached.
Thanks
Patx6

 

[SirJB7]

Hi, patx6!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, at a first glance there's a problem (a big one) with the 3rd argument of VLOOKUP function. This function searches for an argument in the 1st column of a range and return the value at the nth column of that range for the row where the match occurs. So "n" should be a column of the specified range, hence not less than 1 and not great than the range columns.

If you want to perform a look to the left, you'd better use a combination of INDEX & MATCH functions as follows:

' update at top
Set srchRange = book2.Sheets("Sheet1").Range("F:F")  ' or the column where to search
'  add at top
Set rtrvRange = book2.Sheets("Sheet1").Range("F:F") ' or the column to retrieve
' fixed function call
With aplication.WorksheetFunction
    lookFor.Offset(0, 6).Value = .Index(rtrvRange, .Match(lookFor, srchRange.Columns(1), 0))
End With

BTW, to the sample files correspond to this code? I have a problem trying to make column references match.

Regards!

 

[patx6]

Thanks SirJB7

I will give it a go.

 

[patx6]

Hi SirJB7

The concept is I would like to get data from worksheet2, column E and put it to worksheet1, column G.
I still have problem when running "Application.WorksheetFunction".

Thanks
Patx6

 

[SirJB7]

Hi, patx6!

Give a look at the uploaded files. This is the fixed code:

Option Explicit
 
Sub VlookMultipleWorkbooks()
    ' constants
    Const book2Name = "VBA code for Vlookup between two worksheet - Submission1 (for patx6 at chandoo.org).xlsx"
    ' declarations
    Dim lookFor As Range
    Dim srchRange As Range
    Dim book1 As Workbook
    Dim book2 As Workbook
    Dim I As Long
    ' start
    Set book1 = ThisWorkbook
    Set book2 = Workbooks(book2Name)
    Set lookFor = book1.Sheets("Sheet1").Range("A2:A5")
    Set srchRange = book2.Sheets("Sheet1").Range("A:J")
    ' process
    With Application.WorksheetFunction
        For I = 1 To lookFor.Rows.Count
            lookFor.Cells(I, 1).Offset(0, 6).Value = _
                .Index(srchRange, _
                    .Match(lookFor.Cells(I, 1).Value, srchRange.Columns(10).Cells, 0), _
                    5)
        Next I
    End With
    ' end
    Set srchRange = Nothing
    Set lookFor = Nothing
    Set book2 = Nothing
    Set book1 = Nothing
End Sub

Update the file reference for file 2 as required.

Just advise if any issue.

Regards!

 

[pmyk]

SirJB7, Thanks for mentioning that "n" should be a column of the specified range. You saved my time. Thanks.

 

[APA]

hello,
I'm even newer than patx6
my wish is to create a VBA code that will:
1. open a chosen by user file
2. do a VLOOKUP on a range in the selected file

I use this code to open a file:

sub openIt()
dim strFile as string
strfile = application.getopenfilename
workbooks.open strfile
end sub

and it works

but:

how do I tell VBA that I want to vlookup on the opend file?

moreover, can I continue the code from the openIt code?

I'll appreciate any help

APA

 

[rxk]

Hi SirJ7,

I have a similar issue with pat6x but on different column criteria.

I tried using your code but I don't understand part of it, it has error on the With Application.WorksheetFunction syntax, can you please explain how it works? I also added sample files

Sub VlookTrans1()
  ' constants
  Const book2Name = "Trans1.xlsx"
  ' declarations
  Dim lookFor As Range
  Dim srchRange As Range
  Dim book1 As Workbook
  Dim book2 As Workbook
  Dim I As Long
  ' start
  Set book1 = ThisWorkbook
  Set book2 = Workbooks(book2Name)
  Set lookFor = book1.Sheets("DWGLIST").Range("A7:A50")
  Set srchRange = book2.Sheets("DOC").Range("A:B")
  ' process
  With Application.WorksheetFunction
  For I = 1 To lookFor.Rows.Count
  lookFor.Cells(I, 1).Offset(0, 0).Value = _
  .Index(srchRange, _
  .Match(lookFor.Cells(I, 1).Value, srchRange.Columns(1).Cells, 0), _
  5)
  Next I
  End With
  ' end
  Set srchRange = Nothing
  Set lookFor = Nothing
  Set book2 = Nothing
  Set book1 = Nothing
End Sub
&

 

[Hardeep]

Hello Sir,

i am already using this code thanks for the help.

Now i have an issue i do have multiple duplicate Request number and i want submission sheet update with next row data.

Hi, patx6!

Give a look at the uploaded files. This is the fixed code:

Option Explicit

Sub VlookMultipleWorkbooks()
    ' constants
    Const book2Name = "VBA code for Vlookup between two worksheet - Submission1 (for patx6 at chandoo.org).xlsx"
    ' declarations
    Dim lookFor As Range
    Dim srchRange As Range
    Dim book1 As Workbook
    Dim book2 As Workbook
    Dim I As Long
    ' start
    Set book1 = ThisWorkbook
    Set book2 = Workbooks(book2Name)
    Set lookFor = book1.Sheets("Sheet1").Range("A2:A5")
    Set srchRange = book2.Sheets("Sheet1").Range("A:J")
    ' process
    With Application.WorksheetFunction
        For I = 1 To lookFor.Rows.Count
            lookFor.Cells(I, 1).Offset(0, 6).Value = _
                .Index(srchRange, _
                    .Match(lookFor.Cells(I, 1).Value, srchRange.Columns(10).Cells, 0), _
                    5)
        Next I
    End With
    ' end
    Set srchRange = Nothing
    Set lookFor = Nothing
    Set book2 = Nothing
    Set book1 = Nothing
End Sub

Update the file reference for file 2 as required.

Just advise if any issue.

Regards!

 

[SirJB7]

Hi, Hardeep!
So long... almost 4 years.
I'm wondering if I should add a close date for old code... hahaha!
Glad it still works, for the original intended use.
Let me check it tomorrow.
Regards!

 

[Hardeep]

Hi, Hardeep!
So long... almost 4 years.
I'm wondering if I should add a close date for old code... hahaha!
Glad it still works, for the original intended use.
Let me check it tomorrow.
Regards!

Hi, Hardeep!
So long... almost 4 years.
I'm wondering if I should add a close date for old code... hahaha!
Glad it still works, for the original intended use.
Let me check it tomorrow.
Regards!

Good Morning Sir,

i forgot to mention when updating MASTER, if value is BLANK or no value then MASTER should keep old value if any. i uploaded updated sheet for reference.