Excel VBA : Appending string with a variable

[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

 

[sachinns]

@Fluff
It is showing the text in msgbox. The text in textbox3 is showing in the msgbox.
eg : | "text" |

@Fluff
When my string is just "hello" then it is working fine. please check.

Dim Formula As String
Formula = TextBox3
MsgBox "|" & Formula & "|"
Cells(Lastrow, 3).Formula = "Hello" & Formula

 

[Fluff]

That works fine for me.
Try

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

It's best to avoid using VBA keywords (such as Formula) for the names of variables or procedures, as VBA can sometimes get confused.

 

[sachinns]

@Fluff

No. Still it is not working for me.

 

[sachinns]

@Fluff

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

This part of string is also working fine.

When i add this part of the string "=IFERROR" Then it is not working.

I think VBA is not considering IFERROR as a string.

 

[Fluff]

In that case you have probably got a variable, function, sub or some other procedure called IFERROR.
Check all your code

 

[sachinns]

@Fluff

In this UserForm, I never used IFERROR as variable, function, sub or some other procedure anywhere.

Please help me.

 

[Fluff]

Not just in the userform, anywhere in that workbook.

 

[sachinns]

@Fluff

The workbook is very very big. The file size 30MB + . =IFERROR is there in so many modules and functions.

So is there any way i can add this string without changing the other modules and functions ???

 

[Fluff]

Your code works fine for me, so it is not a problem with that line of code.
If you have used IFERROR as the name of a variable or procedure you will need to change it.

 

[sachinns]

@Fluff

I found the issue. It is not because i have used IFERROR as the name of variable . It is because we cannot write a string to a cell which starts with "=" . Because it will be considered as formula. So we have to use '= .

Thanks a lot for your valuable time.

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