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)
Thanks
NimishK
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)
VBA Code:
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