Excel Macro to list current costs by code and extract previous

[Closed]
Report
Posts
3
Registration date
Tuesday March 4, 2014
Status
Member
Last seen
March 6, 2014
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
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

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
Hi Dutch,

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

Best regards,
Trowa
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
Hi Dutch,

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

Can you give result example?

Best regards,
Trowa
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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
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
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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
482
Hi Dutch,

You expected one line answer? That doesn't make sense to me. You have 4 columns and 3 rows of sample data, so the result will have those dimensions as well, right? Can you give result data of the sample data?

Your second question does make sense, which I will change when the above is clear to me.

Best regards,
Trowa