error 2015 performing vlookup

[SP]

The following code inserts #VALUE when I attempt to use the
Application.Vlookup
function.
If I mouse over the Cells(dRow, dCol) I see error 2015 in the pop-up
box.

I am using Excel 2002. Any idea what is wrong ?

Thanks in advance
Sal

Sub CreateReport()
'Declare variables
Dim strFile As String
Dim wkbCode As Workbook
Dim wkbReport As Workbook
Dim wksCustom As Worksheet
Dim dCol As Double
Dim dRow As Double
Dim tmpString As String
Dim tmpRange As String


Set wkbCode = ThisWorkbook

'Let user choose workbook to open
strFile = Application.GetOpenFilename _
("Text Files (*.txt),*.txt, Excel Files (*.xls), *.xls", 2)
If strFile <> "False" Then
Set wkbReport = Workbooks.Open(strFile)
End If

'Display message at bottom of screen
Application.StatusBar = "Creating the report..."

'Delete "Category" column
dCol = Application.Match("Category", Rows(1), 0)
If Not IsError(dCol) Then
Columns(dCol).Delete
End If

'Insert "Manager" column between "Status" and "Caller" columns
dCol = Application.Match("Caller", Rows(1), 0)
If Not IsError(dCol) Then
Columns(dCol).Insert
End If
Cells(1, dCol) = "Manager"


'look up area manager and insert into "Area Mgr" column
Application.EnableEvents = False
tmpRange = "[TicketReport.xls]tmp!$I$6:$J$38"
For dRow = 2 To 100
tmpString = Cells(dRow, dCol + 1)
Cells(dRow, dCol) = Application.VLookup(tmpString, tmpRange, 2,
False)
Next dRow
Application.EnableEvents = True

[Tom Ogilvy]

vlookup requires a range reference as the second argument. You are supplying
a string

tmpRange = "[TicketReport.xls]tmp!$I$6:$J$38"

so change the declaration from
> Dim tmpRange As String

to Dim tmpRange as Range

then do

set tmpRange = "Workbooks("TicketReport.xls") _
.Worksheets("Tmp").Range("I6:J38")

TicketReport.xls will need to be open when you run the code.

--
Regards,
Tom Ogilvy

"SP" <[email protected]> wrote in message
news:[email protected]...
> The following code inserts #VALUE when I attempt to use the
> Application.Vlookup
> function.
> If I mouse over the Cells(dRow, dCol) I see error 2015 in the pop-up
> box.
>
> I am using Excel 2002. Any idea what is wrong ?
>
> Thanks in advance
> Sal
>
> Sub CreateReport()
> 'Declare variables
> Dim strFile As String
> Dim wkbCode As Workbook
> Dim wkbReport As Workbook
> Dim wksCustom As Worksheet
> Dim dCol As Double
> Dim dRow As Double
> Dim tmpString As String
> Dim tmpRange As String
>
>
> Set wkbCode = ThisWorkbook
>
> 'Let user choose workbook to open
> strFile = Application.GetOpenFilename _
> ("Text Files (*.txt),*.txt, Excel Files (*.xls), *.xls", 2)
> If strFile <> "False" Then
> Set wkbReport = Workbooks.Open(strFile)
> End If
>
> 'Display message at bottom of screen
> Application.StatusBar = "Creating the report..."
>
> 'Delete "Category" column
> dCol = Application.Match("Category", Rows(1), 0)
> If Not IsError(dCol) Then
> Columns(dCol).Delete
> End If
>
> 'Insert "Manager" column between "Status" and "Caller" columns
> dCol = Application.Match("Caller", Rows(1), 0)
> If Not IsError(dCol) Then
> Columns(dCol).Insert
> End If
> Cells(1, dCol) = "Manager"
>
>
> 'look up area manager and insert into "Area Mgr" column
> Application.EnableEvents = False
> tmpRange = "[TicketReport.xls]tmp!$I$6:$J$38"
> For dRow = 2 To 100
> tmpString = Cells(dRow, dCol + 1)
> Cells(dRow, dCol) = Application.VLookup(tmpString, tmpRange, 2,
> False)
> Next dRow
> Application.EnableEvents = True
>