VBA(Microsoft Excel)用字符串替换数组

Question

我知道这是完全错误的，但此刻，我真的不知道该怎么做。

在 Microsoft Excel 中，我想用固定字符串“NewValue”替换“OldValues”字符串上的所有值。这怎么可能？

LBound 和 Ubound 用错了吧？

Sub Replace()
Dim sht As Worksheet
Dim OldValues As Variant
Dim NewValue As String
Dim x As Long

OldValue = Array("old1","old2","old3")
NewValue = "allnew!"

  For x = LBound(OldValue) To UBound(NewValue)

      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=OldValue(x), Replacement:=NewValue(x), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht

  Next x

End Sub

Answer 1

您的代码应该进行细微的更改:NewValue不是数组，所以 UBound(NewValue)会给你一个错误。你必须上去UBound(OldValues)而是在您的循环中，并删除 (x)在 NewValue 之后在 Replace .

Sub Replace()
Dim sht As Worksheet
Dim OldValues As Variant
Dim NewValue As String
Dim x As Long

OldValues = Array("old1","old2","old3")
NewValue = "allnew!"

  For x = LBound(OldValues) To UBound(OldValues)

      For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace What:=OldValues(x), Replacement:=NewValue, _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
      Next sht

  Next x

End Sub