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