Hello,
I have found a thread on lookups with VBA but I cannot figure how to manipulate the lookup to match my needs. Can someone break down how a lookup translates into VBA?
Below is the code, which should open two sheets then perform a lookup. If I wanted to use the below lookup how do i use this in VBA?
=VLOOKUP(A2,'[Flat And Drop Report.xlsx]Sheet1'!$A:$C,2,0)
Thanks
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub VlookMultipleWorkbooks()
Dim lookFor As Range
Dim srchRange As Range
Dim book1 As Workbook
Dim book2 As Workbook
Dim book2Name As String
book2Name = "test.xls" 'modify it as per your requirement
Dim book2NamePath As String
book2NamePath = ThisWorkbook.Path & "" & book2Name
Set book1 = ThisWorkbook
If IsOpen(book2Name) = False Then Workbooks.Open (book2NamePath)
Set book2 = Workbooks(book2Name)
Set lookFor = book1.Sheets(1).Cells(2, 1) ' value to find
Set srchRange = book2.Sheets(1).Range("B:C") 'source
lookFor.Offset(0, 1).Value = Application.VLookup(lookFor, srchRange, 2, False)
End Sub
Function IsOpen(strWkbNm As String) As Boolean
On Error Resume Next
Dim wBook As Workbook
Set wBook = Workbooks(strWkbNm)
If wBook Is Nothing Then 'Not open
IsOpen = False
Set wBook = Nothing
On Error GoTo 0
Else
IsOpen = True
Set wBook = Nothing
On Error GoTo 0
End If
End Function</code>
I have found a thread on lookups with VBA but I cannot figure how to manipulate the lookup to match my needs. Can someone break down how a lookup translates into VBA?
Below is the code, which should open two sheets then perform a lookup. If I wanted to use the below lookup how do i use this in VBA?
=VLOOKUP(A2,'[Flat And Drop Report.xlsx]Sheet1'!$A:$C,2,0)
Thanks
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub VlookMultipleWorkbooks()
Dim lookFor As Range
Dim srchRange As Range
Dim book1 As Workbook
Dim book2 As Workbook
Dim book2Name As String
book2Name = "test.xls" 'modify it as per your requirement
Dim book2NamePath As String
book2NamePath = ThisWorkbook.Path & "" & book2Name
Set book1 = ThisWorkbook
If IsOpen(book2Name) = False Then Workbooks.Open (book2NamePath)
Set book2 = Workbooks(book2Name)
Set lookFor = book1.Sheets(1).Cells(2, 1) ' value to find
Set srchRange = book2.Sheets(1).Range("B:C") 'source
lookFor.Offset(0, 1).Value = Application.VLookup(lookFor, srchRange, 2, False)
End Sub
Function IsOpen(strWkbNm As String) As Boolean
On Error Resume Next
Dim wBook As Workbook
Set wBook = Workbooks(strWkbNm)
If wBook Is Nothing Then 'Not open
IsOpen = False
Set wBook = Nothing
On Error GoTo 0
Else
IsOpen = True
Set wBook = Nothing
On Error GoTo 0
End If
End Function</code>
