Hi, I am pretty new to macros and am trying to figure out how I can modify an existing one I have that performs a vlookup. Ideally, I can put a match formula in where the column index number 5 is located that can referencing the column headers in my data upload sheet. Without the match I have to modify my data upload format creating more columns than just adding rows. Also I wouldnt need a new lookup to input data into every column as I can just add that criteria to the data file.
>>> use code - tags <<<
Any help would be greatly appreciated!!!!
Thanks,
Taylor
>>> use code - tags <<<
Code:
Sub VlookuptoSource()
Dim listWs As Worksheet, dataWs As Worksheet
Dim listLastRow As Long, dataLastRow As Long, x As Long
Dim ListRng As Range
Set listWs = ThisWorkbook.Worksheets("Check_List")
Set dataWs = ThisWorkbook.Worksheets("New_Source")
listLastRow = listWs.Range("C" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("B" & Rows.Count).End(xlUp).Row
Set ListRng = listWs.Range("C3:G" & listLastRow)
For x = 2 To dataLastRow
On Error Resume Next
If listWs.Range("G5").Value = 1 Then
dataWs.Range("C" & x).Value = Application.WorksheetFunction.Vlookup(dataWs.Range("B" & x).Value, ListRng, 5, False)
End If
If listWs.Range("G5").Value = 2 Then
dataWs.Range("D" & x).Value = Application.WorksheetFunction.Vlookup(dataWs.Range("B" & x).Value, ListRng, 5, False)
End If
If listWs.Range("G5").Value = 3 Then
dataWs.Range("E" & x).Value = Application.WorksheetFunction.Vlookup(dataWs.Range("B" & x).Value, ListRng, 5, False)
End If
If listWs.Range("G5").Value = 4 Then
dataWs.Range("F" & x).Value = Application.WorksheetFunction.Vlookup(dataWs.Range("B" & x).Value, ListRng, 5, False)
End If
'If listWs.Range("G5").Value = 5 Then
'dataWs.Range("G" & x).Value = Application.WorksheetFunction.Vlookup(dataWs.Range("B" & x).Value, ListRng, 5, False)
'End If
Next x
End Sub
Any help would be greatly appreciated!!!!
Thanks,
Taylor
Last edited by a moderator: