Excel Macro to list current costs by code and extract previous

Closed
DutchYorks
Posts
3
Registration date
Tuesday March 4, 2014
Status
Member
Last seen
March 6, 2014
- Mar 5, 2014 at 07:51 AM
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
- Mar 17, 2014 at 11:37 AM
Hello, I am looking for a macro, that will list one set of values by a code and then move a precious list of codes with values and subtract one from the other and leave a balance. Does anyone know how to do this? Your anticipated co-operation and assistance in this matter is appreciated.

Regards

Dutch



1 reply

TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Mar 6, 2014 at 11:33 AM
Hi Dutch,

Can you give an example, as I have a hard time understanding what you are trying to do.

Best regards,
Trowa
0
DutchYorks
Posts
3
Registration date
Tuesday March 4, 2014
Status
Member
Last seen
March 6, 2014

Mar 6, 2014 at 12:35 PM
MONTH ONE
MONTH ONE COSTS FORECAST BUDGETS
CODE 1 4 3
XXX1 3 5 2
XXX3 4 6 3
XX4 4 2 4


MONTH 2
COSTS FORECAST BUDGETS
CODE 6 9 8
XXX1 8 10 7
XXX3 9 11 8
XX4 9 7 9

CODES COULD BE UP TO 1000 LINES
WOULD LIKE A MACRO THAT CAN EXTRACT ONE MONTH FROM THE OTHER FOR ALL THE HEADINGS BY CODE AND THANKS IN ADVANCE IF IT CAN BE DONE.
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Mar 10, 2014 at 11:41 AM
Hi Dutch,

What do you mean by "extract"? Do you want to add, subtract, something else?

Can you give result example?

Best regards,
Trowa
0
Many thanks for your reply Trowa, yes I mean subtract there will be two lists of data one for the
current month and one for the previous month. Each line entry will have a separate code for a particular costs. Therefore, I would like to extract the current months code with costs and subtract the previous month, same code and see what the difference is between the two months. Does that help look above I did a list before. Your anticipated help is appreciated.
Dutch
0
TrowaD
Posts
2886
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
June 27, 2022
514
Mar 13, 2014 at 12:02 PM
Hi Dutch,

I am assuming that your actual data doesn't look as nice as your sample data, or else you could just use formula's.

The following Macro will loop through the codes in column A (starting at row 2) on "Sheet2" (this is the name I gave to the sheet with the data from month 2).
These codes are compared to the codes in column A (starting at row 2) on "Sheet1" (this is the name I gave to the sheet with the data from month 1).
When a match is found the values from Sheet1 are subtracted from Sheet2 and placed (along with the code) in the sheet called "Balance" at the first available row.

So change the sheet names in the Macro to match yours and give this a try:

Sub RunMe()
Dim lRow, lRow2, x As Integer

lRow = Sheets("Sheet1").Range("A1").End(xlDown).Row
Sheets("Sheet2").Activate
lRow2 = Range("A1").End(xlDown).Row

For Each cell In Range("A2:A" & lRow2)
x = 1
Do
x = x + 1
If cell.Value = Sheets("Sheet1").Cells(x, "A") Then
Sheets("Balance").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Value
Sheets("Balance").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Offset(0, 1).Value - Sheets("Sheet1").Cells(x, "B").Value
Sheets("Balance").Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Offset(0, 1).Value - Sheets("Sheet1").Cells(x, "C").Value
Sheets("Balance").Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = cell.Offset(0, 1).Value - Sheets("Sheet1").Cells(x, "D").Value
End If
Loop Until x = lRow
Next cell

Sheets("Balance").Activate

End Sub

Let me know how this works out for you.

Best regards,
Trowa
0
Trowa, many thanks for your help, but it hasn't quite worked out, I have tried it and it is giving me three columns where I would just expect one line answer. Also if a new cost code is in month 2 I would expect that the cost would be minus no costs from the previous period and the balance would be brought forward. Many thanks for your help. Dutch
0