View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default IsError and Match function

AS I said, this doesn't work

If IsError(Application.Match(sh.Range("B" & I), "ElginPartsRange", 0)) Then

You need the range object


If IsError(Application.Match(sh.Range("B" & I), Range("ElginPartsRange"),
0)) Then


--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

"Tom Ogilvy" wrote in message
...
because you have the priod in front and there is no qualifier. Try

Dim sh as Worksheet
set sh = Activesheet

FinalRowText = sh.Range("A65536").End(xlUp).Row
With Sheets("Elgin Parts")
FinalRowElginParts = .Range("A65536").End(xlUp).Row
.Range("A2:A" & FinalRowElginParts).Name = "ElginPartsRange"
End With
For I = 2 To FinalRowText
If IsError(Application.Match(sh.Range("B" & I), "ElginPartsRange",
0)) Then
sh.Rows(I).Delete
End If
Next I

--
Regards,
Tom Ogilvy





"GDCross" wrote:

I need to have this code delete records of parts from a worksheet that

do not
match a parts list (in sheet "Elgin Parts"). However, I get the error
"invalid or unqualified reference" when I run this code. Why is the code
".Rows(I).Delete" a problem? Any answers? Thanks, GD

FinalRowText = Range("A65536").End(xlUp).Row
Sheets("Elgin Parts").Select
FinalRowElginParts = Range("A65536").End(xlUp).Row
Range("A2:A" & FinalRowElginParts).Name = "ElginPartsRange"

For I = 2 To FinalRowText
Range("A" & I).Select
If IsError(Application.Match("B" & I, "ElginPartsRange", 0)) Then
.Rows(I).Delete
End If
Next I