VBA VLookUp Help (Thanks ahead of time!)

[krfo12]

Hi!


I am very new to VBA and am trying to write a procedure where I use a vlookup to populate information in my active sheet from an external and closed excel document. I would like to use VBA and not a formula.


I need to lookup cell A2 on my ActiveSheet and return the corresponding job name from "Job#Log," which is an external excel workbook with multiple sheets.


I would like the result to be displayed in cell B2 of my ActiveSheet.


This procedure will need to loop down each entry until it reaches the end of the list.


This is what I have so far:

Sub VLookUp_JobNumberLog()

Dim strLocation As String, strFile As String 'declaring variable
[INDENT]strLocation = "C:\Users\kowens\Box\Insurance (Working Folder)\Job#Log.xlsx" 'definition of variable as location of the external file where the information shall be looked up[/INDENT]

Dim JobNumber As String 'variable for the information that I need looked up
[INDENT]JobNumber  = ActiveSheet.Range("A2").Value 'defines variable as value of cell in active sheet[/INDENT]

Dim vlJobName As Variant 'declaring variable
[INDENT]vlJobName=Application.WorksheetFunction.VLookup(JobNumber,Workbooks("Job#Log.xlsx").Sheets("JobNumberLog").Range("A:B"),2,False) 'variable value is the vlookup[/INDENT]

JobNumber.Offset(0,1).Value=vlJobName 'puts result of VLookUp into cell adjacent to JobNumber, in this instance B2

End Sub

If you could comment out the steps so I know what you are doing that would be really helpful.


Any assistance you provide is greatly appreciated!

 

[lennywatson]

Do you mind my asking why not with a formula?

 

[Norie]

The workbook would need to be open if you want to use Application.WorksheetFunction.Vlookup(...).

 

[krfo12]

Do you mind my asking why not with a formula?

Because this file is already running large array formulas and having the addition of a VLookUp causes excel to be unhappy.

Currently, I have the VLookUp run and then copy+paste it as a value into the field. That seems to work the majority of the time. I was just wonder if I could do a simple vlookup using VBA.