macro to subtract columns and put result in new column

  • Thread starter Thread starter Vel
  • Start date Start date
V

Vel

The number of rows in the spreadsheet will always vary,
and I would like to create a macro to subtract column A
from column B and place the resulting value in column C.
Since the resulting spreadsheet will be saved as a *.csv
and imported into another program, I only want the
subtraction to occur if there is a value in both column A
and B so that I don't get a bunch of 0's. Can someone
tell me how to do that?

Thanks so much in advance!
 
Vel said:
The number of rows in the spreadsheet will always vary,
and I would like to create a macro to subtract column A
from column B and place the resulting value in column C.
Since the resulting spreadsheet will be saved as a *.csv
and imported into another program, I only want the
subtraction to occur if there is a value in both column A
and B so that I don't get a bunch of 0's. Can someone
tell me how to do that?
Click to expand...

I don't think you need a macro, just an IF condition:

Just copy this formula in C1 and copy it downwards.

=IF(AND(A1<>"",B1<>""),B1-A1,"")

If you really need a macro:

I suppose you start on row 1 with your first data.

Sub Subtraction()
Dim MyCell as Range

Set MyCell = Range("C1")

With MyCell
While .Offset(0,-2) <> "" Or .Offset(0,-1) <> ""
If .Offset(0,-2) <> "" And .Offset(0,-1) <> "" Then
.Value = .Offset(0,-1) - .Offset(0,-2)
End If
Set MyCell = .Offset(1,0)
Wend
End With
End Sub

Regards,
 
Vel said:
The number of rows in the spreadsheet will always vary,
and I would like to create a macro to subtract column A
from column B and place the resulting value in column C.
Since the resulting spreadsheet will be saved as a *.csv
and imported into another program, I only want the
subtraction to occur if there is a value in both column A
and B so that I don't get a bunch of 0's. Can someone
tell me how to do that?

Thanks so much in advance!
Click to expand...

Vel, open a module and past this to it. This should work. Caution
Column A must not hav any blanks. You can set an "Option key" to run
the program.

HTH
Charles

Sub Add_Subtract()
Application.ScreenUpdating = False
Dim i As Long
Dim mytotal As Variant
Dim addsub As Range
Set addsub = Worksheets("sheet1").Cells(1, 1).CurrentRegion
For i = 1 To addsub.Rows.Count
mytotal = addsub(1, 2).Text - addsub(i, 1).Text
If mytotal <> "0" Then
addsub(i, 3).Value = mytotal
End If
Next
End Sub
 
Back
Top