Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to subtract columns and put result in new column
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to subtract columns and put result in new column
Vel wrote:
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? 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, -- Beto Reply: Erase between the dot (inclusive) and the @. Responder: Borra la frase obvia y el punto previo. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to subtract columns and put result in new column
"Vel" wrote in message ...
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, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtract times with a negative result | Excel Discussion (Misc queries) | |||
Macro or formula to compare columns and give a result of the odd o | Excel Discussion (Misc queries) | |||
Searching on 2 columns and putting result in third column. | Excel Worksheet Functions | |||
Lookup 3 columns and return a result from another column | Excel Discussion (Misc queries) | |||
Subtract times and let result go negative | Excel Worksheet Functions |