IsError and Match function

[GDCross]

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

[Tom Ogilvy]

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

[Tom Ogilvy]

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

[Tom Ogilvy]

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

[GDCross]

I tried your suggestion with no success. However, I used the code for your
submission to "Calling all MVPs! Macro to delete non matches b/t two lists"
and it works. Why does CountIf so elegantly here? Why didn't you use IsError
and Match? Thanks. GD


"Tom Ogilvy" wrote:

> 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

[Tom Ogilvy]

I made numerous changes, but missed the string as the 2nd argument for match.
but then I believe you also mean sh.Range("B" & i) as the first argument
which Bob Missed <LOL>

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), _
Range("ElginPartsRange"),0)) Then
sh.Rows(I).Delete
End If
Next I

I must use match if I need to know where it is and often time use it to see
if it is there - however I suspect countif is faster and it will tell me if
it is there without telling me where it is. Countif must work on a range,
but it not limited to a single row or single column like match - it can do a
rectangle. Match will work with an array as well, which countif will not.

--
Regards,
Tom Ogilvy





"GDCross" wrote:

> I tried your suggestion with no success. However, I used the code for your
> submission to "Calling all MVPs! Macro to delete non matches b/t two lists"
> and it works. Why does CountIf so elegantly here? Why didn't you use IsError
> and Match? Thanks. GD
>
>
> "Tom Ogilvy" wrote:
>
> > 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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[Tom Ogilvy]

<LOL>
Whether you said it or not, it was 6 minutes after I posted according to the
presentation I am looking at. So yes, I did overlook making that correction,
but I am well aware that Match doesn't take a string as the second argument.

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[GDCross]

Tom/Bob: I thank God for the both of you have both helped me out this day.
Take care and have a blessed rest of the day. GD

"Tom Ogilvy" wrote:

> <LOL>
> Whether you said it or not, it was 6 minutes after I posted according to the
> presentation I am looking at. So yes, I did overlook making that correction,
> but I am well aware that Match doesn't take a string as the second argument.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Bob Phillips" wrote:
>
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[Bob Phillips]

Because you dot qualified it
And you need to specify the Range object
And you don't need to select it

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

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



--
HTH

Bob Phillips

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

"GDCross" <[email protected]> wrote in message
news:[email protected]...
> 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