Populate VLOOKUP formula using VBA as far as Column A values

Anne Troy

New member
Joined
Mar 25, 2011
Messages
23
Reaction score
0
Points
0
Once again, I've tried solutions on several websites and can't get them to work right.

Currently:

=VLOOKUP(B2,H:I,2,FALSE)

Should be placed in Cell E2, and copied down in column E as far as their are values in Column A.

Code:
Sub ChoicesFixData()
Dim lrow As Long
lrow = Cells(Rows.Count, "A").End(xlUp).Row
'Copy the choice sets to the ChoiceCategory sheet and remove dupes
'Application.ScreenUpdating = False

'Application.DisplayAlerts = False
Sheets("ChoicesData").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Range("L2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.RemoveDuplicates Columns:=1, Header:=xlNo
Range("A2").Select
'Assign SetIDs
Range("M2").Value = 100
Range("M3").Formula = "=M2+1"
Range("M3").Select
ActiveCell.AutoFill Destination:=Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown)).Offset(0, 1)

Range("N2").Formula = "=vlookup(L2,A:D,4,false)"
Range("N2").Select
ActiveCell.AutoFill Destination:=Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0, -1).End(xlDown)).Offset(0, 1)

Range("E2:E" & lrow).Formula = "=VLOOKUP(B2,H:I,2,FALSE)"
Range("F2:F" & lrow).Formula = "=VLOOKUP(A2,L:M,2,FALSE)"

...

Those last two lines appear to be putting the formulas in row 1 and row 2, and that's it, even though I have many rows of data in column A.
E1 has the formula above. So does E2. I have several vlookups in my workbook, but they always reside next to other data. Help! :)
 
Found it.
Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP(A2,L:M,2,FALSE)"

Thanks!
 
Back
Top