如何让VBA的Range.Replace仅执行精确内容替换而非部分匹配？

The core issue here is that your current Replace method uses LookAt:=xlPart, which makes Excel match any substring occurrence of your search value in a cell. That’s why 19.9000 gets altered—it contains 9.9000 as a partial match.

To restrict replacements to only cells with an exact full match of your target value, you just need to switch the LookAt parameter from xlPart to xlWhole. Here’s the corrected code:

Dim find1 As Variant
Dim rplc1 As Variant
'Captures the wrong prices and their replacements and executes the correction on the new file
fnd1 = ThisWorkbook.Worksheets("Sheets").Range("D3")
rplc1 = ThisWorkbook.Worksheets("Sheets").Range("D4")
With WS
 .Range("AT2:DW1000").Replace what:=fnd1, Replacement:=rplc1, _
 LookAt:=xlWhole, ' Updated from xlPart to enforce exact matches
 SearchOrder:=xlByColumns, MatchCase:=False, _
 SearchFormat:=False, ReplaceFormat:=False
End With

Quick Breakdown: #

• xlWhole instructs Excel to only replace cells where the entire content matches your search value (fnd1). So only cells containing exactly 9.9000 will be swapped to 9.900, while cells like 19.9000 remain unchanged.

If you run into edge cases (like hidden trailing spaces in cells breaking the exact match), you could add a quick trim check, but for your stated problem, this change should resolve the accidental replacements immediately.

内容的提问来源于stack exchange，提问作者onit