Find and Replace values not equal to

[akjohno]

Hi all, I have some code to change values of say column D in a worksheet. It looks at the values (numeric) and then if it finds say a 400 it replaces that text to "%po", and then if it finds 700 it replaces that with "". I do this using the Selection.Replace so it does it extremely quickly. I now want to be able to replace any other value in that column to the text "%sp" but i want it to do it as fast as possible. is there a way to code the Selection.Replace to do a search for does not equal? I can get the code to work by use Select Case or counter type methods, but as the sheet can have hundreds of thousands of rows I don't want the users to have the annoying flickering if it examining each row by itself and looping through if possible. Part of my code below for reference.

Columns("D:D").Select 'Select the column we want to search for feature codes
Selection.Replace What:="700", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="400", Replacement:="%po", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Regards,
Andrew

 

[Fazza]

I don't understand what is wanted. Is it,

Selection.Replace What:="does not equal", Replacement:="whatever", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

For optimal speed, consider alternative approaches. Probably UPDATE query is good - should be able to do all changes in single query - though I expect an array will be fastest. Load worksheet data to array, make changes, paste results back to worksheet.

 

[akjohno]

So in the same column i have numbers 11, 112, 662, 403 etc etc. All of these numbers in that column need to be replaced by "%sp" or do I need to run a Selection.Replace for each individual possible alternative (i.e. one for 11, one for 112, one for 662 etc). I will research the update query, I am only a very part time coder so will check it out on the net. Thanks.

 

[akjohno]

 

[Fazza]

There are many ways to do these sort of things.
Using Selection at all is likely not the fastest way. However if you are only using it once, it might not matter.
Selection.Replace for each option will obviously work though will not be the only way. If it is good enough for your needs, then it might be OK.
Same comments for the select case, if it works for you that might be good enough.
To work out a better solution will require more knowledge of the task. If you want more help on this best to post more explanation.

 

[Fazza]

Thanks for posting the data. Some thoughts. If it can be sorted on column D it might be faster - especially if there is a huge amount of data. If all numbers (not some specific ones, but all at once) are to be changed range.specialcells might be good. Best approach will depend on specifics

 

[akjohno]

I don't understand what is wanted. Is it,

Selection.Replace What:="does not equal", Replacement:="whatever", LookAt:=xlWhole, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

For optimal speed, consider alternative approaches. Probably UPDATE query is good - should be able to do all changes in single query - though I expect an array will be fastest. Load worksheet data to array, make changes, paste results back to worksheet.

 

[Peter_SSs]

My suggestion

Sub Do_Replacements()
  Application.ScreenUpdating = False
  With Range("D1", Range("D" & Rows.Count).End(xlUp))
    .Replace What:="700", Replacement:="", LookAt:=xlWhole
    .Replace What:="400", Replacement:="#N/A", LookAt:=xlWhole
    On Error Resume Next
      .SpecialCells(2, 1).Value = "%sp"
      .SpecialCells(2, 16).Value = "%po"
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[Fazza]

Apologies, Andrew. Though you were clear, I didn't read carefully that it was all other values after 700 & 400 that were to change. I wrongly thought it was some particular list of numbers.

 

[Fazza]

hi, Andrew

Peter's code likely does what you need. Just be careful if your Excel is before 2010 version as I understand there is an 8,192 different ranges limit on the special cells result & with the large dataset you may have a problem - which can be overcome by sorting column D before the other steps. And this sorting almost certainly give you a good speed gain too (in any Excel version).

regards