VBA Find & Replace Not Finding

[reberryjr]

I have a source file that contains some data in column W. This data is formatted as General and structured like 12345-ABC_6789. Each of the 3 segments is dynamic in length and content. I'm trying to replace the - and _ with a comma, so that I can do text to columns, but Excel is saying it can't find a - or _; even if I try it manually. They are clearly present and the sheet is not protected, so I'm at a loss.

Sub ImportRetData()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim m As Workbook, s As Workbook
Dim mD As Worksheet, sD As Worksheet
Dim fP As String, fN As String, fE As String
Dim MaxDt As Date
Dim mDLR As Long, mNLR As Long, sDLR As Long

Set m = ThisWorkbook
Set mD = m.Sheets("New Data")

mDLR = mD.Range("A" & Rows.Count).End(xlUp).Row

fP = "C:\Users\Import Files\"
fN = "RetResults"
fN = Dir(fP & fN & "*.xlsx")

Set s = Workbooks.Open(fP & fN)
Set sD = s.Sheets("Data")

sDLR = sD.Range("A" & Rows.Count).End(xlUp).Row

sD.Activate

'Removes filters from the working data if any exist.
If sD.AutoFilterMode Then sD.AutoFilterMode = False

'Unhides any columns and rows that may be hidden on the working data.
With sD.UsedRange
    .Columns.EntireColumn.Hidden = False
    .Rows.EntireRow.Hidden = False
End With

sD.Range("W2:W" & sDLR).Replace What:="_", Replacement:=",", MatchCase:=False
sD.Range("W2:W" & sDLR).Replace What:="-", Replacement:=",", MatchCase:=False

With sD.Range("W2:W" & sDLR)
    .TextToColumns Destination:=sD.Range("W2"), DataType:=xlDelimited, Comma:=True, FieldInfo:=Array(Array(1, 9), Array(2, 2), Array(3, 9)), TrailingMinusNumbers:=True
End With

s.Close SaveChanges:=False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

 

[Joe4]

Did you uncheck "Match entire cell content" in the Replace dialog box?

Good point. You probably want to explicitly tell it what to do in your code, so it doesn't just inherit whatever current settings you have, i.e.

sD.Range("W2:W" & sDLR).Replace What:="_", Replacement:=",", LookAt:=xlPart, MatchCase:=False
sD.Range("W2:W" & sDLR).Replace What:="-", Replacement:=",", LookAt:=xlPart, MatchCase:=False

 

[reberryjr]

Did you uncheck "Match entire cell content" in the Replace dialog box?

UGH! I never checked it (at least not intentionally). I added the LookAt in this snippet, and it's working now.

sD.Range("W2:W" & sDLR).Replace What:="_", Replacement:=",", MatchCase:=False, LookAt:=xlPart
sD.Range("W2:W" & sDLR).Replace What:="-", Replacement:=",", MatchCase:=False, LookAt:=xlPart

 

[Joe4]

Excellent!
Glad to hear it.

 

[Akuini]

UGH! I never checked it (at least not intentionally). I added the LookAt in this snippet, and it's working now.

You should mark post #11 as the solution because @Joe4 provided the solution in that post.

 

[reberryjr]

You should mark post #11 as the solution because @Joe4 provided the solution in that post.

I'm showing it is checked as the solution.

 

[Joe4]

I'm showing it is checked as the solution.

Yes, I just switched it a little while ago.

 

[reberryjr]

Yes, I just switched it a little while ago.

Gotcha...and thank you!

 

[Akuini]

I want to add something:
When using the Find or Replace method, it's a good practice to set all relevant parameters as needed, otherwise it uses the last/existing settings. Please refer to this discussion for a better understanding:

Trouble with FIND

Hi All, I'm having trouble with using FIND and I've finally given up. I just know this will be easy for most everyone else but not me. The code Sub Find_Cell_In_Menu() Dim s As String Dim rng As Range Worksheets("Menu").Activate s = Range("M3") ' Example of M3 value =...

www.mrexcel.com

 

[reberryjr]

I want to add something:
When using the Find or Replace method, it's a good practice to set all relevant parameters as needed, otherwise it uses the last/existing settings. Please refer to this discussion for a better understanding:

Trouble with FIND

Hi All, I'm having trouble with using FIND and I've finally given up. I just know this will be easy for most everyone else but not me. The code Sub Find_Cell_In_Menu() Dim s As String Dim rng As Range Worksheets("Menu").Activate s = Range("M3") ' Example of M3 value =...

www.mrexcel.com

So, kind of like setting the last row. Even if you add records, the last row doesn't change unless you reset it. Correct?

 

[Joe4]

So, kind of like setting the last row. Even if you add records, the last row doesn't change unless you reset it. Correct?

No, not quite the same thing.

One is talking about processes (like "Find and Replace") filling in/inheriting default settings for settings you do not declare explicitly in the VBA code.

The other is talking about the values of variables you create. Excel doesn't correlate your variables with the "last row". You can name it anything you want (i.e. "x" or whatever).
You are calculating and setting some variable at some point in time. The value of variable is a value, not a "dynamic method", and doesn't change as data changes, unless you re-run it.