How to use an action button for formulas?

[MichaelZ]

I have a matrix of numbers:

a1 b1 c1 d1
a2 b2 c2 d2
a3 b3 c3 d3

I'd like to divide each of the values in this matrix by a particular number
that the user selects, then have the new values in a separate matrix nearby
the original one. One thing, I'd like the user to be able to select the
dividend by simply pressing a button that is specific to each dividend value.
As part of this, please show how to insert the buttons and link each to a
dividend value. Thanks.

 

[Luke M]

First, let's setup your dividend values. Open the forms toolbar by going to
View - Toolsbars - forms. Create some option buttons, 1 for each dividend
value you want. (I'll assume you made 4). Format one of them, and under the
control tab, create a cell link to a blank cell somewhere (I'll use F1).

Now, clicking the buttons will result in an output of 1 - 4. To get the
value you want, let's create a lookup table, with the left column containing
numbers 1 - 4, and the right column containing the value you actually want.
(I'll use H1:I4). Then, in another cell (g1) write this formula:
=LOOKUP(F1,H1:I4)

Now, to create your short macro. Open up the Visual Basic Editor (Alt+F11),
and go to insert - module. Paste this in (modifed accordingly):

'=========
Sub DivideMatrix()
'Cell with lookup formula
Range("G1").Copy
'Range of matrix
Range("A14").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlDivide, SkipBlanks:=False, Transpose:=False
End Sub
'=========

Close out the editor. Back in your workbook, create a button from forms
toolbar and assign your new macro to the button.

That should be it!

 

[MichaelZ]

Luke,
Almost there, 2 things:
1. How do I put the divided values into a matrix next to the original
matrix of values?
2. How do I divide only a single row of values from the original matrix
dependent upon the particular option button I choose? So, in my example
below, if I select option button 1, only the values in row #1 will be
divided, and the output put next to the original matrix.
Hope this makes sense.
Thanks so much for your help.
MZ

 

[Luke M]

If you are creating an output table, it might be easier to skip the macro
idea, and just use formulas.

You could still use option buttons to choose the divedend, and then setup
some checkboxes/ to say which rows to include (checkboxes would prb work
better) Let's say your checkbox(s) to include row 1-4 are in E1:E4
(respecitvely), and the dividend is in F1 (calculated by formula or manually
inputted, your choice

formula in G1:
=IF($E1,A1/$F$1,A1)

Copy down and across as needed.

I would encourage you to play around a little with forms and cell linking,
to help get a feel of how you can use them to pass information to the XL
sheet. If you get stuck, we're here to help.

 

[MichaelZ]

Luke - thanks, you were a great help.
MZ

If you are creating an output table, it might be easier to skip the macro
idea, and just use formulas.

You could still use option buttons to choose the divedend, and then setup
some checkboxes/ to say which rows to include (checkboxes would prb work
better) Let's say your checkbox(s) to include row 1-4 are in E1:E4
(respecitvely), and the dividend is in F1 (calculated by formula or manually
inputted, your choice

formula in G1:
=IF($E1,A1/$F$1,A1)

Copy down and across as needed.

I would encourage you to play around a little with forms and cell linking,
to help get a feel of how you can use them to pass information to the XL
sheet. If you get stuck, we're here to help.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*