ExcelBanter - VBA excel using arrays and loops

Sub array_test()

Dim sub_replace(1 To 7) As String
Dim i As Integer

sub_replace(1) = "COM"
sub_replace(2) = "LS"
sub_replace(3) = "DN"
sub_replace(4) = "ARTS"
sub_replace(5) = "PS"
sub_replace(6) = "IS"
sub_replace(7) = "AL"

For i = 1 To 7
Selection.Replace What:=(sub_replace(i)), Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i

msgbox "Finished"

End Sub

But I find something like this a little easier to modify (or reuse when I want a
similar routine):

Option Explicit
Sub array_test2()

Dim sub_replace As Variant
Dim i As Long

sub_replace = Array("COM", "LS", "DN", "ARTS", "PS", "IS", "AL")

For i = LBound(sub_replace) To UBound(sub_replace)
Selection.Replace What:=(sub_replace(i)), Replacement:="", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i

MsgBox "Finished"

End Sub

wrote:

I would appreciate some help getting this array of strings and replace
function working. All I need it to do is loop through the array and
replace COM, LS, ARTS in the worksheet.

I know my problem lies with how to feed the sub_replace variable into
the function but I cannot figure out how to do it, any help would be
greatly appreciated.

Sub array_test()

Dim sub_replace(1 To 7) As String
Dim i As Integer

sub_replace(1) = "COM"
sub_replace(2) = "LS"
sub_replace(3) = "DN"
sub_replace(4) = "ARTS"
sub_replace(5) = "PS"
sub_replace(6) = "IS"
sub_replace(7) = "AL"

For i = 1 To 7
If i < 8 Then
Selection.Replace What:=(sub_replace(i)), Replacement:="",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
i = i + 1
Else
MsgBox "finished cleanup"
End If
Next i

End Sub

--

Dave Peterson