Posted to microsoft.public.excel.worksheet.functions
|
|
Can I request user in input variable value within formula?
I got it working. Simply an error on my part in transposing to the worksheet.
Thanks again very much!!!
"Gary''s Student" wrote:
Here is the macro. Double-click anywhere from B1 thru Z100 and the macro
runs. It asks for a percentage, so have the user enter stuff like 45%.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Set r = Range("B1:Z100")
If Intersect(Target, r) Is Nothing Then Exit Sub
Cancel = True
rr = Target.Row
x = Application.InputBox(prompt:="Enter a percentage (like 15%)", Type:=2)
Target.Formula = "=A" & rr & "*" & x
End Sub
Because it is worksheet code, it is very easy to install and automatic to use:
1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window
If you have any concerns, first try it on a trial worksheet.
If you save the workbook, the macro will be saved with it.
To remove the macro:
1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window
To learn more about Event Macros (worksheet code), see:
http://www.mvps.org/dmcritchie/excel/event.htm
--
Gary''s Student - gsnu200789
"R Nelson" wrote:
Awesome!
Yes, if I can build in the ability to spread these values across the row (A1
to B1:Z1, A2 to B2:Z2, etc..) that will be excellent! As for adding up to
100% of the value, I'll just have a sum column in there as a check. If the
user in question doesn't know to distribute the full 100% then he has bigger
problems than the spreadsheet! :)
Thanks so much for this help.
"Gary''s Student" wrote:
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.
|