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

Vincent G · Accepted Answer · 2016-08-02 08:46:23Z

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 2, 2016 at 8:46

Vincent G

3,1981 gold badge16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Filipe Pires Over a year ago

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.

Jordan Over a year ago

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

user6432984 Over a year ago

@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

Filipe Pires Over a year ago

Thank you both! It worked as expected, and now I can properly understood the LBound and UBound .

Collectives™ on Stack Overflow

VBA (Microsoft Excel) replace Array with String

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related