Macro to subtract Value in column B from column C

[jarnold231]

Hi all,

I'm having trouble figuring out how I can subtract a value in column B of a spreadsheet from a total in column C which has been added in using a "copy/paste to next blank row".

The value in col B will have been added manually, but it's value must be subtracted from the value in the same row of col C. I need to run the macro repeatedly so each time the macro is run the new row of data that is copied into col C should have the value in Col B subtracted from it.

Ideally I would like to add this stage in immediately following the copy and paste macro included below; many thanks for any help.

Please Login or Register  to view this content.

[davesexcel]

Place this into your worksheet module.
Right click on the sheet tab and select "View Code"
Paste the code there.

Please Login or Register  to view this content.

In a nutshell, when you paste in column C column B will subtract the amount.

[jarnold231]

Thanks for your reply Dave,

I'm sorry but I cannot get the code to work. I have placed it into the worksheet section of the relevant sheet, but when I run my code to paste in the data to column C no subtraction occurs.

I'm working in excel 2007, in the worksheet module I have the two drop downs set to "Worksheet" and "Change".

Thanks for your help.

[jarnold231]

Couple of further points;

The code provided by davesexcel above does work; however

1) the code alters the number in col B, not col C. So the value in column c is subtracted from column b rather than column C = C-B. I guess this is pretty simple to change but I don't understand how Target.Offset(0, -1) works.

2) unfortunately the Private Sub does not work when my original macro pastes the data in columns C-F, it only works if you manually type the values into col C. I was wondering if adding a line of code to the macro telling it to run the a private sub after pasting would help?

Any and all help masssively appreciated,

Jake

[jarnold231]

Any help greatly appreciated

[jarnold231]

Okay so I think I've found something of a workaround, but this in itself has thrown up another problem - using the below code the subtraction element I've been after now works, however it only works for the first row of data (because that's what's written in the code) and I don't understand how to instruct this portion of the macro to only apply the subtraction to the new line of data that is added by the Application.Run "Copy____Data" line.

Please Login or Register  to view this content.