I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.

Try this small macro:

Sub uservalue()
x = Application.InputBox(prompt:="enter the value", Type:=1)
Range("A3").Formula = "=" & x & "*A1"
End Sub


--
Gary''s Student - gsnu200789


"R Nelson" wrote:

I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.

Thanks, that is exactly what I was looking for!!!

"Gary''s Student" wrote:

Try this small macro:

Sub uservalue()
x = Application.InputBox(prompt:="enter the value", Type:=1)
Range("A3").Formula = "=" & x & "*A1"
End Sub


--
Gary''s Student - gsnu200789


"R Nelson" wrote:

I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.

Okay, that didn't work like I first thought. The sub works great, but let me
better explain what I would like to do..

I have a dollar value in A1, and I would like to be able to let the user
spread that value throughout cells B1:H1 (across the row) by percentage.

So it would look like this if the user input 25% in each cell.

A1 B1 C1 D1 E1
100,000 25000 25000 25000 25000

Also, I am doing this same activity with values all down column A (with
different spread values throughout.

And...I need to be able to have the macro run when the user clicks on each
cell.

Is this possible?

"Gary''s Student" wrote:

Try this small macro:

Sub uservalue()
x = Application.InputBox(prompt:="enter the value", Type:=1)
Range("A3").Formula = "=" & x & "*A1"
End Sub


--
Gary''s Student - gsnu200789


"R Nelson" wrote:

I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.

If the user inputs 100% we will only fill B1.
If the user inputs 50% we will fill B1 & C1.
If the user inputs 33% we will fill B1 & C1 & D1.

What if the user inputs 60% ???
--
Gary''s Student - gsnu200789


"R Nelson" wrote:

Okay, that didn't work like I first thought. The sub works great, but let me
better explain what I would like to do..

I have a dollar value in A1, and I would like to be able to let the user
spread that value throughout cells B1:H1 (across the row) by percentage.

So it would look like this if the user input 25% in each cell.

A1 B1 C1 D1 E1
100,000 25000 25000 25000 25000

Also, I am doing this same activity with values all down column A (with
different spread values throughout.

And...I need to be able to have the macro run when the user clicks on each
cell.

Is this possible?

"Gary''s Student" wrote:

Try this small macro:

Sub uservalue()
x = Application.InputBox(prompt:="enter the value", Type:=1)
Range("A3").Formula = "=" & x & "*A1"
End Sub


--
Gary''s Student - gsnu200789


"R Nelson" wrote:

I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.

That is exactly my problem. B1:E1 are dates, so we are spreading that value
across dates, but not necessary all of them. It could be 60% in C1, 40% in E1
and 0% in B1 and D1.

Basically I'm doing a cash flow spread, which I typically have to do
manually for payment terms of each pay item, but if I can get this idea
working, then it expedites the cost spread process and allows other users to
adjust easily in the future.

"Gary''s Student" wrote:

If the user inputs 100% we will only fill B1.
If the user inputs 50% we will fill B1 & C1.
If the user inputs 33% we will fill B1 & C1 & D1.

What if the user inputs 60% ???
--
Gary''s Student - gsnu200789


"R Nelson" wrote:

Okay, that didn't work like I first thought. The sub works great, but let me
better explain what I would like to do..

I have a dollar value in A1, and I would like to be able to let the user
spread that value throughout cells B1:H1 (across the row) by percentage.

So it would look like this if the user input 25% in each cell.

A1 B1 C1 D1 E1
100,000 25000 25000 25000 25000

Also, I am doing this same activity with values all down column A (with
different spread values throughout.

And...I need to be able to have the macro run when the user clicks on each
cell.

Is this possible?

"Gary''s Student" wrote:

Try this small macro:

Sub uservalue()
x = Application.InputBox(prompt:="enter the value", Type:=1)
Range("A3").Formula = "=" & x & "*A1"
End Sub


--
Gary''s Student - gsnu200789


"R Nelson" wrote:

I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.

Now I get it!

Check back tomorrow....We will let the user double-click cols B thru E (or B
thru anything). The user will supply the % and the cell will receive that
percentage of the col A value. For example, if the user is on row 13 and
double-clicks D13, they will input a percentage ( say 13.538%) and D13 will
get the formula:
=.13538*A1


REMEMBER:
You STILL have to be concerned with the user picking a set of percentages
that do not add up to 100%
--
Gary''s Student - gsnu200789


"R Nelson" wrote:

That is exactly my problem. B1:E1 are dates, so we are spreading that value
across dates, but not necessary all of them. It could be 60% in C1, 40% in E1
and 0% in B1 and D1.

Basically I'm doing a cash flow spread, which I typically have to do
manually for payment terms of each pay item, but if I can get this idea
working, then it expedites the cost spread process and allows other users to
adjust easily in the future.

"Gary''s Student" wrote:

If the user inputs 100% we will only fill B1.
If the user inputs 50% we will fill B1 & C1.
If the user inputs 33% we will fill B1 & C1 & D1.

What if the user inputs 60% ???
--
Gary''s Student - gsnu200789


"R Nelson" wrote:

Okay, that didn't work like I first thought. The sub works great, but let me
better explain what I would like to do..

I have a dollar value in A1, and I would like to be able to let the user
spread that value throughout cells B1:H1 (across the row) by percentage.

So it would look like this if the user input 25% in each cell.

A1 B1 C1 D1 E1
100,000 25000 25000 25000 25000

Also, I am doing this same activity with values all down column A (with
different spread values throughout.

And...I need to be able to have the macro run when the user clicks on each
cell.

Is this possible?

"Gary''s Student" wrote:

Try this small macro:

Sub uservalue()
x = Application.InputBox(prompt:="enter the value", Type:=1)
Range("A3").Formula = "=" & x & "*A1"
End Sub


--
Gary''s Student - gsnu200789


"R Nelson" wrote:

I don't even know if this can be done, but I would like to request the user
to define the value of a variable within a formula without having to
reference a separate data table or cell

Example imaginary formula:

A3 =(USER DEFINED VALUE) * A1

Such that the user inputs the value direction into the cell but the shown
value is the fuction result.