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.
Thanks all for your help,![]()
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
Kp
Bookmarks