View Full Version : [SOLVED:] About Subtract Function
snoopies
06-23-2005, 10:36 AM
Dear all,
How to write the VBA codes of Subtract Formula ,
-->A3 minus A2,
A4 minus A3,
..... in col A?
Many thanks!
Norie
06-23-2005, 11:26 AM
What do you mean?
Answer = Range("A3").Value-Range("A2).Value
Range("X1").Formula = "=A3-A2")
mdmackillop
06-23-2005, 11:28 AM
Try
Sub Minus()
ActiveCell.FormulaR1C1 = "=R[-1]C-R[-2]C"
ActiveCell.Offset(0, 1).Select
End Sub
Assign it to a shortcut key and it will insert the subtract formula in any location. Change the Offset to suit yourself.
snoopies
06-23-2005, 04:50 PM
Sorry for misleading ...
I want to let the value of A3 = B4-B3
A4 = B4-B3
A5 = B5-B4
...............etc.
How to write this? Thanks a lot! :help
mdmackillop
06-23-2005, 05:13 PM
OK, Try
Sub Minus()
ActiveCell.FormulaR1C1 = "=RC[1]-R[-1]C[1]"
ActiveCell.Offset(1).Select
End Sub
Norie
06-23-2005, 05:18 PM
I'm still unsure what you mean.
Range("A3") = Range("B4")-Range("B3")
snoopies
06-23-2005, 11:14 PM
Dear all, thx for help!
Sorry for my poor explaination as my mother lanuage is not english...:P
Hi mdmackillop,
Yes, you can get what I mean... How can I do it further by looping it?
so that I can fill row A with the formula by one bottom click?
mdmackillop
06-24-2005, 12:07 AM
This assumes no gaps in column B
Sub Minus()
Do
ActiveCell.FormulaR1C1 = "=RC[1]-R[-1]C[1]"
ActiveCell.Offset(1).Select
Loop Until ActiveCell.Offset(0, 1) = ""
End Sub
Franck
06-24-2005, 07:32 AM
As an example
Function minus(num1 As Long, num2 As Long)
minus = num1 - num2
End Function
:hi:
snoopies
06-24-2005, 07:02 PM
HI,
I've got around 20000 rows of data, it seems the looping is quite low...:dunno
austenr
06-24-2005, 08:46 PM
Take a look at this link. It tells you how to add a button and assign a macro to it. MD's example works for me providing there is no blanks in col B which would result in an abort of the macro. :hi:
http://www.mrexcel.com/tip068.shtml
snoopies
06-25-2005, 04:56 AM
THANKS!
:yes
mdmackillop
06-25-2005, 05:29 AM
Hi Snoopies,
I wouldn't do a loop for 20,000 rows. The following is much faster (by about 580 times on my PC). If you can more fully explain your requirements in the original question, we can get to the solution faster as well.
Sub Minus()
ActiveCell.FormulaR1C1 = "=RC[1]-R[-1]C[1]"
Range(ActiveCell.Address & ":A" & [B65536].End(xlUp).Row).FillDown
End Sub
snoopies
06-25-2005, 06:04 AM
Hi MD,
This one is really useful! Thanks! ^^
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.