Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box question/help
Is there a way using some code attached to my code given below that will
carry out this formula =IF(P19="L",$S$6*$Z$1,IF(P19="W",($R$4*5)-$R$4,"0")) in the cell directly under the cell the user input a value into eg The user inputs a number into cell P19, my input box will then be displayed and I want the formula above calculated and the result placed into cell P20 as this is directly under cell P19! my code thus far Code: Sub Input_box() UserValue = InputBox("Unit Value?") Cells(1, 26).Value = UserValue End Sub thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box question/help
Sub Input_box()
UserValue = InputBox("Unit Value?") With Cells(1, 26) .Value = UserValue .Offset(1,0).Formula = "=IF(P19=""L"",$S$6*$Z$1,IF(P19=""W"",($R$4*5)-$R$4,""0""))" End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Anthony" wrote in message ... Is there a way using some code attached to my code given below that will carry out this formula =IF(P19="L",$S$6*$Z$1,IF(P19="W",($R$4*5)-$R$4,"0")) in the cell directly under the cell the user input a value into eg The user inputs a number into cell P19, my input box will then be displayed and I want the formula above calculated and the result placed into cell P20 as this is directly under cell P19! my code thus far Code: Sub Input_box() UserValue = InputBox("Unit Value?") Cells(1, 26).Value = UserValue End Sub thanks in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box question/help
Right click on the worksheet tab and select view code. In the resulting
module put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$P$19" Then v = Evaluate("IF(P19=""L"",$S$6*$Z$1," & _ "IF(P19=""W"",($R$4*5)-$R$4,""0""))") uservalue = InputBox("Value to be entered will be " & _ v) Application.EnableEvents = False Target.Offset(1, 0).Value = v End If Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Anthony" wrote: Is there a way using some code attached to my code given below that will carry out this formula =IF(P19="L",$S$6*$Z$1,IF(P19="W",($R$4*5)-$R$4,"0")) in the cell directly under the cell the user input a value into eg The user inputs a number into cell P19, my input box will then be displayed and I want the formula above calculated and the result placed into cell P20 as this is directly under cell P19! my code thus far Code: Sub Input_box() UserValue = InputBox("Unit Value?") Cells(1, 26).Value = UserValue End Sub thanks in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
input box question/help
If you actually want to prompt for a value by running a macro (not what you
described although what you described is confusing) then: Sub Input_Box() uservalue = InputBox("End single letter Choice: ") Cells(19,"P").Value = UserValue v = Evaluate("IF(P19=""L"",$S$6*$Z$1," & _ "IF(P19=""W"",($R$4*5)-$R$4,""0""))") Application.EnableEvents = False Cells(19,"P").Offset(1, 0).Value = v End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote: Right click on the worksheet tab and select view code. In the resulting module put in code like this: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$P$19" Then v = Evaluate("IF(P19=""L"",$S$6*$Z$1," & _ "IF(P19=""W"",($R$4*5)-$R$4,""0""))") uservalue = InputBox("Value to be entered will be " & _ v) Application.EnableEvents = False Target.Offset(1, 0).Value = v End If Application.EnableEvents = True End Sub -- Regards, Tom Ogilvy "Anthony" wrote: Is there a way using some code attached to my code given below that will carry out this formula =IF(P19="L",$S$6*$Z$1,IF(P19="W",($R$4*5)-$R$4,"0")) in the cell directly under the cell the user input a value into eg The user inputs a number into cell P19, my input box will then be displayed and I want the formula above calculated and the result placed into cell P20 as this is directly under cell P19! my code thus far Code: Sub Input_box() UserValue = InputBox("Unit Value?") Cells(1, 26).Value = UserValue End Sub thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Input Box Question | Excel Discussion (Misc queries) | |||
Input Box Question | Excel Discussion (Misc queries) | |||
Input Box question | Excel Programming | |||
Input Box Question with Code | Excel Programming | |||
Input box question | Excel Programming |