Find and Replace values not equal to

akjohno

New Member
Joined
Mar 27, 2018
Messages
27
Office Version
365
Platform
Windows
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,327
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

New Member
Joined
Mar 27, 2018
Messages
27
Office Version
365
Platform
Windows
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,327
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,327
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

New Member
Joined
Mar 27, 2018
Messages
27
Office Version
365
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,408
Office Version
365
Platform
Windows
My suggestion

VBA Code:
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,327
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,327
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,267
Messages
5,443,434
Members
405,235
Latest member
1Thess521

This Week's Hot Topics

Top