Setting up an Array on a Macro

[squeakums]

This is my problem. I need to setup an array to look in 408 or so names and I don't want to have to write ("billy", "beth", ... ) etc - I just want it to scan array A3:A408 instead and unsure how to write the macro to do this. Any help would be appreciated. This is what I have so far:

Sub Echo_Monthly_CSR_June()
'
' Macro8 Macro
' Macro recorded 6/21/2008 by Kelly Simcik
'

'
Windows("Monthly Macro Insert.xls").Activate
Sheets("CSR Data").Select
Dim MyArray As Variant, x As Long, c As Range

'Assuming your Source, book = Monthly Macro Insert.xls and Sheet = Copy & Paste Echo
'Also assuming your Destination, book = Monthly Macro Insert.xls and sheets = CSR Data

MyArray = Array("A3:A408").Select

For x = 0 To 405
With Workbooks("Monthly Macro Insert.xls").Sheets("Copy & Paste Echo")
Set c = .Cells.Find(What:=MyArray(x), After:=.Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If Not c Is Nothing Then
c.Offset(0, 2).Copy
Workbooks("Monthly Macro Insert.xls").Sheets("CSR Data").Range("B" & x + 3).PasteSpecial xlPasteValues
If Not c Is Nothing Then
c.Offset(0, 5).Copy
Workbooks("Monthly Macro Insert.xls").Sheets("CSR Data").Range("C" & x + 3).PasteSpecial xlPasteValues
End If
End If
End With
Next x
Range("A1").Select
Windows("Weekly Macro Insert.xls").Activate
Sheets("Supervisor Echo Results").Select
Range("B2:V28").Select
Selection.Replace What:="", Replacement:="N/A", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select
Windows("Weekly Macro Insert.xls").Activate
Sheets("Macro").Select
Range("A1").Select

End Sub

 

[mikerickson]

There is a mismatch between the index of MyArray and the row number it came from. This is one fix, but Jindon's suggestion that you use a For Each loop is the way I would go.

For X = 1 to 366