ExcelUser5005
New Member
- Joined
- Jan 23, 2019
- Messages
- 9
Hi
Im getting error on attaining vlookup result.
Pls help
Im getting error on attaining vlookup result.
Pls help
Code:
Sub vlokup()
Dim myLookupValue As String
Dim myFirstColumn As Long
Dim myLastColumn As Long
Dim myColumnIndex As Long
Dim myFirstRow As Long
Dim myLastRow As Long
Dim myVLookupResult As String
Dim myTableArray As Range
Dim WsRoute As Worksheet
Dim WsCT As Worksheet
Dim LVCol As Long
Dim LVRow As Long
Set WsRoute = Sheet2
Set WsCT = Sheet3
LVRow = 2
LVCol = 10
myLookupValue = WsCT.Cells(LVRow, LVCol).Value
myFirstColumn = 2
myFirstRow = 1
myColumnIndex = 2
WsRoute.Activate
myLastColumn = Application.WorksheetFunction.CountA(Range(Range("B1"), Range("B1").End(xlToRight)))
myLastRow = Application.WorksheetFunction.CountA(Range(Range("B1"), Range("B1").End(xlDown)))
With WsRoute
Set myTableArray = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
End With
WsRoute.Range("BJ1").Value = WorksheetFunction.VLookup(myLookupValue, myTableArray, myColumnIndex, False)
End Sub