VBA CODE to Subtract two cells

  • Thread starter Thread starter Doug Glancy
  • Start date Start date
D

Doug Glancy

Kevin,

Try this something like this:

= Worksheets("Sheet1").Range("F2").Value2 - Range("H2").Value2

hth,

Doug
 
Hi,

Depending on where you want it to go, I'm making some assumptions here ...

Range("A1").Value = Range("F2").value - Range("H2").value

or

Range("A1").formula = "=F2-H2"
 
Kevin,

The code would be:

=Worksheets("Sheet1").Range("I2") =
Worksheets("Sheet1").Range("F2").Value2 - Range("H2").Value2

Where you put it depends on what you're trying to do. As it's described so
far, the best thing would be not to use VBA and to just have the formula in
the worksheet in I2, but I'm guessing there's more to it than that. Give us
a little more description of what you are doing and somebody will help.

Doug
 
Kevin,

Are you saying you want a formula in each cell going down performing the
calculation on all cells in the respective columns? What is the end result
you are looking for here?
 
Does this not suit your needs? If not, can you explain what you need
differently here? Will this be dependent on some rows? Any headers? Only
to a certain row?
 
Kevin

There appears to be a bit of confusion as to what you are asking for here.
The way I understand it is you want to populate Column I with the RESULTS of
subtracting column H from Column F. If this is the case then run the macro
below. Save your work first just in case. I did note that at some stage the
request changed to Column F - Column G. I have provided for this as well.

To subtract column H from column F

Sub PopCol()

Dim endRow As Long

endRow = Cells(Rows.Count, 6).End(xlUp).Row
With Range(Cells(2, 9), Cells(endRow, 9))
.FormulaR1C1 = "=RC[-3]-RC[-1]" 'this is F-H
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False

End Sub

To subtract column G from Column F:

Sub PopCol()

Dim endRow As Long

endRow = Cells(Rows.Count, 6).End(xlUp).Row
With Range(Cells(2, 9), Cells(endRow, 9))
.FormulaR1C1 = "=RC[-3]-RC[-2]" 'this is F-G
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False

End Sub

Hope this helps
Rowan
 
Is the problem multiple fired events?

Try this

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 6 And .Value > 1 And _
.Offset(0, 2).Value > 1 Then
.Offset(0, 3).Value = .Value - .Offset(0, 2).Value
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
use this to put the value in all.VERY fast & no formula left

sub doformulas
set mr=range("i2:i"&cells(rows.count,"i").end(xlup).row)
with mr
..formula="=f2-g2"
..formula=.value
end with
end sub
 
Fwiw, try not to use ..

..Copy
..Paste...

Instead, use just a value ..

..Value = .Value

And it's faster. I also agree about the multiple firing events. Changing a
cells value/formula will trigger the event for that cell as well. If you
don't want to create a very inefficient loop, turn off events, then back on.

--
Regards,
Zack Barresse, aka firefytr

Rowan said:
Kevin

There appears to be a bit of confusion as to what you are asking for here.
The way I understand it is you want to populate Column I with the RESULTS
of
subtracting column H from Column F. If this is the case then run the macro
below. Save your work first just in case. I did note that at some stage
the
request changed to Column F - Column G. I have provided for this as well.

To subtract column H from column F

Sub PopCol()

Dim endRow As Long

endRow = Cells(Rows.Count, 6).End(xlUp).Row
With Range(Cells(2, 9), Cells(endRow, 9))
.FormulaR1C1 = "=RC[-3]-RC[-1]" 'this is F-H
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False

End Sub

To subtract column G from Column F:

Sub PopCol()

Dim endRow As Long

endRow = Cells(Rows.Count, 6).End(xlUp).Row
With Range(Cells(2, 9), Cells(endRow, 9))
.FormulaR1C1 = "=RC[-3]-RC[-2]" 'this is F-G
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False

End Sub

Hope this helps
Rowan


Kevin Baker said:
Would like to use VB Code to do the following:

=F2-H2

Thanks,
Kevin
Click to expand...
Click to expand...
 
Doug,

Thanks, but I guess I didn't give enough info.

On my sheet I cell I2 needs to subtract cell F2 from cell H2.

I right click on the excel icon and select view code.. what do I do from
there?

Thanks,
Kevin
 
Hi all.

It seems when I use a formula (the formula would need to be in the entire
column of "I") the spreadsheet file size is very large, however, it seems
when I use VBA code the file size doesn't grow as much.

In my spreadsheet column "I" would be the difference between the value in
Column "F" and Column "G".

Does that make sense?

Thanks for all your help,
Kevin
 
Here is what I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 And Target.Value > 1 And Target.Offset(0, 2).Value > 1
Then
Target.Offset(0, 3).Value = Target.Value - Target.Offset(0, 2).Value
End If
End Sub
 
Zack,

I know I could put the following formula in Column I2 and copy it all the
way down the sheet in all cells to I6553:
=F2-H2
But doing this makes my spreadsheet HUGE, so I was looking for a way to do
the above in VBA vice using a formula.

Thanks again,
Kevin
 
Back
Top