| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MATCH Worksheet Function
I am continuously getting a runtime error, "Error 1004 application-
defined or object-defined error" when using the MATCH function. I noticed that it occurs only in the second round of the loop. If I delete the entry that works, the code will execute properly again for the first row processed (which it previously did not), and then subsequent rows again obtain the same error message. My question is, does the MATCH function require some kind of reset? Or if anyone has a suggestion to try something else? Thanks! Enzo For n = 2 To llastRow If (Trim(Sheets("Temp Output").Cells(n, 5).Value) < "") Then lend = False a = 2 Do test = Trim(Sheets("Temp Output").Cells(n, 5).Value) lfoundRow = Application.Match(Trim(Sheets("Temp Output").Cells(n, 5).Value), Sheets("Temp Output2").Range(Cells(a, 2), Cells(1000, 2)), 0) lactualRow = lfoundRow + a - 1 If (Trim(Sheets("Temp Output2").Cells(lactualRow + 3, 1).Value) = Trim(Sheets("Temp Output").Cells(n, 1).Value)) Then lend = True Sheets("Temp Output2").Select Range("B" & lactualRow + cReportLength - 2).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Cap by Site'!E" & n Sheets("Temp Output").Select Range("E" & n).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Site Info'!A" & lactualRow End If a = lfoundRow + 1 Loop Until lend = True End If Next n |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MATCH Worksheet Function
On Mar 16, 2:09 pm, "Don Guillett" wrote:
Did you return to the original sheet? Better yet, do the other sheet things without ever leaving the original. Sheets("Temp Output2").Range("B" & lactualRow + cReportLength - 2).Hyperlinks.Add Anchor:=etc -- Don Guillett SalesAid Software "Enz" wrote in message oups.com... I am continuously getting a runtime error, "Error 1004 application- defined or object-defined error" when using the MATCH function. I noticed that it occurs only in the second round of the loop. If I delete the entry that works, the code will execute properly again for the first row processed (which it previously did not), and then subsequent rows again obtain the same error message. My question is, does the MATCH function require some kind of reset? Or if anyone has a suggestion to try something else? Thanks! Enzo For n = 2 To llastRow If (Trim(Sheets("Temp Output").Cells(n, 5).Value) < "") Then lend = False a = 2 Do test = Trim(Sheets("Temp Output").Cells(n, 5).Value) lfoundRow = Application.Match(Trim(Sheets("Temp Output").Cells(n, 5).Value), Sheets("Temp Output2").Range(Cells(a, 2), Cells(1000, 2)), 0) lactualRow = lfoundRow + a - 1 If (Trim(Sheets("Temp Output2").Cells(lactualRow + 3, 1).Value) = Trim(Sheets("Temp Output").Cells(n, 1).Value)) Then lend = True Sheets("Temp Output2").Select Range("B" & lactualRow + cReportLength - 2).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Cap by Site'!E" & n Sheets("Temp Output").Select Range("E" & n).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Site Info'!A" & lactualRow End If a = lfoundRow + 1 Loop Until lend = True End If Next n- Hide quoted text - - Show quoted text - I tried what you indicated, but I think the Anchor statement actually performs the selection anyway, so I still get the same message. If I take the anchor statement out, I get another Error regarding parameters, I believe it was Error 450. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MATCH Worksheet Function
Does this idea help? Test it
'Executed from ANYWHERE in the active workbook Sub dohyperlinkonothersheet() With Worksheets("Sheet8") .Hyperlinks.Add anchor:=.Range("b6"), _ Address:="", SubAddress:="Sheet11!b1" End With End Sub -- Don Guillett SalesAid Software "Enz" wrote in message oups.com... On Mar 16, 2:09 pm, "Don Guillett" wrote: Did you return to the original sheet? Better yet, do the other sheet things without ever leaving the original. Sheets("Temp Output2").Range("B" & lactualRow + cReportLength - 2).Hyperlinks.Add Anchor:=etc -- Don Guillett SalesAid Software "Enz" wrote in message oups.com... I am continuously getting a runtime error, "Error 1004 application- defined or object-defined error" when using the MATCH function. I noticed that it occurs only in the second round of the loop. If I delete the entry that works, the code will execute properly again for the first row processed (which it previously did not), and then subsequent rows again obtain the same error message. My question is, does the MATCH function require some kind of reset? Or if anyone has a suggestion to try something else? Thanks! Enzo For n = 2 To llastRow If (Trim(Sheets("Temp Output").Cells(n, 5).Value) < "") Then lend = False a = 2 Do test = Trim(Sheets("Temp Output").Cells(n, 5).Value) lfoundRow = Application.Match(Trim(Sheets("Temp Output").Cells(n, 5).Value), Sheets("Temp Output2").Range(Cells(a, 2), Cells(1000, 2)), 0) lactualRow = lfoundRow + a - 1 If (Trim(Sheets("Temp Output2").Cells(lactualRow + 3, 1).Value) = Trim(Sheets("Temp Output").Cells(n, 1).Value)) Then lend = True Sheets("Temp Output2").Select Range("B" & lactualRow + cReportLength - 2).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Cap by Site'!E" & n Sheets("Temp Output").Select Range("E" & n).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Site Info'!A" & lactualRow End If a = lfoundRow + 1 Loop Until lend = True End If Next n- Hide quoted text - - Show quoted text - I tried what you indicated, but I think the Anchor statement actually performs the selection anyway, so I still get the same message. If I take the anchor statement out, I get another Error regarding parameters, I believe it was Error 450. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MATCH Worksheet Function
or
Sub dohyperlinkonothersheet1() Set mycel = Sheets("sheet8").Range("b5") mycel.Hyperlinks.Add anchor:=mycel, _ Address:="", SubAddress:="sheet11!b1" End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Does this idea help? Test it 'Executed from ANYWHERE in the active workbook Sub dohyperlinkonothersheet() With Worksheets("Sheet8") .Hyperlinks.Add anchor:=.Range("b6"), _ Address:="", SubAddress:="Sheet11!b1" End With End Sub -- Don Guillett SalesAid Software "Enz" wrote in message oups.com... On Mar 16, 2:09 pm, "Don Guillett" wrote: Did you return to the original sheet? Better yet, do the other sheet things without ever leaving the original. Sheets("Temp Output2").Range("B" & lactualRow + cReportLength - 2).Hyperlinks.Add Anchor:=etc -- Don Guillett SalesAid Software "Enz" wrote in message oups.com... I am continuously getting a runtime error, "Error 1004 application- defined or object-defined error" when using the MATCH function. I noticed that it occurs only in the second round of the loop. If I delete the entry that works, the code will execute properly again for the first row processed (which it previously did not), and then subsequent rows again obtain the same error message. My question is, does the MATCH function require some kind of reset? Or if anyone has a suggestion to try something else? Thanks! Enzo For n = 2 To llastRow If (Trim(Sheets("Temp Output").Cells(n, 5).Value) < "") Then lend = False a = 2 Do test = Trim(Sheets("Temp Output").Cells(n, 5).Value) lfoundRow = Application.Match(Trim(Sheets("Temp Output").Cells(n, 5).Value), Sheets("Temp Output2").Range(Cells(a, 2), Cells(1000, 2)), 0) lactualRow = lfoundRow + a - 1 If (Trim(Sheets("Temp Output2").Cells(lactualRow + 3, 1).Value) = Trim(Sheets("Temp Output").Cells(n, 1).Value)) Then lend = True Sheets("Temp Output2").Select Range("B" & lactualRow + cReportLength - 2).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Cap by Site'!E" & n Sheets("Temp Output").Select Range("E" & n).Select ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _ SubAddress:="'Site Info'!A" & lactualRow End If a = lfoundRow + 1 Loop Until lend = True End If Next n- Hide quoted text - - Show quoted text - I tried what you indicated, but I think the Anchor statement actually performs the selection anyway, so I still get the same message. If I take the anchor statement out, I get another Error regarding parameters, I believe it was Error 450. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match worksheet Function displays wrong data on calculation | Excel Worksheet Functions | |||
| Match() worksheet function | Excel Worksheet Functions | |||
| index, match, offset worksheet function | Excel Worksheet Functions | |||
| Worksheet function match - run time error | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Hybrid Mode
