VBA Perform Autofill formula in specified column name/location

  • MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

    I have a VBA macro that will insert a new column (titled updated col..) to the right of the specified column name. Then I use a VLOOKUP and autofill in the new column with updated values. The column location of the updated column is variable/dynamic. How can I make the autofill function automatically perform in the column range lu or in the adjacent column?

    I tried to write it like this

    Code
    1. Selection.AutoFill Destination:=Range(Cells(2, Columns(lu).Column) & Range("B" & Rows.Count).End(xlUp).Row)

    My current solution requires manually inputting the column Letter location every time before running the VBA

    Code
    1. Selection.AutoFill Destination:=Range("M2:M" & Range("B" & Rows.Count).End(xlUp).Row)


  • Hello and Welcome to the Forum :)


    You could test the following

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for listing all your CROSS POSTS ...


    Thanks also for the Like :thumbup:


    Does it mean the macro is producing the expected result ...???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks for listing all your CROSS POSTS ...


    Thanks also for the Like :thumbup:


    Does it mean the macro is producing the expected result ...???

    Hello,


    Yes, your code did produce the correct results. However, I will use a different solution which was simpler because it only changed 1 line of code

    Code
    1. Selection.AutoFill Destination:=Cells(2, lu).Resize(Range("B" & Rows.Count).End(xlUp).Row - 1)
  • Glad you could fix your problem :)


    You will gradually learn that using Select and Selection in your macros is counter-productive ...

    1. It is the result of the macro recorder which is only a starting point ...

    2. and, it has a negative impact on the overall performance of your workbook ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)