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]

Let's confirm that the data that is in there is really what it appears to be (sometimes data that comes from external sources has special characters).

So, if we have a value like "12345-ABC_6789", we want to verify the values found in the 6th place ("-") and the 10th place ("_").
We can do that by entering these formulas into any blank cells in your workbook and see what they return:

=CODE(MID(W2,6,1))

and

=CODE(MID(W2,10,1))

(note, change the 6 and 10, if necessary to match the stop where the "-" and "_" are found in your example in W2).
(And be sure to run this on some REAL data you have).

What numbers do those two formulas return?

 

[reberryjr]

Let's confirm that the data that is in there is really what it appears to be (sometimes data that comes from external sources has special characters).

So, if we have a value like "12345-ABC_6789", we want to verify the values found in the 6th place ("-") and the 10th place ("_").
We can do that by entering these formulas into any blank cells in your workbook and see what they return:

=CODE(MID(W2,6,1))

and

=CODE(MID(W2,10,1))

(note, change the 6 and 10, if necessary to match the stop where the "-" and "_" are found in your example in W2).
(And be sure to run this on some REAL data you have).

What numbers do those two formulas return?

Couple of things....1 - I wasn't aware of this formula, and it's pretty handy. 2 - The values returned are 95 and 45 respectively, so the characters I'm trying to replace are what I coded for.

 

[Joe4]

Couple of things....1 - I wasn't aware of this formula, and it's pretty handy. 2 - The values returned are 95 and 45 respectively, so the characters I'm trying to replace are what I coded for.

Hmmm.... OK, those values are indeed correct.

Try placing this MsgBox in your code above the replace lines, and let me know what it returns:

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

Does it look like it is returning the correct last row of data on that sheet?

 

[reberryjr]

Hmmm.... OK, those values are indeed correct.

Try placing this MsgBox in your code above the replace lines, and let me know what it returns:

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

Does it look like it is returning the correct last row of data on that sheet?

It is returning the correct last row.

 

[Joe4]

I tested out your code, and it appears to be doing what it is supposed to, but I think you have an error in your logic.

It makes the change in your source file only (the "RetResults" file), but then you close that workbook WITHOUT saving the changes, i.e.

s.Close SaveChanges:=False

I am not sure if your goal is to make the changes in the source file and just save them there, or if you are wanting to copy these values over to the file where this macro resides, but it is doing neither of these things. It is making the changes, and then closing the file without saving it, so all those changes are lost!

 

[reberryjr]

I tested out your code, and it appears to be doing what it is supposed to, but I think you have an error in your logic.

It makes the change in your source file only (the "RetResults" file), but then you close that workbook WITHOUT saving the changes, i.e.

s.Close SaveChanges:=False

I am not sure if your goal is to make the changes in the source file and just save them there, or if you are wanting to copy these values over to the file where this macro resides, but it is doing neither of these things. It is making the changes, and then closing the file without saving it, so all those changes are lost!

Right. I change the source file, then later in the code the contents of column W get copied to my destination file. The source file closes after the changes have been made and copied over. I didn't include the copy snippet because it's not failing there.

 

[Joe4]

Have you tried stepping through your code line-by-line, using the F8 key while watching what is happening to your file?
I did, and was able to see the replacements work properly, as well as the Text to Columns. So I do not see any logic problems with that part of the code, so it may be a data issue.

Since I do not have access to your full code or the real file you are trying to run this against, I cannot do this for you.
You will need to do it yourself. Many times when you do this, the problem becomes evident (when you can watch and see exactly what is happening).

 

[reberryjr]

Have you tried stepping through your code line-by-line, using the F8 key while watching what is happening to your file?
I did, and was able to see the replacements work properly, as well as the Text to Columns. So I do not see any logic problems with that part of the code, so it may be a data issue.

Since I do not have access to your full code or the real file you are trying to run this against, I cannot do this for you.
You will need to do it yourself. Many times when you do this, the problem becomes evident (when you can watch and see exactly what is happening).

That was one of the things I tried prior to posting. Nothing happens to the file. It doesn't replace the characters, so the text to columns fails. Even when I try to replace the characters manually with CTRL + H, they're not found.

 

[Akuini]

Even when I try to replace the characters manually with CTRL + H, they're not found.

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