Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding and working with rows that have data in only
Good morning,
I am trying to design a macro for a workbook that contains transactions over a period of time that groups together, and summarises, all the transactions that occured at the same time. EG: (workbook before macro) A B C 1 Date quantity price 2 29/07/09 10 10 3 29/07/09 20 10 4 30/07/09 10 10 5 30/07/09 20 10 (workbook after macro) A B C D 1 Date quantity price *newinsertedcolumn*sales 2 29/07/09 10 10 100 3 29/07/09 20 10 200 4 30 10 300 5 6 30/07/09 10 10 100 7 30/07/09 20 10 200 8 30 10 300 **Where Column D is equal to quantity x price** I have learned how to insert the blank rows to seperate the different dates, While checkrow < lastrow If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert checkrow = checkrow + 3 lastrow = lastrow + 2 Else: checkrow = checkrow + 1 End If I can insert the new column Columns("D:D").Select Selection.Insert Shift:=xlToRight (there are further columns after D, hence why I needed to insert it) But how can i program the macro to then look for only rows with transactions in, and perform the calculation of Quantity x Price and then calculate the total of those results that are grouped by time? If I haven't been clear in my question, please just ask for more detail and I will provide as best I can. I really appreciate the help you guys provide on this forum, and look forward to the responses. Regards and thanks once again Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding and working with rows that have data in only
SORRY FOR THE FORMATTING PROBLEM IN THE INITIAL POST... Have tried to clean
it up Good morning, I am trying to design a macro for a workbook that contains transactions over a period of time that groups together, and summarises, all the transactions that occured at the same time. EG: (workbook before macro) A B C 1 Date quantity price 2 29/07/09 10 10 3 29/07/09 20 10 4 30/07/09 10 10 5 30/07/09 20 10 (workbook after macro) A B C D 1 Date quantity price *newinsertedcolumn*sales 2 29/07/09 10 10 100 3 29/07/09 20 10 200 4 30 10 300 5 6 30/07/09 10 10 100 7 30/07/09 20 10 200 8 30 10 300 **Where Column D is equal to quantity x price** I have learned how to insert the blank rows to seperate the different dates, While checkrow < lastrow If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert checkrow = checkrow + 3 lastrow = lastrow + 2 Else: checkrow = checkrow + 1 End If I can insert the new column Columns("D:D").Select Selection.Insert Shift:=xlToRight (there are further columns after D, hence why I needed to insert it) But how can i program the macro to then look for only rows with transactions in, and perform the calculation of Quantity x Price and then calculate the total of those results that are grouped by time? If I haven't been clear in my question, please just ask for more detail and I will provide as best I can. I really appreciate the help you guys provide on this forum, and look forward to the responses. Regards and thanks once again Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding and working with rows that have data in only
Just recorded this simple macro. Does it doe what you want?
Sub Macro1() Range("A1").Select Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _ Replace:=True, PageBreaks:=False, SummaryBelowData:=True Columns("D:D").Select Selection.Insert Shift:=xlToRight Range("A1").Select End Sub HTH, Ryan---- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "PVANS" wrote: SORRY FOR THE FORMATTING PROBLEM IN THE INITIAL POST... Have tried to clean it up Good morning, I am trying to design a macro for a workbook that contains transactions over a period of time that groups together, and summarises, all the transactions that occured at the same time. EG: (workbook before macro) A B C 1 Date quantity price 2 29/07/09 10 10 3 29/07/09 20 10 4 30/07/09 10 10 5 30/07/09 20 10 (workbook after macro) A B C D 1 Date quantity price *newinsertedcolumn*sales 2 29/07/09 10 10 100 3 29/07/09 20 10 200 4 30 10 300 5 6 30/07/09 10 10 100 7 30/07/09 20 10 200 8 30 10 300 **Where Column D is equal to quantity x price** I have learned how to insert the blank rows to seperate the different dates, While checkrow < lastrow If Cells(checkrow, datecolumn) < Cells(checkrow + 1, datecolumn) Then Rows(checkrow + 1).EntireRow.Insert Rows(checkrow + 1).EntireRow.Insert checkrow = checkrow + 3 lastrow = lastrow + 2 Else: checkrow = checkrow + 1 End If I can insert the new column Columns("D:D").Select Selection.Insert Shift:=xlToRight (there are further columns after D, hence why I needed to insert it) But how can i program the macro to then look for only rows with transactions in, and perform the calculation of Quantity x Price and then calculate the total of those results that are grouped by time? If I haven't been clear in my question, please just ask for more detail and I will provide as best I can. I really appreciate the help you guys provide on this forum, and look forward to the responses. Regards and thanks once again Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Macro - Finding rows with data to copy | Excel Worksheet Functions | |||
finding columns that has data by rows | Excel Programming | |||
Finding data between blank rows | Excel Worksheet Functions | |||
delete from : to n rows after finding data in a cell | Excel Programming | |||
Deleting rows A to E when finding dublicated Data in Column B | Excel Programming |