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

    Question

  •  

    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
    Range("T5").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],Sheet3!R2C2:R36C3,2,FALSE)"
        Range("T5").Select
        Selection.Copy
        Range("T6:T41").Select
        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