PDA

View Full Version : Solved: Customize a Formula Based on User Input



bturner2
06-16-2004, 07:32 AM
I am new to VB ( so be nice <G> )and I am hoping someone can help solve a problem I can?t figure out.
What I am trying to do is to customize a formula based on input from a user. Can anyone suggest how I can take the input that I have get from the user and plug it into and write a formula into a cell. It is important that the formula be written to a cell rather than doing the If statement with vb.

Here is the formula. #REF will equal the input that was given by the user.


=IF(#REF!G2>79,"Discharge",IF(#REF!G2>71,"Final",IF(#REF!G2>63, "Second", IF(#REF!G2>55,"First",IF(#REF!G2>31," Informational","")))))


Here is a test script that I am using.

Sub TEST()
MyInput = InputBox("Enter Sheet Name")
Sheets("Home").Select
Range("k1").Select
ActiveCell.FormulaR1C1 = _
"=IF(" & MyInput & "!G2>79,""Discharge"", _
IF(" & MyInput & "!G2>71,""Final""," & "IF(" & MyInput & "!G2>63, _
""Second"", IF(" & MyInput & "!G2>55,""First""," & _
"IF(" & MyInput & "!G2>31,""Informational"","""")))))"
End Sub





Here is the result that is being placed in the cell. The text is being placed in the cell but there are quotes around the cell references [ ?G2? ] and this is causing the formula to croak.



=IF(arggg!'G2'>79,"Discharge",IF(arggg!'G2'>71,"Final",IF(arggg!'G2'>63,"Second",IF(arggg!'G2'>55,"First",IF(arggg!'G2'>31,"Informational","")))))



Any help would be greatly appreciated. I have already spent several hours trying to figure things (reading mostly) out for this project and since it is being done on my own time I would like to spend as little time as needed.

mark007
06-16-2004, 07:42 AM
Try:


x= InputBox("Enter Sheet Name")
Sheets("Home").Range("k1").Formula "=IF(" & x & "!G2>79,""Discharge"",IF(" & x & "!G2>71,""Final"",IF(" & x & "!G2>63,""Second"",IF(" & x & "!G2>55,""First"",IF(" & x & "!G2>31,"" Informational"","""")))))"


:)

Steiner
06-16-2004, 07:58 AM
Hi bturner2,
since it looks like you aren't using the R1C1-Syntax anyway (at least as far as I can tell), why don't you just assign it using .Formula instead?

I just tried your code with just removing the "R1C1", and it worked (Excel97)

NateO
06-16-2004, 08:21 AM
Hello, avoid using 5 nested ifs here, the following uses a binary search algorithm and will blow the doors of the nested if logic in terms of performance.


Sub TEST()
Dim myInput As Variant
Let myInput = Application.InputBox("Enter Sheet Name")
If Not myInput = False Then _
Sheets("Home").Range("k1").Formula = _
"=VLOOKUP(" & myInput & "!g2,{-10000,"""";32,""Informational"";56,""First"";64,""Second"";72,""Final"";80,""Discharge""},2,1)"
End Sub

Bon chance. :)

Zack Barresse
06-16-2004, 08:33 AM
As an alternative...


Sub testing()

Dim sht As String, MyInput As String
sht = InputBox("Enter Sheet Name")
MyInput = Sheets(sht).Range("G2")
Sheets("Home").Select
Range("K1").Select

Select Case MyInput
Case Is > 79
Selection.Value = "Discharge"
Case Is > 71
Selection.Value = "Final"
Case Is > 63
Selection.Value = "Second"
Case Is > 55
Selection.Value = "First"
Case Is > 31
Selection.Value = "Informational"
Case Else
Selection.Value = ""
End Select

End Sub

NateO
06-16-2004, 08:49 AM
Also, you can pass a value with Vlookup():



Sub tester()
Dim myInput As Variant
Let myInput = Application.InputBox("Enter Sheet Name")
On Error Resume Next
If Not myInput = False Then _
Sheets("Home").Range("k1").Value = _
WorksheetFunction.VLookup(Sheets(myInput).Range("g2").Value, _
[{-10000,"";32,"Informational";56,"First";64,"Second";72,"Final";80,"Discharge"}], 2, 1)
If CBool(Err.Number) Then MsgBox "Bad Sheet Name"
End Sub

Zack Barresse
06-16-2004, 08:54 AM
Also, you can pass a value with Vlookup():...


If they've got everything in a table to lookup (why I went with Case Select :rolleyes: ). Nice job though! That's excellent! :)

edit: scratch that table bit. Just waking up and re-read your post Nate. oops!

NateO
06-16-2004, 09:01 AM
Hello Zack,

Right, the 2nd argument is the array to lookup ('table').

Thanks and have a nice day. :)

Incidentally, with the Select Case approach, which there's absolutely nothing wrong with, avoid selecting the range, unless there's a presentation aspect/reason to do so. Perhaps there is... :)

Zack Barresse
06-16-2004, 09:04 AM
Please see my edit ( :eek: ). I realized that after I posted, ugh. Slow morning. Very nice though! I'm trying to work on my Case Select skills anyway - as they're near non-existent at the moment..

Sorry 'bout the confusion.

And as for the selection, I almost put that as a variable and use that in the code instead of *hard* selecting it, but I was retaining some of the original code posted. Kind of a mix I guess.

Zack Barresse
06-16-2004, 10:24 AM
bturner2,

Did any of these solutions work for you? If so, tell us which one and we'll mark this thread as solved. If not, let us know! :)

bturner2
06-17-2004, 12:48 PM
WOW! You guys are fast. I have had a similar message posted on several other msg boards with no help. Low and behold I come back her and I have all sorts of help! Guess where I will be coming to from now on.



Yes I have got it working now. The answer Steiner posted was the one that would best suite my needs. I did learn allot from the answers and will look at how they are suggesting I try to do the same thing. The main reason I am not using some of the other answers is because this formula is doing nothing more than giving a quick visual aid to a manager concerning sick leave. With approx 200 employees this is the easiest way I have found to do the limited function or a visual aid. I guess I should have explained in a little better detail of what I was doing and why.



Thanks for all the help. I am sure you will be seeing me post again soon J

mark007
06-17-2004, 01:38 PM
Glad you like the forum!

:D

byundt
06-19-2004, 02:56 PM
You could also use the INDIRECT function to avoid using VBA altogether. To do this, you will need to reference a cell on the worksheet that contains the user's choice for worksheet name. Let's suppose this is cell F2. NateO's VLOOKUP formula can then be placed directly in the cell:
=VLOOKUP(INDIRECT(F2 & "!G2"),{-10000,"";31,"Informational";55,"First";63,"Second";71,"Final";79,"Discharge"},2)