VBA vlookup to a different workbook

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...
Go back to All Questions Login or Register
480 views
0

I am new to VBA scripting, My intention is to do vlookup to another workbook and i got VBA code from google as below but its not working for me. It’s not giving any result.The VBA code is below. How it can be corrected?

Sub LookupValues()
Dim colI_Cell As Range
Dim colI_Range As Range
Dim rngLookupRange As Range
Dim rngFound As Range
‘add this
Dim wb As Workbook
ThisWorkbook.Worksheets(“MAIN”).Activate
Set colI_Range = ActiveSheet.Range(“I4:I500”).Cells

‘when you do this, Workbook2.xls becomes active
Set wb = Workbooks.Open(“G:\Excel macro workings\Master files\A.xlsx”, True, True) ‘ open the source workbook, read only
Set rngLookupRange = wb.Worksheets(“CDE”).Range(“A1:G600”)
‘moved from below to make this workbook active again
‘Workbooks(“Workbook1.xlsm”).Worksheets(“MAIN”).Activate ‘ old code
ThisWorkbook.Activate ‘ leaves no doubt!

For Each colI_Cell In colI_Range
With rngLookupRange
Set rngFound = .Find(What:=colI_Cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not rngFound Is Nothing Then
‘rngFound points to a cell in col A in other workbook
‘we need to bring back value from G into col J in this workbook
‘ looked in col A, so offset (0, 7) returns column G value
colI_Cell.Offset(0, 1) = rngFound.Offset(0, 7).Value
End If
End With
Next
‘housekeeping – release resources back to the system
‘and close the other workbook
Set rngLookupRange = Nothing
wb.Close False ‘ close the source workbook without saving any changes
Set wb = Nothing ‘ free memory
Set colI_Range = Nothing
End Sub

1

Hi Muhammed unfortunately you must be more specific. Which line of code is failing?

Simply the best place to learn VBA!

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match