Error - Invalid procedure call or argument

[NimishK]

Hello
Can anyone help to correct the Error Invalid procedure call or arguement
I get Error at following line in code WhatPathstring = Mid(str, openPos + 1, closePos - openPos + 1)

in worksheet following is the value in range A1 and uptil the last Row in Col A
Range(A1).value
=SUMIFS('C:\Excel\Data\[1234.xlsm]Sheet3'!AB:AB,'C:\Excel\Data\[1234.xlsm]Sheet3'!A:A,'C:\Excel\Data\[1234.xlsm]SheetReport'!A:A,'C:\Excel\Data\[1234.xlsm]Sheet3'!K:K,'C:\Excel\Data\[1234.xlsm]SheetReport'!$DF$1)+SUMIFS('C:\Excel\Data\[1234.xlsm]Sheet3'!Y:Y,'C:\Excel\Data\[1234.xlsm]Sheet3'!A:A,'C:\Excel\Data\[1234.xlsm]SheetReport'!A:A,'C:\Excel\Data\[1234.xlsm]Sheet3'!K:K,'C:\Excel\Data\[1234.xlsm]SheetReport'!$DF$1)

Public Sub testFindReplace()

Dim lstRow As Long, WhatPathString As String, replacementString As String
Dim Wks As Worksheet
Set Wks = Worksheets("Sheet1")
Wks.Activate
Dim str As String
Dim openPos As Integer
Dim closePos As Integer

Dim cellRng As Range

Dim FirstStrSrch As String
Dim SecondStrSrch As String

FirstStrSrch = "'"
SecondStrSrch = "]"
replacementString = "'"
lstRow = Wks.Cells(Rows.Count, 1).End(xlUp).Row

Set mainRng = Wks.Range("A1:A" & lstRow)

For Each cellRng In mainRng
    str = cellRng.Value
       openPos = InStr(str, FirstStrSrch)
       closePos = InStr(str, SecondStrSrch)
       WhatPathstring = Mid(str, openPos + 1, closePos - openPos + 1)
       str = Replace(cellRng, WhatPathstring, replacementString)
       MsgBox str
Next

End Sub

Thanks
NimishK

 

[Fluff]

The coding is just in front of you. And there are no procedures/functions or varaibles called MID

That doesn't mean you don't have other procedures or variables called MID & as I cannot see what other code you have I suggested you check.

I cannot explain why the error disappeared by using two apostrophes, especially as what you said here

OpenPos = 9 and closePos =34

Shows quite clearly that the code is finding both strings & returning the correct numbers.