我想更改地址栏中的"路"一词，但它更改了所有单词，包括"百老汇"一词

Question

我一直在考虑使用len，但我不知道怎么做。如果还有更好的方法可以做到这一点，你能展示并解释一下吗？谢谢！：(

Sub Invalid()
Dim e
For Each e In Array("road", "street")
Select Case e
Case "road"
Range("A1").EntireColumn.Replace e, "rd"

End Select
Next e
End Sub

编辑：

仍然无法理解，但如果它只是在活动细胞中，并且有这个概念呢？

Dim Original As String
Dim Corrected As String

Original = ActiveCell.Value

Corrected = Replace(Original, "ROAD", "RD")


ActiveCell.Value = Corrected

End Sub

Answer 1

使用此处概述的基本方法：仅搜索和替换单词

Sub ReplaceOnlyWords()
Dim ws As Worksheet, c As Range
Dim arrFind, arrReplaceWith, e, v

arrFind = Array("road", "street")   'look for these words
arrReplaceWith = Array("rd", "st")  'replace with these words

Set ws = ActiveSheet
For Each c In Application.Intersect(ws.UsedRange, ws.Columns(1)).Cells
v = c.Value
If Len(v) > 0 Then 'anything to work with?
c.Value = ReplaceWords(v, arrFind, arrReplaceWith)
End If
Next c
End Sub
'In strSource, replace all words in FindThis with same-position word in ReplaceWith
Public Function ReplaceWords(strSource, FindThis, ReplaceWith) As String
Dim re As Object, s, i As Long
'check we got arrays and make so if not...
'assuming both arrays if provided are the same length
If Not IsArray(FindThis) Then FindThis = Array(FindThis)
If Not IsArray(ReplaceWith) Then ReplaceWith = Array(ReplaceWith)
s = strSource
With CreateObject("VBScript.RegExp")
.Global = True
.IgnoreCase = True ' <-- case insensitve
For i = LBound(FindThis) To UBound(FindThis)
.Pattern = "b" & FindThis(i) & "b"
s = .Replace(s, ReplaceWith(i))
Next i
End With
ReplaceWords = s
End Function