using a vb variable in an excel formula

[wolfpack95]

I have a VB macro variable that I want to reference in an Excel formul
(faulty code below gives me a #NAME? error) . There has to be an eas
way to do this but I haven't found it yet. Thanks in advance.


Sub test()
'
'
Dim ok As String
Dim no As String

String1 = "ok"
String2 = "no"

Range("A1").Select
ActiveCell.FormulaR1C1 = "23"
Range("B1").Select
ActiveCell.FormulaR1C1 = "45"

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20, String1, String2)"

End Su

 

[firefytr]

you can use custom formatting for that, if you'd like, as a non-v
solution.

[>20]"ok";"no";@

or make your own UDF. you cannot do it the way your trying as you ca
only call that from a routine; which will run, complete the code, the
be done. a standard routine cannot be dynamic like that. unless mayb
you called it from a worksheet_change event or something. but it woul
seem to me that an easier solution will work

 

[Frank Kabel]

Hi
try
ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20," & String1 & "," & String2 &
")"

 

[wolfpack95]

Thanks for the replies however, I think my sample code oversimplifie
what I'm trying to do.

String1 and String2 are being calculated in a macro and their value
will need to be used in a Excel formula.

Frank, I copied and pasted your formula but got a syntax error. Pleas
help and thanks again

 

[Frank Kabel]

Hi
try
"=IF(R[-1]C>20," & "" & String1 & "" & "," & "" & String2 & "" & ")"

 

[wolfpack95]

Frank,

Last suggestion gives #NAME? error in excel sheet. I tried widenin
the variable scope with Public declaration for String1 and String2 bu
didn't help.

Any ideas

 

[Frank Kabel]

Hi
sorry,
my fault . Try
"=IF(R[-1]C>20," & """" & String1 & """" & "," & """" & String2 &
"""" & ")"

 

[wolfpack95]

Frank,
ExcelVBA red flags your last suggestion:

ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20," & """" & String1 & """" & ",
& """" & String2 &
"""" & ")"

When I try an integer (first sub below) instead of a string, it works.
So I'm missing something regarding String types or the double-quot
syntax.

I can solve my problem with the integer approach (thanks to your help
but am curious on why the string approach won't work.

Thanks!!


------------------------------------------------------------
Sub testInteger()
' THIS CODE WORKS FINE!
'
Dim Int1 As Integer
Dim Int2 As Integer

Int1 = 15
Int2 = 10

Range("A1").Select
ActiveCell.FormulaR1C1 = "23"
Range("B1").Select
ActiveCell.FormulaR1C1 = "45"

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20, " & Int1 & "," & Int2
")"

End Sub

-------------------------------------------------------------------------
Sub testString()
' THIS CODE DOESN"T WORK
'

Dim String1 As String
Dim String2 As String

String1 = "ok"
String2 = "no"

Range("A1").Select
ActiveCell.FormulaR1C1 = "23"
Range("B1").Select
ActiveCell.FormulaR1C1 = "45"

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20, " & String1 & ","
String2 & ")"


End Su

 

[Frank Kabel]

Hi
works for me (it's all one line)

 

[wolfpack95]

Frank,
ExcelVBA red flags your last suggestion:

ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20," & """" & String1 & """" & ",
& """" & String2 &
"""" & ")"

When I try an integer (first sub below) instead of a string, it works.
So I'm missing something regarding String types or the double-quot
syntax.

I can solve my problem with the integer approach (thanks to your help
but am curious on why the string approach won't work.

Thanks!!


------------------------------------------------------------
Sub testInteger()
' THIS CODE WORKS FINE!
'
Dim Int1 As Integer
Dim Int2 As Integer

Int1 = 15
Int2 = 10

Range("A1").Select
ActiveCell.FormulaR1C1 = "23"
Range("B1").Select
ActiveCell.FormulaR1C1 = "45"

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20, " & Int1 & "," & Int2
")"

End Sub

-------------------------------------------------------------------------
Sub testString()
' THIS CODE DOESN"T WORK
'

Dim String1 As String
Dim String2 As String

String1 = "ok"
String2 = "no"

Range("A1").Select
ActiveCell.FormulaR1C1 = "23"
Range("B1").Select
ActiveCell.FormulaR1C1 = "45"

Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20, " & String1 & ","
String2 & ")"


End Su

 

[wolfpack95]

ahh, works on one line ....thanks a bunch!