Excel VBA : Help me to join string and variable using VBA

[sachinns]

Hi Friends,

I have a string and a variable called "Formula". I want to append this string with my variable and update this value in the Cells(Lastrow, 3) of my active sheet.

String is :

“=IFERROR(IF(O9=” + Formula + " ;””);”Missing Leader Price”) "

Note : Orange part is my string

I tried to do it but its showing error. Can somebody help me to provide the vba code for this.

Thanks

[jdc2000]

Cells(Lastrow, 3) = "=IFERROR(IF(O9=" & CStr(Formula) & " ;" & Chr$(34) & Chr$(34) & ");" & Chr$(34) & "Missing Leader Price" & Chr$(34) & ") "

Note that this may or may not be a legal formula - I have not tested it.

[westconn1]

I tried to do it but its showing error.

what error?
what is the content of formula variable?

[sachinns]

@jdc2000

Sorry. Error.

[sachinns]

@westconn1

Dim MyFormula As String
MyFormula = TextBox3
Cells(Lastrow, 3).Formula = "(IF(O9=" + MyFormula + " ,"""");""Missing Leader Price"")"
'"=IFERROR"

Hi,
This code is working fine. But when i add the string which i commented in code i.e, =IFERROR , Then my code is not working. When i checked with 1 expert , he told me that you might have used =IFERROR as variable somewhere in the worksheet .
So is there any way to add the string =IFERROR to this code without changing anything in the worksheet.

Please help me on this.

[Siddharth Rout]

What is the value of `TextBox3`?

[sachinns]

@Siddharth Rout

It can be anything what user gives.

[Siddharth Rout]

@Siddharth Rout

It can be anything what user gives.

When you are writing a formula you cannot leave it to the user. There are lot of things that you need to consider. For example

1. Have they entered the correct syntax?
2. Have they entered the correct function name etc etc.

Also if the user or you use ", then you will have to pad it with extra quotes.

So let's assme the user types Sum("A10:A11") in the textbox then the code will be

    Dim MyFormula As String
    
    '~~> Assuming the textbox has Sum("A10:A11")
    MyFormula = Replace(TextBox3.Text, Chr(34), """")

    Cells(Lastrow, 3).Formula = "=IFERROR(O9=" & _
                                MyFormula & _
                                ",""Missing Leader Price"")"

Now try the above code with just 9 in the textbox.

If you are just accepting Numeric Inputs in the Textbox then you may want to limit the entry to the textbox so that the user cannot type anything other than numbers. When Text will be allowed then there is a chance of user making errors. For example what if the user type "Batman!" in the textbox?

and if you want to do a String comparision like "O9=Batman", then you will have to pad the text within quotes which may further complicate things...

[westconn1]

you might have used =IFERROR

very easy to check, just use find

as pointed out by siddharth, if you use iferror remove the IF