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

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" wrote in message
oups.com...
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