Copy a formula down in blank cells

[Kevin P]

Hi,

I'm pretty new to VBA and I need to create a macro. I have managed part by using the record macro function (though I'm sure this can be simplified). However, I am coming unstuck as I do not know how to copy the fomulae in column T down to the last cell in column T where the colum one to the left (S) contains data. The current macro just copies down to a pre specied cell. I alos then need to sum the column of data.

Below is how my macro currently looks. Any help you can give me with the Macro would be very much appreciated.

Sub DATACASHCHECK2()
'
' DATACASHCHECK2 Macro
'

'
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
        25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), _
        Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
        38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
        Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1)), TrailingMinusNumbers:=True
    Cells.Select
    Cells.EntireColumn.AutoFit
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    Columns("T:T").Select
    Selection.Insert Shift:=xlToRight
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=IF(RIGHT(RC[-1],3)=""C"",-RC[1],RC[1])"
    Range("T2").Select
    Selection.Copy
    Range("T3:T228").Select
    ActiveWindow.SmallScroll Down:=-21
    ActiveWindow.ScrollRow = 169
    ActiveWindow.ScrollRow = 163
    ActiveWindow.ScrollRow = 160
    ActiveWindow.ScrollRow = 157
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 151
    ActiveWindow.ScrollRow = 145
    ActiveWindow.ScrollRow = 139
    ActiveWindow.ScrollRow = 133
    ActiveWindow.ScrollRow = 127
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 110
    ActiveWindow.ScrollRow = 92
    ActiveWindow.ScrollRow = 74
    ActiveWindow.ScrollRow = 60
    ActiveWindow.ScrollRow = 48
    ActiveWindow.ScrollRow = 39
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 33
    ActiveWindow.ScrollRow = 30
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 25
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 16
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 1
    Range("T3:T2210").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWindow.SmallScroll Down:=75
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 80
    ActiveWindow.ScrollRow = 83
    ActiveWindow.ScrollRow = 89
    ActiveWindow.ScrollRow = 95
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 154
    ActiveWindow.ScrollRow = 280
    ActiveWindow.ScrollRow = 430
    ActiveWindow.ScrollRow = 542
    ActiveWindow.ScrollRow = 659
    ActiveWindow.ScrollRow = 803
    ActiveWindow.ScrollRow = 927
    ActiveWindow.ScrollRow = 1024
    ActiveWindow.ScrollRow = 1159
    ActiveWindow.ScrollRow = 1285
    ActiveWindow.ScrollRow = 1368
    ActiveWindow.ScrollRow = 1456
    ActiveWindow.ScrollRow = 1565
    ActiveWindow.ScrollRow = 1615
    ActiveWindow.ScrollRow = 1697
    ActiveWindow.ScrollRow = 1785
    ActiveWindow.ScrollRow = 1870
    ActiveWindow.ScrollRow = 1944
    ActiveWindow.ScrollRow = 2008
    ActiveWindow.ScrollRow = 2035
    ActiveWindow.ScrollRow = 2050
    ActiveWindow.ScrollRow = 2061
    ActiveWindow.ScrollRow = 2073
    ActiveWindow.ScrollRow = 2091
    ActiveWindow.ScrollRow = 2105
    ActiveWindow.ScrollRow = 2117
    ActiveWindow.ScrollRow = 2123
    ActiveWindow.ScrollRow = 2126
    ActiveWindow.ScrollRow = 2129
    ActiveWindow.ScrollRow = 2132
    ActiveWindow.ScrollRow = 2135
    ActiveWindow.ScrollRow = 2138
    ActiveWindow.ScrollRow = 2141
    ActiveWindow.ScrollRow = 2147
    ActiveWindow.ScrollRow = 2149
    ActiveWindow.ScrollRow = 2152
    ActiveWindow.ScrollRow = 2155
    ActiveWindow.ScrollRow = 2161
    ActiveWindow.ScrollRow = 2167
    ActiveWindow.ScrollRow = 2170
    ActiveWindow.ScrollRow = 2173
    ActiveWindow.SmallScroll Down:=18
    Range("T2213").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-2211]C:R[-1]C)"
    Range("T2214").Select
End Sub

Thanks all for your help,

Kp