Please help, I'm stuck with a variable rang vlookup between (2) worksheets.
The vlookup is in worksheet 1 and worksheet 2 is where the range is not constant. It randomly starts in the row 1000 (right now it starts on row 1356). How do I set up a variable range on worksheet 2 and then do a vlookup.
This is what I have so far:
The vlookup is in worksheet 1 and worksheet 2 is where the range is not constant. It randomly starts in the row 1000 (right now it starts on row 1356). How do I set up a variable range on worksheet 2 and then do a vlookup.
This is what I have so far:
VBA Code:
Sub Test()
'''Identify Unattributed in the UNFI Natural file
Dim mRange As String
Dim mRow As Long, lr As Long
Dim mOriginal As String
Dim mEcallunattributed As String
Dim mUNFI As String
Dim mTempRow As Long
Dim mNewStart As Long
Dim wba As Workbook
Set wba = ActiveWorkbook
mEcallunattributed = "UnattributedReport_All Items.xlsx"
'Worsheet 1 already opened
wba.Activate
'Open worksheet 2
Workbooks.Open Filename:=mUnattributed, ReadOnly:=True
'Find the range
Range("B2").End(xlDown).Select
ActiveCell.Offset(4, 0).Select
'The range is the 2nd part of the worksheet. There is 2 spaces between the top data and the bottom data (bootm data is range I'm trying to create on the variable range on)
Selection.End(xlDown).Select
mRow = Selection.Row
mRange = "B" & mRow
'Go back to worksheet 1 for vlookup
wba.Activate
Range("L3").Select
mRange = "L" & mNewStart & ":" & "L" & mRow
Range(mRange).Select
mValue = "=VLOOKUP(D:D,'[UnAttributed_Nielsen List.xlsx]Wks Items'!C:C,1,0)"
Selection.Value = mValue
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End Sub