Dear all,
How to write the VBA codes of Subtract Formula ,
-->A3 minus A2,
A4 minus A3,
..... in col A?
Many thanks!
Dear all,
How to write the VBA codes of Subtract Formula ,
-->A3 minus A2,
A4 minus A3,
..... in col A?
Many thanks!
What do you mean?
Answer = Range("A3").Value-Range("A2).Value Range("X1").Formula = "=A3-A2")
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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!
OK, Try
Sub Minus() ActiveCell.FormulaR1C1 = "=RC[1]-R[-1]C[1]" ActiveCell.Offset(1).Select End Sub
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I'm still unsure what you mean.
Range("A3") = Range("B4")-Range("B3")
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?
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
As an example
Function minus(num1 As Long, num2 As Long) minus = num1 - num2 End Function
Franck
HI,
I've got around 20000 rows of data, it seems the looping is quite low...
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.
http://www.mrexcel.com/tip068.shtml
Peace of mind is found in some of the strangest places.
THANKS!
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi MD,
This one is really useful! Thanks! ^^