Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
Kevin,
Try this something like this: = Worksheets("Sheet1").Range("F2").Value2 - Range("H2").Value2 hth, Doug "Kevin Baker" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" 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" wrote in message ... Kevin, Try this something like this: = Worksheets("Sheet1").Range("F2").Value2 - Range("H2").Value2 hth, Doug "Kevin Baker" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" 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" wrote in message ... 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" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" 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" 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" wrote in message ... 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" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" 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" 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" wrote in message ... 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" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
Would like to use VB Code to do the following:
=F2-H2 Thanks, Kevin |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" wrote in message ... Kevin, Try this something like this: = Worksheets("Sheet1").Range("F2").Value2 - Range("H2").Value2 hth, Doug "Kevin Baker" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" wrote in message ... 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" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" 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" wrote in message ... 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" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA CODE to Subtract two cells
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" wrote in message ... 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" 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" 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" wrote in message ... 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" wrote in message news:UPbee.660$It1.521@lakeread02... Would like to use VB Code to do the following: =F2-H2 Thanks, Kevin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I subtract cells in two different worksheets? | Excel Discussion (Misc queries) | |||
Subtract and multiply as a code ???????? | Excel Discussion (Misc queries) | |||
How do I make cells subtract? | Excel Worksheet Functions | |||
How can I subtract cells in a row | New Users to Excel | |||
how to add or subtract from two cells into one | Excel Worksheet Functions |