Styjun

I'm trying to use VBA to concatenate a string inside a formula. If I only use the code below i'm not getting any errors but when i add the IFERROR together with the code I get a runtime error.

Is there any way to work around it?

text1 = "='C:UsersJOHLA\DesktopYield arkNyt-yield-ark[Yield-Uge-"
text2 = ".xlsm]Scrap'!H7"

The code including string with IFERROR that gives runtime error is given below.

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim i As Integer
Dim preRange As Range
Dim path, filename1 As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set ws = ActiveWorkbook.Sheets("Sheet1")
Set preRange = ws.Range("E9:I17")
i = ws.Range("C1").Value
text1 = "=IFERROR('C:UsersJOHLADesktopYield arkNyt-yield-ark[Yield-Uge-"
text2 = ".xlsm]Scrap'!H7;0)"

With ws
 For i = .Range("C1").Value To .Range("C1").Value + 4
 debug.print text1 & i & text2
 preRange = text1 & i & text2
 Set preRange = preRange.Offset(0, 5)
 Next i
End With

End Sub

Judging by your use of semicolon in your formula, it would suggest that you're using local settings which are not compatible with VBA.Formula

in this case, you either need to change the formula to use a comma or set the formula using FormulaLocal:

preRange.FormulaLocal = Replace(text1 & i & text2, "'", Chr(34))

As you can see, I've also added a Replace that changes ' into " - as I think you need this also.

Lastly, don't forget to enable ScreenUpdating and DisplayAlerts at the end of your routine.

Any time you use

Application.ScreenUpdating = False 
Application.DisplayAlerts = False

you need to make sure that you are using error handling to manage if your code breaks.

On Error GoTo ExitErr

 Application.ScreenUpdating = False 
 Application.DisplayAlerts = False
 <your code here>

ExitErr:
 Application.ScreenUpdating = True
 Application.DisplayAlerts = True

This makes sure that if your code breaks (in your case the most obvious being someone renaming "Sheet1"), Excel isn't left with ScreenUpdating and DisplayAlerts left turned off. I can't tell you how many times I've had to fix other people's code because they turned off these functions and then couldn't figure out why Excel was acting up.