Attach formula in a button

parsec

Board Regular
Joined
Aug 10, 2002
Messages
111
Hello to all

Is any way that I can attach a formula in to button, save it in my personal file and applied as need.

Thank you
John
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hello,

Yes, create a macro in the usual way, and enter the following code

Code:
Sub form()
A = ActiveCell.Value
A = A * 10
ActiveCell.Value = A
End Sub

CHange the formula as required,

Add the button to the tool bar, assign the macro to it, and when your cursor is in the relevant cell press the button e.g. 5 will become 50.
 
Upvote 0
Yes, but is the formula going to change?

i.e. do you want to add a different range each time, so if you are in cell A10 the formula should be =sum(A1:A9) and if you are in cell A50 the formula should be =sum(A1:A49) ?
 
Upvote 0
The advice works but the issue that I am facing is that when I build a macro
I need to format the column as 000000000000, I have use the custom format but is not present in the macro what am I doing wrong

thank you
John
 
Upvote 0
Hello,

Do you mean something like this?

Code:
Sub form()
A = ActiveCell.Value
A = A * 10
ActiveCell.Value = A
Columns(ActiveCell.Column).Select
Selection.NumberFormat = "000000000000"
End Sub
 
Upvote 0
Do you think that will work for this kind of formula?

=VLOOKUP(B1,Sheet2!A1:B16000,2,0)

Thank you for all your help

John
 
Upvote 0
This might sound totally idiotic, but how do you add the button to the toolbar? Do you want to use a command button or just a regular button?
Katt
 
Upvote 0
parsec said:
Do you think that will work for this kind of formula?

=VLOOKUP(B1,Sheet2!A1:B16000,2,0)

Thank you for all your help

John

You can add this formula to a macro, but will have to change it slightly, assuming you don't want to always to look up B1.

This will look up the cell to the left of the cell you are in, so if you are in C10 it will lookup B10 and if you are in E5 it will lookup D5.

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Sheet2!R1C1:R16000C2,2,0)"

This will lookup column B in the row you are in, so if you are any column in row 10 it will look up B10

Code:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,Sheet2!R1C1:R16000C2,2,0)"
 
Upvote 0
kattharris said:
This might sound totally idiotic, but how do you add the button to the toolbar? Do you want to use a command button or just a regular button?
Katt
Right mouse over a toolbar and select customize, select the Commands tab, in categories select Macros, click and drag the Custon button onto your toolbar, then right mouse over it and select Assign Macro. (you can also change the icon and name.
 
Upvote 0

Forum statistics

Threads
1,211,685
Messages
6,103,290
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top