How do I declare an ARRAY for filenames?

[jumpingbean]

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

 

[bandit_1981]

you mean something like this

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

 

[jumpingbean]

Perfect, thanks for your help.

 

[Norie]

jumpingbean

Can we see your code?

You might not actually need an array.

 

[jumpingbean]

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]

Try this.

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]

Yeah!!

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

stay brillant!
jumpingbean