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]

Try

    str = cellRng.Formula

 

[NimishK]

Try

    str = cellRng.Formula

Sorry Fluff

the error repeats after implementing your suggestion

NimishK

 

[Fluff]

With the formula you provided it works for me, although the code should be

WhatPathString = Mid(str, openPos + 1, closePos - openPos)

Otherwise it will return "C:\Excel\Data\[1234.xlsm]S"

What are the values of openPos and closePos when you get the error?

 

[Fluff]

Another thing
What is the code meant to do?

 

[NimishK]

Another thing
What is the code meant to do?

The code was to find Reference link and replace the path with "" empty string so that sheet name displays after " ' ".
was this the correct method to do what i want ?

 

[Fluff]

Can you answer this as well?

What are the values of openPos and closePos when you get the error?

 

[NimishK]

OpenPos = 9 and closePos =34

 

[Fluff]

In that case I do not understand why you would get this error "Invalid procedure call or argument" on this line

WhatPathstring = Mid(str, openPos + 1, closePos - openPos + 1)

Check that you don't have any procedures or variables called Mid

 

[NimishK]

In that case I do not understand why you would get this error "Invalid procedure call or argument" on this line

WhatPathstring = Mid(str, openPos + 1, closePos - openPos + 1)

Check that you don't have any procedures or variables called Mid

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

One thing observed was when
FirstStrSrch = "'" written as FirstStrSrch = "''" ie with two Rem. Marks / Two single inverted comma. Error disappeared
i dont understand why error disappeared by incorporating two single inverted commas
Is it anything to do with text which begins in a cell to start from Extreme left is by ' Single inverted comma