Array used for loop

mk000

Board Regular
Joined
Dec 10, 2004
Messages
234
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

martinee

Well-known Member
Joined
Nov 4, 2003
Messages
960
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

Board Regular
Joined
Dec 10, 2004
Messages
234
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

Board Regular
Joined
Dec 10, 2004
Messages
234
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

Well-known Member
Joined
Nov 4, 2003
Messages
960

ADVERTISEMENT

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.
Code:
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

Board Regular
Joined
Dec 10, 2004
Messages
234
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

Well-known Member
Joined
Nov 4, 2003
Messages
960
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):
Code:
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
 

Forum statistics

Threads
1,148,215
Messages
5,745,424
Members
423,951
Latest member
peggrif

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top