Attach formula in a button

[parsec]

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

 

[onlyadrafter]

Hello,

Yes, create a macro in the usual way, and enter the following 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]

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]

Thank you
John

 

[parsec]

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]

Hello,

Do you mean something like this?

Sub form()
A = ActiveCell.Value
A = A * 10
ActiveCell.Value = A
Columns(ActiveCell.Column).Select
Selection.NumberFormat = "000000000000"
End Sub

 

[parsec]

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]

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]

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.

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

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,Sheet2!R1C1:R16000C2,2,0)"

 

[jimboy]

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.