About Subtract Function

[snoopies]

Dear all,

How to write the VBA codes of Subtract Formula ,
-->A3 minus A2,
A4 minus A3,
..... in col A?

Many thanks!

[Norie]

What do you mean?


Answer = Range("A3").Value-Range("A2).Value

Range("X1").Formula = "=A3-A2")

[mdmackillop]

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]

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]

OK, Try


Sub Minus()
ActiveCell.FormulaR1C1 = "=RC[1]-R[-1]C[1]"
ActiveCell.Offset(1).Select
End Sub

[Norie]

I'm still unsure what you mean.


Range("A3") = Range("B4")-Range("B3")

[snoopies]

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]

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]

As an example


Function minus(num1 As Long, num2 As Long)
minus = num1 - num2
End Function

:hi:

[snoopies]

HI,

I've got around 20000 rows of data, it seems the looping is quite low...:dunno

[austenr]

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]

THANKS!
:yes

[mdmackillop]

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]

Hi MD,
This one is really useful! Thanks! ^^