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.

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.