Hello, my first post so I hope I am doing this correctly
I have the Function below that finds the nth occurrence of a substring in a string and it works great, but I will not take wildcards
And I desperately need to use it with wildcards
Can anyone see how to alter the function or the way I am using it that will permit me to use it using wildcards?
I have tried but have been no luck
Thanks for considering my question
Function
Function ReplaceN(ByVal str1 As Variant, strFind As String, strReplace As String, N As Long, Optional Count As Long) As String
Dim i As Long, j As Long
Dim strM As String
strM = str1
If Count <= 0 Then Count = 1
For i = 1 To N - 1
j = InStr(1, strM, strFind)
strM = Mid(strM, j + Len(strFind), Len(strM))
Next i
If N <= 0 Then
ReplaceN = str1
Else
ReplaceN = Mid(str1, 1, Len(str1) - Len(strM)) & Replace(strM, strFind, strReplace, Start:=1, Count:=Count)
End If
End Function
How I am calling it
Sub ReplaceNthInstance()
Dim ws As Worksheet
Dim outArray As Variant
Dim i As Long, LR As Long
Set ws = ThisWorkbook.Sheets("Z")
With ws.Range("E:E")
With Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
outArray = .value
For i = 1 To .Rows.Count
outArray(i, 1) = ReplaceN(.Cells(i, 1).value, "[*]", vbNullString, 1)
Next i
.value = outArray
End With
End With
End Sub
Bookmarks