Customize a Formula Based on User Input

[bturner2]

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]

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]

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]

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]

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]

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]

Also, you can pass a value with Vlookup():...

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

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

[NateO]

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]

Please see my edit ( ). 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]

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]

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]

Glad you like the forum!

[byundt]

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)