Array used for loop

[mk000]

Can someone help me set this up???

I want to Dim an array, 1, 2, 3, 4, 5 ... for our example here.

And I want the macro to loop thro using 1, 2, 3, 4, and 5 as items in the loop each time.

for the example, create the array that loops.

The first iteration will be...

'Open file
Application.Workbooks.Open "C:\1.xls"
Windows("1.xls").Activate


'Then search on the open book for the file name (1 on this iteration)
Cells.Find(What:="1", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select


'Now loop, 2, 3, 4, and 5


That's it. I'm just horrible with variables, but I'm externally grateful to the knowledgable gent who solves this!
Thanks!
MK

 

[martinee]

Perhaps there is a better way to do this. Are all the files you are looping through in the same directory, if not, where are they?

 

[mk000]

No, you're right, same directory. And Id prefer it to know the directory no matter when the entire directory moves. But the file names must be in the array, so inside the loop, the file names are used for a variable.

 

[mk000]

maybe that came out wrong, if the directory moves, I want the macro to not care. I want it to use the active directory for the 1, 2, 3... files.

 

[martinee]

I'm assuming your files are not names as simple as 1,2,3...etc....but check this code out. See comments in code if you don't understand anything. HTH.

Sub LoopThruADirectory()

Dim MyPath As String, f

'Define a static directory
MyPath = "C:\Documents And Settings\JoeUser\Desktop\"

'turn stuff off for efficiency
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With

'***********************Loop Through Workbooks in Directory********************
f = Dir(MyPath & "*.xls")

Do While Len(f) > 0
    
    Workbooks.Open (MyPath & f)
    'If there are other workbooks in this directory, you can use some sort of
    'IF statement to ensure you only modifye etc... the workbooks you want to
    'like:
    'If workbooks(f).name <> "workbook1.xls" OR workbooks(f).name <> _
    '                          "workbooks2.xls" Then etc....
    
    
    'define the variable 'wkbkName' as equal to that of the workbook
    'you just opened
    wkbkName = ActiveWorkbook.Name
    
    'look to the next file
    f = Dir()
Loop

'turn stuff back on
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .DisplayAlerts = True
End With

End Sub

 

[mk000]

No, they are not really 1, 2, 3...Instead of the numbers, the array will be HELLO, GOODBYE, FAIRWELL...

But i need the macro to use an array. And it must do the following...

First iteration will be...

'Open file
Application.Workbooks.Open "C:\HELLO.xls" '<- if you can make this open HELLO.xls from the active directory, BONUS POINTS!!!!!!!!!
Windows("HELLO.xls").Activate

'Then search on the open book for the file name (HELLO on this iteration)
Cells.Find(What:="HELLO", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Select

'Now loop for GOODBYE and FAIRWELL

 

[martinee]

I still see no need for an array, but perhaps you are hiding things from me. In the previous way I posted, the string wkbkName becomes a searchable string that you can use later in your code. However, if you are stuck on an array, try this: (Note: This also references the current directory):

Sub loopfiles()
Dim mypath As String
Dim myfile(2) As String

mypath = CurDir & "\"

myfile(0) = "Hello"
myfile(1) = "Goodbye"
myfile(2) = "Farewell"

For i = 0 To 2 Step 1

MsgBox mypath & myfile(i) & ".xls"

Next i


End Sub

HTH