Replace part of text in a cell case sensitive

user_453116 0
Viewed 147

This seems easy but I couldn't find an answer.

Each solution either replaced part of the cell, or replaced case sensitive but then the replace text had to equal the value in the cell

If InStr(1, CurColumnName, ReplaceFrom, vbTextCompare) <> 0 Then 
'wksC.Range(CurColumnLetter & "1").Value = Replace(CurColumnName, ReplaceFrom, ReplaceTo)
wksC.Range(CurColumnLetter & "1").Replace what:=ReplaceFrom, replacement:=ReplaceTo, MatchCase:=True 
End If

Let's say my CurColumnName is Mp3npi, my ReplaceFrom is npi, my ReplaceTo is NPI.

The code goes into the loop, goes through the range.replace, but the cell isn't replaced, it remains as is.

vba excel
1 Answers

If you check out the official documentation of the Range.Replace method it says:

The settings for LookAt, SearchOrder, MatchCase, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used.

So that means if you don't specify all of them, Excel uses what ever was used before (either by VBA or by the user interface Find/Replace). There is no default value for the parameters you can rely on. Therefore you get random results depending on what your user selected before. So you always have to specify at least those mentioned parameters or your code is not reliable.

wksC.Columns(CurColumnLetter).Replace What:=ReplaceFrom, Replacement:=ReplaceTo, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, MatchByte:=True

This would replace npi into NPI in the entire column CurColumnLetter at once where npi doesn't need to be the entire cell value but a part (xlPart) of it.

Actually the same issue applies to the Find method as well, where parameters need to be specified to make it reliable.

user_32196130
Related