Run-time Error 13 Type Mismatch in Find and Replace Macro

[chergh]

Hi, I am using the following code to find and replace function in a workbook

Sub WeffUpdate()


Dim wb As Workbook
Dim ws As Worksheet
Dim strFind As String
Dim strRepl As String



Set wb = ThisWorkbook

For Each ws In wb.Worksheets

Application.DisplayAlerts = False

strFind = "SUM('https://company.sharepoint.com/sites/Forecast/[Reduction Plan_Aug2020_Baseline Input_V2.xlsx]IBP2 Aug 2020'!$F$68:F$68)+SUM('https://company.sharepoint.com/sites/Forecast/[Reduction Plan_Aug2020_Baseline Input_V2.xlsx]IBP2 Aug 2020'!$F$74:F$74)"
strRepl = "SUM('https://company.sharepoint.com/sites/Forecast/[Reduction Plan_Sep2020_30.09.xlsx]IBP2.v2 Sep20'!$F$56:F$56)+SUM('https://company.sharepoint.com/sites/Forecast/[Reduction Plan_Sep2020_30.09.xlsx]IBP2.v2 Sep20'!$E$61:E$61)"

'Debug.Print strFind
'Debug.Print strRepl
'Debug.Print ws.Name

If CBool(InStr(1, ws.Name, "London")) = True Then


                       ws.Cells.Replace _
                        what:=strFind, _
                        replacement:=strRepl, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        MatchCase:=False, _
                        SearchFormat:=False, _
                        ReplaceFormat:=False, _
                        FormulaVersion:=xlReplaceFormula2

        End If

Next ws

Application.DisplayAlerts = True


End Sub

when the code gets to the cells,replace part of the macro I get the Run-time Error 13 Type Mismatch. Anyone have any suggestions?

 

[VBasic2008]

Why are you toggling DisplayAlerts?
Why are you using CBool? Instr returns either 0 or a number greater than. So if you are trying to restrict the code to worksheets containing "London" in their name the following will do:
If InStr(1, ws.Name, "London") >0 Then, unless you want to ignore case i.e. A=a, in which case you can use the following:
If InStr(1, ws.Name, "London", vbTextCompare) >0 Then

 

[chergh]

Personal preference is the answer to both your questions and neither is the cause of the issue.

 

[davesexcel]

I did not run into the error.
Maybe use xl2bb addin to show the data in the sheet

 

[Trebor76]

Just a guess but make sure both formulas are actually returning something and not an error like #REF? You can check by putting each formula into an unused cell and see what the result is.