# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  VBA CODE to Subtract two cells

## Kevin Baker

Would like to use VB Code to do the following:

=F2-H2

Thanks,
Kevin

----------


## Doug Glancy

Kevin,

Try this something like this:

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

hth,

Doug

"Kevin Baker" <shcbaker@cox.net> wrote in message
news:UPbee.660$It1.521@lakeread02...
> Would like to use VB Code to do the following:
>
> =F2-H2
>
> Thanks,
> Kevin
>
>

----------


## zackb

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"

--
Regards,
Zack Barresse, aka firefytr

"Kevin Baker" <shcbaker@cox.net> wrote in message
news:UPbee.660$It1.521@lakeread02...
> Would like to use VB Code to do the following:
>
> =F2-H2
>
> Thanks,
> Kevin
>
>

----------


## Kevin Baker

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

"Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
news:O%23KWvkPUFHA.1200@TK2MSFTNGP10.phx.gbl...
> Kevin,
>
> Try this something like this:
>
> = Worksheets("Sheet1").Range("F2").Value2 - Range("H2").Value2
>
> hth,
>
> Doug
>
> "Kevin Baker" <shcbaker@cox.net> wrote in message
> news:UPbee.660$It1.521@lakeread02...
>> Would like to use VB Code to do the following:
>>
>> =F2-H2
>>
>> Thanks,
>> Kevin
>>
>>
>
>

----------


## Doug Glancy

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 Baker" <shcbaker@cox.net> wrote in message
news:Ogcee.663$It1.521@lakeread02...
> 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
>
> "Doug Glancy" <nobodyhere@replytogroup.com> wrote in message
> news:O%23KWvkPUFHA.1200@TK2MSFTNGP10.phx.gbl...
> > Kevin,
> >
> > Try this something like this:
> >
> > = Worksheets("Sheet1").Range("F2").Value2 - Range("H2").Value2
> >
> > hth,
> >
> > Doug
> >
> > "Kevin Baker" <shcbaker@cox.net> wrote in message
> > news:UPbee.660$It1.521@lakeread02...
> >> Would like to use VB Code to do the following:
> >>
> >> =F2-H2
> >>
> >> Thanks,
> >> Kevin
> >>
> >>
> >
> >
>
>

----------


## Kevin Baker

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
"zackb" <firefytr@vbaexpress.com> wrote in message
news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
> 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"
>
> --
> Regards,
> Zack Barresse, aka firefytr
>
> "Kevin Baker" <shcbaker@cox.net> wrote in message
> news:UPbee.660$It1.521@lakeread02...
>> Would like to use VB Code to do the following:
>>
>> =F2-H2
>>
>> Thanks,
>> Kevin
>>
>>
>
>

----------


## Kevin Baker

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



"Kevin Baker" <shcbaker@cox.net> wrote in message
news:vWcee.667$It1.55@lakeread02...
> 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
> "zackb" <firefytr@vbaexpress.com> wrote in message
> news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
>> 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"
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr
>>
>> "Kevin Baker" <shcbaker@cox.net> wrote in message
>> news:UPbee.660$It1.521@lakeread02...
>>> Would like to use VB Code to do the following:
>>>
>>> =F2-H2
>>>
>>> Thanks,
>>> Kevin
>>>
>>>
>>
>>
>
>

----------


## zackb

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?

--
Regards,
Zack Barresse, aka firefytr

"Kevin Baker" <shcbaker@cox.net> wrote in message
news:vWcee.667$It1.55@lakeread02...
> 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
> "zackb" <firefytr@vbaexpress.com> wrote in message
> news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
>> 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"
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr
>>
>> "Kevin Baker" <shcbaker@cox.net> wrote in message
>> news:UPbee.660$It1.521@lakeread02...
>>> Would like to use VB Code to do the following:
>>>
>>> =F2-H2
>>>
>>> Thanks,
>>> Kevin
>>>
>>>
>>
>>
>
>

----------


## zackb

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?

--
Regards,
Zack Barresse, aka firefytr

"Kevin Baker" <shcbaker@cox.net> wrote in message
news:P4dee.669$It1.624@lakeread02...
> 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
>
>
>
> "Kevin Baker" <shcbaker@cox.net> wrote in message
> news:vWcee.667$It1.55@lakeread02...
>> 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
>> "zackb" <firefytr@vbaexpress.com> wrote in message
>> news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
>>> 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"
>>>
>>> --
>>> Regards,
>>> Zack Barresse, aka firefytr
>>>
>>> "Kevin Baker" <shcbaker@cox.net> wrote in message
>>> news:UPbee.660$It1.521@lakeread02...
>>>> Would like to use VB Code to do the following:
>>>>
>>>> =F2-H2
>>>>
>>>> Thanks,
>>>> Kevin
>>>>
>>>>
>>>
>>>
>>
>>
>
>

