Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not working.
I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not be
trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i

Otto Moehrbach wrote:
Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not
working. I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not
be trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i

If FoundCell is a Range object you should use

Set FoundCell = List2.Find(....)

--
Regards,

Juan Pablo González

Hi
try
For Each i In List1
On Error resume next
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
On Error GoTo 0
Next i

--
Regards
Frank Kabel
Frankfurt, Germany


Otto Moehrbach wrote:
Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not
working. I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not
be trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i

Sligfht amendement

For Each i In List1
On Error resume next
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
On Error GoTo 0
If Not FoundCell Is Nothing Then
'Copy/Paste FoundCell & stuff
Else
'Do something else
End If Next i


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
try
For Each i In List1
On Error resume next
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
On Error GoTo 0
Next i

--
Regards
Frank Kabel
Frankfurt, Germany


Otto Moehrbach wrote:
Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not
working. I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not
be trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i

Plus of course the Set Foundcell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Sligfht amendement

For Each i In List1
On Error resume next
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
On Error GoTo 0
If Not FoundCell Is Nothing Then
'Copy/Paste FoundCell & stuff
Else
'Do something else
End If Next i


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
try
For Each i In List1
On Error resume next
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
On Error GoTo 0
Next i

--
Regards
Frank Kabel
Frankfurt, Germany


Otto Moehrbach wrote:
Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not
working. I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not
be trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i

Juan
You hit it on the head. I wonder how many times I have made that error.
Muy gracias. Otto
"Juan Pablo González" wrote in message
...
Otto Moehrbach wrote:
Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not
working. I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not
be trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i

If FoundCell is a Range object you should use

Set FoundCell = List2.Find(....)

--
Regards,

Juan Pablo González

Frank & Bob
Thanks for your help. I didn't include the variable declarations in my
post so, of course, you didn't know that FoundCell was a range. My error
was that I did not set FoundCell. Thanks for your help. Otto
"Otto Moehrbach" wrote in message
...
Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not
working.
I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not be
trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i

Bob
My code didn't work even after I changed "FoundCell=" to "Set
FoundCell=". Your code, however, worked just fine. I still don't
understand why my error trap didn't trap the error. Thanks for your help.
Otto
"Bob Phillips" wrote in message
...
Plus of course the Set Foundcell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
Sligfht amendement

For Each i In List1
On Error resume next
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
On Error GoTo 0
If Not FoundCell Is Nothing Then
'Copy/Paste FoundCell & stuff
Else
'Do something else
End If Next i


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
try
For Each i In List1
On Error resume next
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
On Error GoTo 0
Next i

--
Regards
Frank Kabel
Frankfurt, Germany


Otto Moehrbach wrote:
Excel 2002, WinXP
I have List1 on one sheet, List2 on another sheet, and a third sheet
selected. Many items in List1 are not in List2.
I have the following code with a simple error trap that is not
working. I am getting an "Object variable not set..." error on the
FoundCell.......Find line. I am not seeing how that error could not
be trapped. What am I not seeing?
Thanks for your help. Otto
For Each i In List1
On Error GoTo Nexti
FoundCell = List2.Find(What:=i, LookAt:=xlWhole)
'Copy/Paste FoundCell & stuff
Nexti:
On Error GoTo 0
Next i