CODEWITHSUNDEEP
vbaexcel
Filipe Pires
Filipe Pires

Reputation: 147

VBA (Microsoft Excel) replace Array with String

I know this is completely wrong, but at the moment, I really don't know how to do it.

In Microsoft Excel, I want to replace all the values on the "OldValues" string, by a fixed string "NewValue". How is that possible?

LBound and Ubound are wrong to be used, right?

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

Upvotes: 1

Views: 1335

Answers (1)

Vincent G
Vincent G

Reputation: 3188

Your code should be working with minor changes: NewValue is not an array, so UBound(NewValue) will get you an error. You have to go up to UBound(OldValues) instead in your loop, and remove the (x) after NewValue in the 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

Upvotes: 4

Related Questions