P
PVANS
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").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
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").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