input box question/help

[Anthony]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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