Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
I need a macro that will do the following for me:
I have an accounts programme that outputs to Excel. Cells C1:C70 contain the code "1300"; cells C74:c80 contain the code "1310" and so on for 820 rows. I need to insert a row under row 70 and to put the total of J1:J70 in cell N71. So the macro will travel down Column C and when the code changes, insert a row, and put the total in Column N. Thus I will end up with the total of each code in Column N. I'm sure it's not difficult, but I can't work it out myself. Thanks. Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
One way:
Public Sub SubtotalIt() Dim cell As Range Application.DisplayAlerts = False Cells.Subtotal GroupBy:=3, _ Function:=xlSum, _ TotalList:=Array(10), _ Replace:=True, _ PageBreaks:=False, _ SummaryBelowData:=True Application.DisplayAlerts = True For Each cell In Columns(3).Cells.SpecialCells( _ xlCellTypeConstants, xlTextValues) If InStr(cell.Text, " Total") Then _ cell.Offset(0, 7).Cut cell.Offset(0, 11) Next cell End Sub In article , "Peter" wrote: I need a macro that will do the following for me: I have an accounts programme that outputs to Excel. Cells C1:C70 contain the code "1300"; cells C74:c80 contain the code "1310" and so on for 820 rows. I need to insert a row under row 70 and to put the total of J1:J70 in cell N71. So the macro will travel down Column C and when the code changes, insert a row, and put the total in Column N. Thus I will end up with the total of each code in Column N. I'm sure it's not difficult, but I can't work it out myself. Thanks. Peter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Very many thanks for doing this for me. I have run it
and it works nicely. I have been doing this manually for some years now, and much appreciate the automation. Peter -----Original Message----- Here is another way. Paste the following code in a macro. Select the cell you want to start from (1300) and run the macro: (watch for line wrap) Dim Cell2Check 'holds the current total of the coulumn Dim ColTotal As Single Dim Col2Comp As Integer Dim ValCol As Integer Dim TotCol As Integer Dim CompareColumn As String Dim Numbers2AddCol As String Dim Total2Column As String ' CHANGE THESE LETTERS TO ' YOUR COLUMNS ' can be upper or lower case '============================== CompareColumn = "C" ' 1300, 1310 ,etc Numbers2Add = "J" ' numbers to add Total2Column = "n" ' column to put total '============================== ' Initialize variables ' convert to uppercase and subtract 64 to get column number Col2Comp = Asc(UCase(CompareColumn)) - 64 ' these two are relative positions to the activecell (Col2Comp) ' and are GT so subtract to get offset ValCol = Asc(UCase(Numbers2AddCol)) - 64 - Col2Comp TotCol = Asc(UCase(Total2Column)) - 64 - Col2Comp ColTotal = 0 ' goto first cell 'ActiveSheet.Cells(Row2Start, Col2Comp).Select 'Get first value Cell2Check = ActiveCell.Value 'get first number 'ColTotal = ActiveCell.Offset(0, ValCol).Value Do While ActiveCell.Value < "" ' if current cell value = saved value Do While ActiveCell.Value = Cell2Check ' add current number to running total ColTotal = ColTotal + ActiveCell.Offset(0, ValCol).Value ' move down one row ActiveCell.Offset(1, 0).Activate Loop ActiveCell.EntireRow.Insert ' write total to cell ActiveCell.Offset(0, TotCol).Value = ColTotal ' move down one row ActiveCell.Offset(1, 0).Activate 'starting again so.... ' get the new value of the cell to check Cell2Check = ActiveCell.Value 'clear column total ColTotal = 0 Loop Yes, longer but more control.... HTH Steve -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) -----Original Message----- I need a macro that will do the following for me: I have an accounts programme that outputs to Excel. Cells C1:C70 contain the code "1300"; cells C74:c80 contain the code "1310" and so on for 820 rows. I need to insert a row under row 70 and to put the total of J1:J70 in cell N71. So the macro will travel down Column C and when the code changes, insert a row, and put the total in Column N. Thus I will end up with the total of each code in Column N. I'm sure it's not difficult, but I can't work it out myself. Thanks. Peter . . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Very many thanks. This is incredible! I have been doing
this task manually for some years. I must read more manuals! Peter -----Original Message----- There's an option built into excel that almost does what you want. (but it puts subtotals in the same column as the raw data). Take a look at Data|subtotals (Since this is such a useful function, I'd try to accept the "wrong" column stuff!) Peter wrote: I need a macro that will do the following for me: I have an accounts programme that outputs to Excel. Cells C1:C70 contain the code "1300"; cells C74:c80 contain the code "1310" and so on for 820 rows. I need to insert a row under row 70 and to put the total of J1:J70 in cell N71. So the macro will travel down Column C and when the code changes, insert a row, and put the total in Column N. Thus I will end up with the total of each code in Column N. I'm sure it's not difficult, but I can't work it out myself. Thanks. Peter -- Dave Peterson . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro
Very many thanks. I have run this and it works fine. See
my thanks to the others. Peter -----Original Message----- One way: Public Sub SubtotalIt() Dim cell As Range Application.DisplayAlerts = False Cells.Subtotal GroupBy:=3, _ Function:=xlSum, _ TotalList:=Array(10), _ Replace:=True, _ PageBreaks:=False, _ SummaryBelowData:=True Application.DisplayAlerts = True For Each cell In Columns(3).Cells.SpecialCells( _ xlCellTypeConstants, xlTextValues) If InStr(cell.Text, " Total") Then _ cell.Offset(0, 7).Cut cell.Offset(0, 11) Next cell End Sub In article , "Peter" wrote: I need a macro that will do the following for me: I have an accounts programme that outputs to Excel. Cells C1:C70 contain the code "1300"; cells C74:c80 contain the code "1310" and so on for 820 rows. I need to insert a row under row 70 and to put the total of J1:J70 in cell N71. So the macro will travel down Column C and when the code changes, insert a row, and put the total in Column N. Thus I will end up with the total of each code in Column N. I'm sure it's not difficult, but I can't work it out myself. Thanks. Peter . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |