String Functions Not Working?

[CGSB]

I created a function which will go through every cell in column B to replace "bad" strings with "good" strings. Below is what I have come up with.

My problem is that the Replace and InStr functions do not seem to work. My variable strPos is always 0. Replace never replaces the string even when I remove the line "If strPos > 0 Then".

Can anyone help?

Dim strBadString, strGoodString As String
Dim badStringArray() As String
Dim goodStringArray() As String
Dim strURL As String
Dim strPos As Integer

Dim RowCount As Long

strBadString = "//pwgsc-tpsgc.com, .pwgsc-tpsgc., .tpsgc., //w3."
strGoodString = "//

http://www.pwgsc.,

.pwgsc., .pwgsc., //

http://www."

badStringArray = Split(strBadString, ",")
goodStringArray = Split(strGoodString, ",")

With Worksheets(1)
RowCount = .UsedRange.Rows.Count
End With

For i = 1 To RowCount
strURL = Worksheets(1).Cells(i, "B").Value

For x = 0 To UBound(badStringArray)
strPos = InStr(strURL, badStringArray(x))
If strPos > 0 Then
strURL = Replace(strURL, badStringArray(x), goodStringArray(x))
Worksheets(1).Cells(i, "B").Value = strURL
End If
strPos = 0
Next x
Next i

 

[PHV]

what about replacing this:
strBadString = "//pwgsc-tpsgc.com, .pwgsc-tpsgc., .tpsgc., //w3."
strGoodString = "//

http://www.pwgsc.,

.pwgsc., .pwgsc., //

http://www."

with this ?
strBadString = "//pwgsc-tpsgc.com,.pwgsc-tpsgc.,.tpsgc.,//w3."
strGoodString = "//

http://www.pwgsc.,.pwgsc.,.pwgsc.,//www."

another way:
badStringArray = Array("//pwgsc-tpsgc.com", ".pwgsc-tpsgc.", ".tpsgc.", "//w3.")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RonRepp]

Usually a function returns only 1 answer, like this:

Sub WhatIsGoodString()
''Use a sub procedure to extract the answer.
dim s as String
s=GetGoodString
Msgbox(s)

End Sub

Function GetGoodString() as String
Dim strBadString, strGoodString As String
    Dim badStringArray() As String
    Dim goodStringArray() As String
    Dim strURL As String
    Dim strPos As Integer
    
    Dim RowCount As Long
    
    strBadString = "//pwgsc-tpsgc.com, .pwgsc-tpsgc., .tpsgc., //w3."
    strGoodString = "//[URL unfurl="true"]www.pwgsc.,[/URL] .pwgsc., .pwgsc., //[URL unfurl="true"]www."[/URL]

GetGoodString=strGoodstring

End Function

If your procedure truly is a function, it can handle multiple commands, but you are not returning anything.

If you want to find out where the problem is (you thought it was in the Replace function), then use this approach to find out exactly what strGoodString is.


I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[CGSB]

Thanks PVH! I forgot to add the space in the arrays' delimiter. That fixed it.