Macro to copy and paste formula into blank cells based on table range in excel???



    I am trying to figure out a macro that will copy a formula from one cell and paste it into blank cells in the rows below based on the length of the table.  The table size changes each time I run the data query.  I was thinking that I could name the range but I have to base the last row on another column that has data in it.  This is what I had that doesn't work.


    Code Snippet
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet3!R2C2:R36C3,2,FALSE)"
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False



      The paste will always start at cell T6 but will be a variable as far as the last row.  This column will be blank starting at cell T6 and I need to paste the formula down based on the size of column S.


    Any thoughts on this issue?

    Wednesday, June 11, 2008 3:07 PM

All replies

  • Hi,

    Try this,

    Code Snippet

    Range("T5").Resize(Range("S6", Range("S6").End(xlDown)).Rows.Count + 1).FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet3!R2C2:R36C3,2,FALSE)"

    • Proposed as answer by Irish Phil Wednesday, May 27, 2009 8:03 AM
    Thursday, June 12, 2008 8:17 AM
  • Thank you. This answered my problem too.


    Thursday, June 18, 2015 12:21 PM