VBA - Use variable from InputBox in formula?

[Bergans]

Hi!
I've a macro where I need to search for a string, given by InputBox and split the result in to two columns.
How may I use the string from the InputBox, in the forumula?

If I use the following, the code will not search for anything. If I replace the sReturn-variable in the forumla with a fixed string (eq. ""apple"") it will act as intended

sReturn = InputBox("What to search for?")


'Insert two new columns with format "General" to the right of the selected cell/column
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Select
ActiveCell.Offset(0, 0).FormulaR1C1 = "Part 1"
ActiveCell.EntireColumn.Offset(0, 1).NumberFormat = "General"
'
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Select
ActiveCell.Offset(0, 0).FormulaR1C1 = "Part 2"
ActiveCell.EntireColumn.Offset(0, 1).NumberFormat = "General"
'


Dim i, LastRow
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  For i = 1 To LastRow - 1
  ActiveCell(i + 1, 0).FormulaR1C1 = _
        "=IF(ISNUMBER(FIND(sReturn,RC[-1])),LEFT(RC[-1],FIND(sReturn,RC[-1])-1),"""")"
  ActiveCell(i + 1, 1).FormulaR1C1 = _
        "=IF(ISNUMBER(FIND(sReturn,RC[-2])),SUBSTITUTE(RC[-2],RC[-1]&sReturn,""""),"""")"

[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),"")

[Bergans]

...
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

Thanks Paul!
Sadly the resulting MsgBox'es are blank. When I run the code in debug I get the String I put in, when holding the mouse both over the "Chr(34)" and the "sReturn".
Here is the complete code I'm using, after your input:

Sub SearchAndSplitt()'
' Search for given String and print tekst before and after in two new columns
'
ActiveWorkbook.ActiveSheet.Select    'Executes in the open workbookvba and "insert a new column"
ActiveWindow.DisplayFormulas = False ' Show calculated value of formulas
'
Dim sReturn As String, sFormula As String
'Dim i As Long
sReturn = InputBox("What to search for?")


'Insert two new columns with format "General" to the right of the selected cell/column
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Select
ActiveCell.Offset(0, 0).FormulaR1C1 = "Part 1"
ActiveCell.EntireColumn.Offset(0, 1).NumberFormat = "General"
'
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Select
ActiveCell.Offset(0, 0).FormulaR1C1 = "Part 2"
ActiveCell.EntireColumn.Offset(0, 1).NumberFormat = "General"
'


Dim i As Long, LastRow
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  For i = 1 To LastRow - 1
  ActiveCell(i + 1, 0).FormulaR1C1 = _
        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 1 & " -- " & sFormula
        MsgBox sFormula


  ActiveCell(i + 1, 1).FormulaR1C1 = _
        sFormula = "=IF(ISNUMBER(FIND(""" & sReturn & """,RC[-1])),LEFT(RC[-1],FIND(""" & sReturn & """,RC[-1])-1),"""")"
        Debug.Print 2 & " -- " & sFormula
        MsgBox sFormula
  Next i


'Selects the two new columns, copy the results and paste only the values
  Selection.Offset(0, -1).Resize(Selection.Rows.Count + 0, _
  Selection.Columns.Count + 1).Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues
  
'Cleanup
  Range("A1").Select
  Application.CutCopyMode = False
End Sub

[Paul_Hossler]

Try it this way

    sFormula = "=IF(ISNUMBER(FIND(" & Chr(34) & sReturn & Chr(34) & ",RC[-1])),LEFT(RC[-1],FIND(" & Chr(34) & sReturn & Chr(34) & ",RC[-1])-1),"""")"


    ActiveCell(i + 1, 0).FormulaR1C1 = sFormula

[Bergans]

Fantastic Paul!
This did the trick

Here is the full working code, if anyone need it:

Sub SearchAndSplitt()'
' Search for given String and print tekst before and after in two new columns
'
ActiveWorkbook.ActiveSheet.Select    'Executes in the open workbookvba and "insert a new column"
ActiveWindow.DisplayFormulas = False ' Show calculated value of formulas
'
Dim sReturn As String, sFormula As String
'Dim i As Long
sReturn = InputBox("What to search for?")


'Insert two new columns with format "General" to the right of the selected cell/column
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Select
ActiveCell.Offset(0, 0).FormulaR1C1 = "Part 1"
ActiveCell.EntireColumn.Offset(0, 1).NumberFormat = "General"
'
ActiveCell.EntireColumn.Offset(0, 1).Insert
ActiveCell.EntireColumn.Offset(0, 1).Select
ActiveCell.Offset(0, 0).FormulaR1C1 = "Part 2"
ActiveCell.EntireColumn.Offset(0, 1).NumberFormat = "General"
'


Dim i As Long, LastRow
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  For i = 1 To LastRow - 1


sFormula = "=IF(ISNUMBER(FIND(" & Chr(34) & sReturn & Chr(34) & ",RC[-1])),LEFT(RC[-1],FIND(" & Chr(34) & sReturn & Chr(34) & ",RC[-1])-1),"""")"
ActiveCell(i + 1, 0).FormulaR1C1 = sFormula
'Debug.Print 1 & " -- " & sFormula
'MsgBox sFormula


sFormula = "=IF(ISNUMBER(FIND(" & Chr(34) & sReturn & Chr(34) & ",RC[-2])),SUBSTITUTE(RC[-2],RC[-1]&" & Chr(34) & sReturn & Chr(34) & ",""""),"""")"
ActiveCell(i + 1, 1).FormulaR1C1 = sFormula
'Debug.Print 2 & " -- " & sFormula
'MsgBox sFormula
Next i


'Selects the two new columns, copy the results and paste only the values
  Selection.Offset(0, -1).Resize(Selection.Rows.Count + 0, _
  Selection.Columns.Count + 1).Select
  Selection.Copy
  Selection.PasteSpecial Paste:=xlPasteValues
  
'Cleanup
  Range("A1").Select
  Application.CutCopyMode = False
End Sub