Replacement Macro for one column impacting other columns

[DJhuffman]

Hey all,

I'm working with a large macro, and part of the macro is designed to filter col M for "O" then look into column E and modify the data entered in there. Basically, the data in Col E contains several various numbers, some of which have a hyphen and a subsequent number (101-2, 102-6). I wanted to use the following macro to remove the hyphen and any numbers after the hyphen (102-6 becomes 102). I thought that the following formula would be effective:

'Sort OP in data file to remove -2 from E

Workbooks("260 " & CM).Activate
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="O"
Columns("E").Replace What:="-?", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.UsedRange.AutoFilter Field:=13

However, when I use it, it seems to be removing data from other columns, such as H, I, & J. Is there something I am doing wrong that is causing the formula to effect other columns?

 

[Fluff]

What if you add this line

Workbooks("260 " & CM).Activate
Range("A1").Find ("X/#@")
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="O"
Columns("E").Replace What:="-?", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.UsedRange.AutoFilter Field:=13

 

[DJhuffman]

What if you add this line

Workbooks("260 " & CM).Activate
Range("A1").Find ("X/#@")
ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="O"
Columns("E").Replace What:="-?", Replacement:="", LookAt:=xlPart, _
   SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.UsedRange.AutoFilter Field:=13

I'm not familiar with that function. What does it do?

 

[Fluff]

Does it work?

 

[DJhuffman]

Does it work?

Forgive the delay in my reply. I actually didn't get a chance to try this out until the monthly adjustments.

To answer your question, it does work. But I'm not sure what it's doing to make things work. If you don't mind, I'm curious what this function is doing to correct the problem.

 

[Fluff]

I suspect that you had done a manual search or replace & changed the "Within" box from Sheet to Workbook & that can cause the problem you were encountering.
Putting

Range("A1").Find ("X/#@")

at the start of the code just resets the "Within" back to Sheet.
I tend to use something like X/#@ as a value that wont be found to be on the safe side.

 

[DJhuffman]

I'm very grateful for your help on this. Thank you for the explanation.

 

[Fluff]

You're welcome & thanks for the feedback.