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 :yes

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