VBA (Microsoft Excel) replace Array with String

Question

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

score 4 · Accepted Answer · answered Aug 02 '16 at 08:46

4

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

answered Aug 02 '16 at 08:46

Vincent G

3,153
1
13
30

Nice one. Don't know properly the function LBound & Ubound (as i understood, they exist to create a loop searching on an array?), so I didn't know how to put it working. – Filipe Pires Aug 02 '16 at 08:55
1

When used with an array they refer to the upper and lower index boundaries, so with the array of `Array("old1","old2","old3")` - `LBound` will be 0 and `UBound` will be 2 – Jordan Aug 02 '16 at 08:58
@MiguelRasquinho Alternately you can iterate over an array using a variable of type variant and a For Each Loop. `Dim x as Variant: For Each x in OldValues` – Aug 02 '16 at 09:00
Thank you both! It worked as expected, and now I can properly understood the `LBound` and `UBound` . – Filipe Pires Aug 02 '16 at 14:33

VBA (Microsoft Excel) replace Array with String

1 Answers1

Linked