Fill down with formula

Question

I'm trying to write a macro that automatically fills down from cell C4 and fills to the amount of existing rows in a data set. I enter a formula into C4 and F4, and after formatting the data set (and right before I make it a table), I want to fill down those formulas. What I'm finding is that it fills down to a specific cell when creating the macro, and then always fills to that cell. Unfortunately, my data set changes the number of records each time, so it's either filling too far, or not far enough. I know it's user error, but I don't know how to write it so that it'll fill down. Help!!

Thank you!

My macro so far is as follows (obviously, I've deactivated the lines of code that I've tried and failed, but they're in there): 

  Columns("H:H").Select

    Application.CutCopyMode = False

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Columns("I:M").Select

    Selection.Delete Shift:=xlToLeft

    Columns("J:O").Select

    Selection.Delete Shift:=xlToLeft

    Range("B1:G1").Select

    Selection.Cut Destination:=Range("L1:Q1")

    Columns("B:B").Select

    Selection.Delete Shift:=xlToLeft

    Selection.Delete Shift:=xlToLeft

    Columns("E:E").Select

    Selection.Delete Shift:=xlToLeft

    Selection.Insert Shift:=xlToRight

    Columns("C:C").Select

    Selection.Insert Shift:=xlToRight

    Range("C3").Select

    ActiveCell.FormulaR1C1 = "sku2"

    Range("F3").Select

    ActiveCell.FormulaR1C1 = "qty calc"

    Range("C4").Select

    ActiveCell.FormulaR1C1 = "=MID(RC[-1],FIND(""-"",RC[-1])+1,LEN(RC[-1]))"

    Range("K1:P1").Select

    Selection.Cut Destination:=Range("B1:G1")

    Range("F4").Select

    ActiveCell.FormulaR1C1 = "=IFERROR(IF(RC[-1]<R1C3,0,R1C4),0)"

    Range("C4").Select

    'Selection.End(x1Down).Select

    'ActiveCell.Offset(0, 1).Select

    'Range(Selection, Selection.End(x1Up)).Select

    'Selection.FillDown

    'ActiveCell.Range("A1:A3560").Select

    Range("A3").Select

    Range(Selection, Selection.End(xlToRight)).Select

    Range(Selection, Selection.End(xlDown)).Select

     Dim tbl As ListObject

    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)

    tbl.TableStyle = "TableStyleMedium15"

    Range("A1").Select

End Sub

Answer 1

I'll try that, but I never get answers on that forum, and I've had good luck here before.

Answer 2

Hi Judi,

For in depth support, we recommend that you post this query in our MSDN Forums. This forum is mainly composed of software developers that can cater to the complexity of your concern.

Thank you.