I am trying to create user form (like on picture) from where I would choose 2 excel files and execute Vlookup. I
I try this code but it does not execute Vlookup.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Option Explicit
Dim FileToOpen1 As Variant
Dim FileToOpen2 As Variant
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim cl As Range
Private Sub BrowseButton1_Click()
FileToOpen1 = Application.GetOpenFilename(Title:="Browse for your file", FileFilter:="Excel Files(*.xls*),*xls*")
If FileToOpen1 <> False Then
TextBox1 = FileToOpen1
End If
End Sub
Private Sub BrowseButton2_Click()
FileToOpen2 = Application.GetOpenFilename(Title:="Browse foy your file", FileFilter:="Excel Files(*.xls*),*xls*")
If FileToOpen2 <> False Then
TextBox2 = FileToOpen2
End If
End Sub
Private Sub OK_Click()
If FileToOpen1 <> False Then
Set wb1 = Application.Workbooks.Open(FileToOpen1)
End If
If FileToOpen2 <> False Then
Set wb2 = Application.Workbooks.Open(FileToOpen2)
End If
On Error Resume Next
rng1 = wb1.Sheets(1).Range("B3:B8")
Price_row = wb1.Sheets(1).Range("C3").row
Price_clm = wb1.Sheets(1).Range("C3").column
rng2 = wb2.Sheets(1).Range("A3:C8")
For Each cl In rng1
wb1.Sheets(1).Cells(Price_row, Price_clm) = Application.WorksheetFunction.VLOOKUP(cl, rng2, 2, False)
Price_row = Price_row 1
Next cl
End Sub
CodePudding user response:
You are missing two variable definitions:
Dim Price_row As Long
Dim Price_clm As Long
And variables for range need to be assigned with Set
This: Set rng2 = wb2.Sheets(1).Range("A3:C8")
instead of this rng2 = wb2.Sheets(1).Range("A3:C8")
Now, vlookup function in vba will throw an error when it doesnt find a value. A workaround for this will be something like this.
Private Sub OK_Click()
If FileToOpen1 <> False Then
Set wb1 = Application.Workbooks.Open(FileToOpen1)
End If
If FileToOpen2 <> False Then
Set wb2 = Application.Workbooks.Open(FileToOpen2)
End If
Set rng1 = wb1.Sheets(1).Range("B3:B8")
Price_row = wb1.Sheets(1).Range("C3").Row
Price_clm = wb1.Sheets(1).Range("C3").Column
Set rng2 = wb2.Sheets(1).Range("A3:C8")
For Each cl In rng1
On Error Resume Next
vlResult = "" 'Reset variable
vlResult = Application.WorksheetFunction.VLookup(cl, rng2, 2, False) 'Performs vlookup
If Not vlResult = "" Then
wb1.Sheets(1).Cells(Price_row, Price_clm).Value = vlResult
Else
wb1.Sheets(1).Cells(Price_row, Price_clm).Value = "N/A"
End If
Price_row = Price_row 1
Next cl
End Sub
*Dont forget to add the variable too.
Dim vlResult As String