VBA excel using arrays and loops

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

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


[email protected] 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