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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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.
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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) ?
 

parsec

Board Regular
Joined
Aug 10, 2002
Messages
111

ADVERTISEMENT

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
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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
 

parsec

Board Regular
Joined
Aug 10, 2002
Messages
111

ADVERTISEMENT

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
 

kattharris

New Member
Joined
Jun 2, 2003
Messages
22
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
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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)"
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,145
Members
417,010
Latest member
jnuss03

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
Top