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

chergh

New Member
Joined
Nov 10, 2008
Messages
36
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?
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
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:

chergh

New Member
Joined
Nov 10, 2008
Messages
36
Office Version
  1. 365
Platform
  1. Windows
Personal preference is the answer to both your questions and neither is the cause of the issue.
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
I did not run into the error.
Maybe use xl2bb addin to show the data in the sheet

1601697823942.png
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,735
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,064
Messages
5,628,391
Members
416,315
Latest member
certainlyfrustrated

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
Top