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

MATCH Worksheet Function
 

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

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.

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.

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.