----------


## Kevin Baker

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

"zackb" <firefytr@vbaexpress.com> wrote in message
news:edFDpOQUFHA.3952@TK2MSFTNGP15.phx.gbl...
> 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?
>
> --
> Regards,
> Zack Barresse, aka firefytr
>
> "Kevin Baker" <shcbaker@cox.net> wrote in message
> news:P4dee.669$It1.624@lakeread02...
>> 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
>>
>>
>>
>> "Kevin Baker" <shcbaker@cox.net> wrote in message
>> news:vWcee.667$It1.55@lakeread02...
>>> 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
>>> "zackb" <firefytr@vbaexpress.com> wrote in message
>>> news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
>>>> 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"
>>>>
>>>> --
>>>> Regards,
>>>> Zack Barresse, aka firefytr
>>>>
>>>> "Kevin Baker" <shcbaker@cox.net> wrote in message
>>>> news:UPbee.660$It1.521@lakeread02...
>>>>> Would like to use VB Code to do the following:
>>>>>
>>>>> =F2-H2
>>>>>
>>>>> Thanks,
>>>>> Kevin
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

----------


## Zack Barresse

What part of this code doesn't work for you?

--
Regards,
Zack Barresse, aka firefytr


"Kevin Baker" <shcbaker@cox.net> wrote in message
news:P4dee.669$It1.624@lakeread02...
> 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
>
>
>
> "Kevin Baker" <shcbaker@cox.net> wrote in message
> news:vWcee.667$It1.55@lakeread02...
>> 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
>> "zackb" <firefytr@vbaexpress.com> wrote in message
>> news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
>>> 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"
>>>
>>> --
>>> Regards,
>>> Zack Barresse, aka firefytr
>>>
>>> "Kevin Baker" <shcbaker@cox.net> wrote in message
>>> news:UPbee.660$It1.521@lakeread02...
>>>> Would like to use VB Code to do the following:
>>>>
>>>> =F2-H2
>>>>
>>>> Thanks,
>>>> Kevin
>>>>
>>>>
>>>
>>>
>>
>>
>
>

----------


## Rowan

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" wrote:

> Would like to use VB Code to do the following:
>
> =F2-H2
>
> Thanks,
> Kevin
>
>
>

----------


## Bob Phillips

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)


"Kevin Baker" <shcbaker@cox.net> wrote in message
news:P4dee.669$It1.624@lakeread02...
> 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
>
>
>
> "Kevin Baker" <shcbaker@cox.net> wrote in message
> news:vWcee.667$It1.55@lakeread02...
> > 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
> > "zackb" <firefytr@vbaexpress.com> wrote in message
> > news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
> >> 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"
> >>
> >> --
> >> Regards,
> >> Zack Barresse, aka firefytr
> >>
> >> "Kevin Baker" <shcbaker@cox.net> wrote in message
> >> news:UPbee.660$It1.521@lakeread02...
> >>> Would like to use VB Code to do the following:
> >>>
> >>> =F2-H2
> >>>
> >>> Thanks,
> >>> Kevin
> >>>
> >>>
> >>
> >>
> >
> >
>
>

----------


## Don Guillett

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

--
Don Guillett
SalesAid Software
donaldb@281.com
"Kevin Baker" <shcbaker@cox.net> wrote in message
news:vWcee.667$It1.55@lakeread02...
> 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
> "zackb" <firefytr@vbaexpress.com> wrote in message
> news:OY8HDoPUFHA.3152@TK2MSFTNGP12.phx.gbl...
> > 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"
> >
> > --
> > Regards,
> > Zack Barresse, aka firefytr
> >
> > "Kevin Baker" <shcbaker@cox.net> wrote in message
> > news:UPbee.660$It1.521@lakeread02...
> >> Would like to use VB Code to do the following:
> >>
> >> =F2-H2
> >>
> >> Thanks,
> >> Kevin
> >>
> >>
> >
> >
>
>

----------


## Zack Barresse

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" <Rowan@discussions.microsoft.com> wrote in message
news:3923C337-8257-4D57-881C-0A013D3573D2@microsoft.com...
> 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" wrote:
>
>> Would like to use VB Code to do the following:
>>
>> =F2-H2
>>
>> Thanks,
>> Kevin
>>
>>
>>

----------


## wietse

Hi guys,

I am looking for the same code as what you guys are talking about here but I have tried all the codes that you guys discussed but it isn't working?

I am not to clued up with VB so that might be the problem as well  :Smilie: 

Can you guys please help

Sorry, should mention that I would like the values in column B to be subtracted from column A and give the answer in column C. The code must run from the first line of column C to the last line. So each cell must have it's own answer in column C - an example would be the closing km's of a vehicle subtracted by the opening km's to give you the total km's driven. hope this makes sense  :Smilie:

----------

