VBA Help with subtracting columns

MR6905

New Member
Joined
Aug 16, 2009
Messages
5
I am hoping someone can help with the vba code for subtracting one column from another.

I am trying to subtract column "O" from column "C". The result goes in column "S". (S= C-O)
The first row of each column has a name header - so the data starts in row 2.
The overall number of rows vary from time to time so I need to be able to adjust for that.

I tried the flowing but I did not get the formula correct. I am not a code wizard and tried to copy some other examples.

Range("S2").Select

Dim LR As Long, i As Long
LR = Range("S" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("S" & i)
.Value = .Offset(, -16).Value - .Offset(, -4).Value
End With
Next I

Any suggestions or advice would be appreciated.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
try below code

Code:
Sub Substract_Column()
Dim i As Integer
For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
    Cells(i, 19).Value = Cells(i, 3).Value - Cells(i, 15).Value
Next
End Sub
 
Upvote 0
try below code

Code:
Sub Substract_Column()
Dim i As Integer
For i = 1 To Cells(Rows.Count, 3).End(xlUp).Row
    Cells(i, 19).Value = Cells(i, 3).Value - Cells(i, 15).Value
Next
End Sub

I tried the code but a get a runtime error '13' - type mismatch on the "Cells(i, 19).Value = Cells(i, 3).Value - Cells(i, 15).Value" portion.

MR
 
Upvote 0
I tried the code but a get a runtime error '13' - type mismatch on the "Cells(i, 19).Value = Cells(i, 3).Value - Cells(i, 15).Value" portion.

MR

can you please post your data what you have in you column C and O

post some sample data
 
Upvote 0
You are finding your last row based on column S. Column S might be empty before the macro.
This bases the last row on column C.
Code:
Sub test()

    With Sheet1.Range("C:S")
        With Range(.Cells(Rows.Count, 1).End(xlUp), .Cells(2, .Columns.Count))
            With .Columns(.Columns.Count)
                .FormulaR1C1 = "=RC3-RC15"
                .Value = .Value
            End With
        End With
    End With

End Sub
 
Upvote 0
Here is one more way...
Code:
Sub CminusO()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "C").End(xlUp).Row
  Range("S2:S" & LastRow) = Evaluate("C2:C" & LastRow & "-O2:O" & LastRow)
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top