How do I declare an ARRAY for filenames?

jumpingbean

New Member
Joined
Dec 12, 2005
Messages
8
Hi...

I have 10 Excel files that I would like to declare in an array. This array will then be used to run in my VBA code. Currently, the code mention each file repetively. What would be the easiest way to declare the files an array?

For example thess are the files.
state1.xls
state2.xls
state3.xls
state4.xls
state5.xls
.....and so on until state10.xls

Thanks a bunch for your help and continue to enjoy life!
jumpingbean
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

bandit_1981

Board Regular
Joined
Aug 17, 2005
Messages
201
you mean something like this

For x = 0 to 9
arrState(x) = "State" & x+1 & ".XLS"
Next
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
jumpingbean

Can we see your code?

You might not actually need an array.
 

jumpingbean

New Member
Joined
Dec 12, 2005
Messages
8

ADVERTISEMENT

Hi Norie:

This code is intended to VLOOKUP values in Excel file.
Currently, I am referencing each Excel file in the formula.
Isn't there a way I can list all files referenced in my VLOOKUP formula instead of mentioning each one individually.
Suppose I had 50 Excel files to VLOOKUP, replicating each one individually would seem redundant.
Please advise and thanks a bunch.

Sub lookupvalues()

'1) Loop over Cells (x,3)
For x = 2 To 10000

'2) Select active cell
Cells(x, 3).Select
ActiveCell = ""

'3) Check State1.xls
If Cells(x, 3) = "" Then

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-2],[state1.xls]Sheet1!C1,1,FALSE)),"""",VLOOKUP(RC[-2],[state1.xls]Sheet1!C1:C3,3,FALSE))"

End If

'''''''''''

'4) Check State2.xls
If Cells(x, 3) = "" Then

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-2],[state2.xls]Sheet1!C1,1,FALSE)),"""",VLOOKUP(RC[-2],[state2.xls]Sheet1!C1:C3,3,FALSE))"

End If
'''''''''''
'5) Check State3.xls
If Cells(x, 3) = "" Then

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-2],[state3.xls]Sheet1!C1,1,FALSE)),"""",VLOOKUP(RC[-2],[state3.xls]Sheet1!C1:C3,3,FALSE))"

End If
'''''''''''

'6) Check State4.xls
If Cells(x, 3) = "" Then

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP(RC[-2],[state4.xls]Sheet1!C1,1,FALSE)),"""",VLOOKUP(RC[-2],[state4.xls]Sheet1!C1:C3,3,FALSE))"

End If

Next x
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Sub lookupvalues()
Dim strLookup As String

    For x = 2 To 10000
    
        For i = 1 To 4
            If Cells(x, 3) = "" Then
                strLookup = "VLOOKUP(RC[-2],[state" & i & ".xls]Sheet1!C1,1,0)"
                Cells(x, 3).FormulaR1C1 = "=IF(ISNA(" & strLookup & "),""""," & strLookup & ")"
            End If
        Next i
    Next x
End Sub
 

jumpingbean

New Member
Joined
Dec 12, 2005
Messages
8
Yeah!!

Thanks Norie for helping me condensed the code from a millions lines to five lines.

stay brillant!
jumpingbean :p
 

Watch MrExcel Video

Forum statistics

Threads
1,118,018
Messages
5,569,652
Members
412,286
Latest member
kychemist00
Top