VBA - Use variable from InputBox in formula?

[Paul_Hossler]

You need to construct the formulas a little differently:

This is what you end with the way you have it

"=IF(ISNUMBER(FIND(sReturn,RC[-1])),LEFT(RC[-1],FIND(sReturn,RC[-1])-1),"")"

In VBA, you use 2 double quotes embed a double quote as a character in a string or use Chr(34)


BTW, since I find building formulas a little finicky, I like to build the formula using a separate, testable variable (like sFormula below). It just seems easier to me

Option Explicit
Sub test()
    Dim sReturn As String, sFormula As String
    Dim i As Long
    Range("C3").Select
    i = 0
    sReturn = "ABCDEF"
    
    'wrong
    sFormula = "=IF(ISNUMBER(FIND(sReturn,RC[-1])),LEFT(RC[-1],FIND(sReturn,RC[-1])-1),"""")"
    Debug.Print 1 & " -- " & sFormula
    MsgBox sFormula
    
    'right (I think) :-)
    sFormula = "=IF(ISNUMBER(FIND(" & Chr(34) & sReturn & Chr(34) & ",RC[-1])),LEFT(RC[-1],FIND(" & Chr(34) & sReturn & Chr(34) & ",RC[-1])-1),"""")"
    Debug.Print 2 & " -- " & sFormula
    MsgBox sFormula
    
    
    'right (I think) :-)
    sFormula = "=IF(ISNUMBER(FIND(""" & sReturn & """,RC[-1])),LEFT(RC[-1],FIND(""" & sReturn & """,RC[-1])-1),"""")"
    Debug.Print 3 & " -- " & sFormula
    MsgBox sFormula
    
End Sub

Debug output:

1 -- =IF(ISNUMBER(FIND(sReturn,RC[-1])),LEFT(RC[-1],FIND(sReturn,RC[-1])-1),"")
2 -- =IF(ISNUMBER(FIND("ABCDEF",RC[-1])),LEFT(RC[-1],FIND("ABCDEF",RC[-1])-1),"")
3 -- =IF(ISNUMBER(FIND("ABCDEF",RC[-1])),LEFT(RC[-1],FIND("ABCDEF",RC[-1])-1),"")