Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Index and WorksheetFunction.Match
I've used Index and Match in Excel for some time to take a value from one
worksheet, find it on another and return a different value, looking something like "Index(range,Match(value,range,0),1)". I am trying to do the same thing in VBA, but haven't quite got the hang of it. I keep getting an "Application-defined or object-defined error". Here's what I have (using some abbreviations for space): If App.WF.Index(Wkshts("Sheet1").Range(Cells(3,3),Cel ls(LastRow, 3)), App.WF.Match(Wkshts("Sheet2").Range("A2").Offset(C urrRow,2), Wkshts("Sheet1").Range(Cells(3,1),Cells(LastRow, 1)), 0), 1) = Value Then GoTo Line Feel free to point me toward a more efficient way of doing this. Otherwise, I would greatly appreciate it if someone could point out why VBA doesn't like me or my code. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Index and WorksheetFunction.Match
Hi
First, you should always copy/paste your original code, as typos can be a part of the error. In this case you have to add an sheet reference for each range/cell object (not tested): Dim shA As Worksheet Set shA = Worksheets("Sheet1") If Application.WorksheetFunction.Index(shA.Range(shA. Cells(3, 3), shA.Cells(LastRow, 3)), Application.WorksheetFunction.Match(Worksheets("Sh eet2").Range("A2").Offset(CurrRow, 2), shA.Range(shA.Cells(3, 1), shA.Cells(LastRow, 1)), 0), 1) = Value Then GoTo Line Regards, Per "Luke" skrev i meddelelsen ... I've used Index and Match in Excel for some time to take a value from one worksheet, find it on another and return a different value, looking something like "Index(range,Match(value,range,0),1)". I am trying to do the same thing in VBA, but haven't quite got the hang of it. I keep getting an "Application-defined or object-defined error". Here's what I have (using some abbreviations for space): If App.WF.Index(Wkshts("Sheet1").Range(Cells(3,3),Cel ls(LastRow, 3)), App.WF.Match(Wkshts("Sheet2").Range("A2").Offset(C urrRow,2), Wkshts("Sheet1").Range(Cells(3,1),Cells(LastRow, 1)), 0), 1) = Value Then GoTo Line Feel free to point me toward a more efficient way of doing this. Otherwise, I would greatly appreciate it if someone could point out why VBA doesn't like me or my code. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Index and WorksheetFunction.Ma
Thank you for your help. I added the Dim statement and used Set as you
described. Instead of the original error message, I am now getting the following: Method 'Range' of object '_Worksheet' failed "Per Jessen" wrote: Hi First, you should always copy/paste your original code, as typos can be a part of the error. In this case you have to add an sheet reference for each range/cell object (not tested): Dim shA As Worksheet Set shA = Worksheets("Sheet1") If Application.WorksheetFunction.Index(shA.Range(shA. Cells(3, 3), shA.Cells(LastRow, 3)), Application.WorksheetFunction.Match(Worksheets("Sh eet2").Range("A2").Offset(CurrRow, 2), shA.Range(shA.Cells(3, 1), shA.Cells(LastRow, 1)), 0), 1) = Value Then GoTo Line Regards, Per "Luke" skrev i meddelelsen ... I've used Index and Match in Excel for some time to take a value from one worksheet, find it on another and return a different value, looking something like "Index(range,Match(value,range,0),1)". I am trying to do the same thing in VBA, but haven't quite got the hang of it. I keep getting an "Application-defined or object-defined error". Here's what I have (using some abbreviations for space): If App.WF.Index(Wkshts("Sheet1").Range(Cells(3,3),Cel ls(LastRow, 3)), App.WF.Match(Wkshts("Sheet2").Range("A2").Offset(C urrRow,2), Wkshts("Sheet1").Range(Cells(3,1),Cells(LastRow, 1)), 0), 1) = Value Then GoTo Line Feel free to point me toward a more efficient way of doing this. Otherwise, I would greatly appreciate it if someone could point out why VBA doesn't like me or my code. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Index and WorksheetFunction.Match
Along with Per's suggestion of qualifying your ranges, I'd break it into smaller
pieces. Dim res as variant 'could be an error dim myRng as range dim wks as worksheet dim myCell as range Dim CurrRow as long dim SomeValue as variant SomeValue = "whatever you're checking" CurrRow = 1 'whatever... set mycell = worksheets("sheet2").range("A2").offset(currRow,2) set wks = worksheets("Sheet1") with wks set myrng = .range("A3",.cells(lastrow,"A")) end with res = application.match(mycell.value, myrng, 0) if iserror(res) then 'there is no match 'what should happen else if myrng.cells(1).offset(res-1).value = SomeValue then 'do nothing else 'do something else end if end if ========= I'm not sure why you'd declare some variable for Worksheets (wkshts doesn't save me that much typing). Maybe you're qualifying the workbook??? I'd just use: with workbooks("Someworkbookhere.xls") set wks = .worksheets("sheet1") '... end with And notice that I didn't use the worksheetfunction qualifier with the match() line. I used application.match(). When I use this syntax, I can check for an error being returned. If I used application.worksheetfunction.match() I'd have to code around a run-time error: on error resume next somevar = application.worksheetfunction.match(...) if err.number < 0 then err.clear 'no match 'do the no match stuff else 'do the match stuff here end if on error goto 0 ===== And since application.index() or application.worksheetfunction.index() is essentially an offset from the first cell in range. I used: myrng.cells(1).offset(res-1).value But I could have used: myrng(res).value too. Luke wrote: I've used Index and Match in Excel for some time to take a value from one worksheet, find it on another and return a different value, looking something like "Index(range,Match(value,range,0),1)". I am trying to do the same thing in VBA, but haven't quite got the hang of it. I keep getting an "Application-defined or object-defined error". Here's what I have (using some abbreviations for space): If App.WF.Index(Wkshts("Sheet1").Range(Cells(3,3),Cel ls(LastRow, 3)), App.WF.Match(Wkshts("Sheet2").Range("A2").Offset(C urrRow,2), Wkshts("Sheet1").Range(Cells(3,1),Cells(LastRow, 1)), 0), 1) = Value Then GoTo Line Feel free to point me toward a more efficient way of doing this. Otherwise, I would greatly appreciate it if someone could point out why VBA doesn't like me or my code. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with WorksheetFunction.Index and WorksheetFunction.Match
Ps. I wouldn't use "Value" as a variable name. Excel/VBA may allow it, but it
would confuse the heck out of me! Luke wrote: I've used Index and Match in Excel for some time to take a value from one worksheet, find it on another and return a different value, looking something like "Index(range,Match(value,range,0),1)". I am trying to do the same thing in VBA, but haven't quite got the hang of it. I keep getting an "Application-defined or object-defined error". Here's what I have (using some abbreviations for space): If App.WF.Index(Wkshts("Sheet1").Range(Cells(3,3),Cel ls(LastRow, 3)), App.WF.Match(Wkshts("Sheet2").Range("A2").Offset(C urrRow,2), Wkshts("Sheet1").Range(Cells(3,1),Cells(LastRow, 1)), 0), 1) = Value Then GoTo Line Feel free to point me toward a more efficient way of doing this. Otherwise, I would greatly appreciate it if someone could point out why VBA doesn't like me or my code. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
WorksheetFunction.Match | Excel Programming | |||
WorksheetFunction.Index producing error | Excel Programming | |||
Application.WorksheetFunction.Index syntax | Excel Programming | |||
WorkSheetFunction.CountIf & WorkSheetFunction.SumIf with 2 conditions? | Excel Programming | |||
worksheetfunction.match | Excel Programming |