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

chergh

New Member
Joined
Nov 10, 2008
Messages
37
Office Version
  1. 365
Platform
  1. Windows
Hi, I am using the following code to find and replace function in a workbook

VBA Code:
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?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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
 
Last edited:
Upvote 0
Personal preference is the answer to both your questions and neither is the cause of the issue.
 
Upvote 0
I did not run into the error.
Maybe use xl2bb addin to show the data in the sheet

1601697823942.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top