Using VLookup with 2 Workbooks

[Dave]

Hi,

I am stumped and hoping someone can assist me.

I am writing a macro that will format workbook1 and pull who is assigned to
a task from the previous days report(workbook2). I am getting an error on the
table array and can not figure it out.

I am using excel 2007 on windows XP pro OS. I will post the code below. Keep
in mind that the macro is not complete as i am only at the VLookup section.

Sub Assign()

Dim NewWbk As Workbook, SpcWbk As Workbook
Dim AssignFile As String
Dim r As Integer, s As Integer, b As Integer, a As Integer, c As Integer
Dim i As Long
Dim Gin As String, StSpecialist As String
Dim SecRng As Range
Dim FileDate As String

'*************************************************

'*******************************************************

Set NewWbk = ActiveWorkbook

b = 0
On Error Resume Next

Application.DisplayAlerts = False

OpenOldWorkbook:
FileDate = Format(Date - b, "mmmmyyyy")
AssignFile = "FileLocation\PrevFile-" & FileDate & ".xls"

Workbooks.Open AssignFile, ReadOnly:=True

Application.DisplayAlerts = True

Set SpcWbk = ActiveWorkbook

If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

r = NewWbk.Sheets(1).Range("A1").CurrentRegion.Rows.Count
a = SpcWbk.Sheets.Count
s = SpcWbk.Sheets(a).Range("A1").CurrentRegion.Rows.Count

StrRng = Range(Cells(1, 1), Cells(s, 9)).Address

NewWbk.Activate

Range("I1").Select

i = 2

Do While Cells(i, 1).Value <> ""


ActiveCell.Offset(1, 0).Select
Gin = Cells(i, 1).Value

StSpecialist = "=VLookup(" & Gin & ",'[" & SpcWbk.Name & "]Sheet(" &
a & ")'!" & StrRng & ", 9, false)"
Debug.Print StSpecialist
If IsError(StSpecialist) Then
Cells(i, 9).Value = ""
Else
Cells(i, 9).Value = StSpecialist
End If


i = i + 1

Loop