Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and match against separate workbook
I'm trying to do the following with this procedu
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and match against separate workbook
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 "Stan Plumber" wrote in message om... I'm trying to do the following with this procedu 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and match against separate workbook
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. -- Regards, Tom Ogilvy "Stan Plumber" wrote in message om... I'm trying to do the following with this procedu 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and match against separate workbook
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" wrote in message ... 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"? 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. Regards Trevor "Stan Plumber" wrote in message om... I'm trying to do the following with this procedu 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and match against separate workbook
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 "Stan Plumber" wrote in message om... 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" wrote in message ... 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"? 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. Regards Trevor "Stan Plumber" wrote in message om... I'm trying to do the following with this procedu 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup and match against separate workbook
Thanks to everyone for the pointers and advise. Everything worked
perfectly now I "just" need to optimize. Stan "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "Stan Plumber" wrote in message om... I'm trying to do the following with this procedu 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Formula: Return 1st match, then 2nd match, then 3rd match | Excel Discussion (Misc queries) | |||
Lookup? Match? pulling rows from one spreadsheet to match a text f | Excel Worksheet Functions | |||
Index,Match table array in separate workbook | Excel Worksheet Functions | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Lookup worksheet name in separate workbook | Excel Discussion (Misc queries) |