How to use Excel VBA replace function with case sensitivity?

Question

This code should replace the words with the case typed by the user, instead it always replaces with the first searc ignoring the case.

Any ideas on how to fix it?

Sub chaser()

Range(Range("B15"), Range("B15").End(xlDown)).Replace what:="Part of chair", replacement:="Chair part"
Range(Range("B15"), Range("B15").End(xlDown)).Replace what:="part of chair", replacement:="chair part"

End Sub

Result:

Chair part
Chair part

should be:

Chair part
chair part

Any ideas?

Answer 1

You need to use the MatchCase criteria; MatchCase:=True.

Just add it to the end of your existing statements:

Sub chaser()

Range(Range("B15"), Range("B15").End(xlDown)).Replace what:="Part of chair", replacement:="Chair part", MatchCase:=True
Range(Range("B15"), Range("B15").End(xlDown)).Replace what:="part of chair", replacement:="chair part", MatchCase:=True

End Sub

See HERE for more information on Range.Replace.