Results 1 to 3 of 3

Thread: Populate VLOOKUP formula using VBA as far as Column A values

  1. #1

    Populate VLOOKUP formula using VBA as far as Column A values



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!

  2. #2
    Found it.
    Range("F2:F" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=VLOOKUP(A2,L:M,2,FALSE)"

    Thanks!

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,208
    Articles
    57
    Blog Entries
    14
    Glad you're answering questions here too. LOL!

    (Sorry I wasn't here a little quicker)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •