Lookup and match against separate workbook

  • Thread starter Thread starter Stan Plumber
  • Start date Start date
S

Stan Plumber

I'm trying to do the following with this procedure:

1. In the current worksheet, starting at a specific row, cycle
through each each and match with a defined range in a lookup workbook
named "expttl". If the cell value is a match or "" then continue
until it finds a non match. When if finds a nonmatch return the
column value of cell.

2. I'm having difficulty with the Set RngA =
Workbooks("LookupWorkbook").Sheets("LookupWorsheet").Range line. Any
ideas.

TIA


Function FindColumn() As Long

On Error GoTo Proc_Err

Dim fStart As Boolean
Dim lngHeader As Long
Dim lngX As Long
Dim strCell As String
Dim RngA As Range
Dim wks As Worksheet
Dim res As Variant

fStart = False
lngX = 1

Do Until fStart = True

strCell = Cells(lngHeader, lngX).Value

'If cell is blank assign as date and is ignored
If strCell = "" Then
strCell = "Date"
Else
End If

'Trim spaces on either slide
strCell = RTrim(LTrim(strCell))

'Reference the Export

Set RngA = Workbooks("LookupWorkbook").Sheets("LookupWorsheet").Range("LookupRange")

res = 0

res = Application.WorksheetFunction.Match(strCell, RngA, 0)

If IsError(res) Then
fStart = True
FindVendorStart = lngX
Exit Function
Else
End If

lngX = lngX + 1

Loop


Proc_Exit:
Exit Function

Proc_Err:
Select Case Err.Number
Case 1004
FindVendorStart = lngX
Case Else
Debug.Print Err.Number & " - " & Err.Description
End Select
Resume Proc_Exit

End Function
 
Stan

I think you'll need to be more specific than "I'm having difficulty with
...."

But could it be that you have "LookupWorsheet" rather than
"LookupWorksheet"?

Regards

Trevor
 
This

res = Application.WorksheetFunction.Match(strCell, RngA, 0)

Returns a 1004 error if there is no match.

You can't trap it with IsError

If you want to use IsError, take out the WorksheetFunction part

res = Application.Match(strCell, RngA, 0)

If iserror(res) then
' no match found

Not sure what you are doing with the combination of the error handler and
iserror.
 
In terms of being specific, the procedure is generating subscript and
object errors on this line:

Set RngA = Workbooks("LookupWorkbook").Sheets("LookupWorsheet").Range("LookupRange")
line.


Trevor Shuttleworth said:
Stan

I think you'll need to be more specific than "I'm having difficulty with
..."

But could it be that you have "LookupWorsheet" rather than
"LookupWorksheet"?
Click to expand...

Actually the LookupWorksheet is meant as a place holder simply
referencing the specific sheet that that range is in. Although your
right the "k" is missing.
 
Stan

switch off the error handler at the beginning of the function and step
through the code. The first thing you will discover is that lngHeader is
not assigned a value and hence strCell = Cells(lngHeader, lngX).Value fails
with error 1004 because you are trying to get the value from cell(0,1) which
doesn't exist.

Then you get error 9 (subscript out of range) accessing the lookup range
which probably means that the lookup cannot find the workbook or worksheet.
As I said in my earlier reply ... check the worksheet name.

Regards

Trevor
 
Thanks to everyone for the pointers and advise. Everything worked
perfectly now I "just" need to optimize.

Stan
 
Back
Top