IsError and Match function

[Guest]

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

 

[Guest]

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

 

[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)

 

[Guest]

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

 

[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)

 

[Guest]

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

 

[Guest]

<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.

 

[Guest]

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