Vlookup on another worksheet 1

[merlynsdad]

I'm trying to compare the ActiveCell in one worksheet to a range in another sheet looking for a match.

strLead = Application.WorksheetFunction.VLookup(Worksheets("Perfect").ActiveCell, Worksheets("Lists").Range("$b$197:f$202"),2, False)

It's giving me bad info and I can't figure out why. Row b in Lists should give me the same name as the ActiveCell in Perfect. strLead is coming up blank in the Watch window.

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

what is the VALUE of Worksheets("Perfect").ActiveCell?

When you look in Lists, what is the VALUE in column B row ??? that corresponde?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

They're both names. This procedure does something akin to selecting the Employee of the Quarter, but we're excluding a certain group.

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

What 'bad information' is being returned?

What makes it 'bad?'

Are the values in column B UNIQUE values or repeating values?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I hardly ever use AcitveANYTHING. It is a lazy man's reference. Rather use an explicit reference of some sort.

That being said, try ActiveCell.Value rather than Worksheets("Perfect").ActiveCell.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

I've changed the code a bit, so it now reads

Do Until ActiveCell.Value = ""
        If ActiveCell.Value = Application.WorksheetFunction.VLookup(Worksheets("Lists").Range("$c$197:$c$202"), 1, False) Then
            Rows(ActiveCell.Row).Select
            Selection.Delete shift:=xlUp
            ActiveCell.Offset(0, 4).Select
        End If
    Loop

When I test it, it reads the IF as true, even when the activecell.value does not appear in the Vlookup table. Activecell.value is a name, and C197-C202 are also names that I'm trying to match.

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

you missed including the lookup value!!!

ALSO, if you just want to match the value, then use the MATCH function which is a better approch...

TestValue = Application.MATCH([highlight]ActiveCell.value[/highlight], Worksheets("Lists").Range("$C:C"), 0)

if not IsError(TestValue) then
   if ActiveCell.value = TestValue then

   end if
end if

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops...

        TestValue = Application.Match(ActiveCell.Value, Worksheets("Lists").Range("C:C"), 0)
        If Not IsError(TestValue) Then
            If ActiveCell.Value = [b]Application.Index(Worksheets("Lists").Range("C:C"), TestValue, 1)[/b] Then
                Rows(ActiveCell.Row).Select
                Selection.Delete shift:=xlUp
                ActiveCell.Offset(0, 4).Select
            End If
        End If

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, deleting rows in top-down loop will inevitabley be a problem, as you loose the row reference.

Change your process to a bottom-up loop instead.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[merlynsdad]

Thanks; I've never used MATCH so I'll try it. As for the top down loop, it's a bit too late for that, but I appreciate the heads up. The program loops thru 1500 rows looking for 9 parameters top down, deleting rows that don't match. It works fine, but has more ActiveWhatever offsets than Mardi Gras has beads!

If the square peg won't fit in the round hole, sand off the corners.

 

[merlynsdad]

Tried your code using MATCH and got left with an index number instead of a name, and I need the name. Went back and inserted the lookup value so it now reads

Do Until ActiveCell.Value = ""
        If ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell.Value, Worksheets("Lists").Range("$c$197:$c$202"), 1, False) Then
            Rows(ActiveCell.Row).Select
            Selection.Delete shift:=xlUp
            ActiveCell.Offset(0, 4).Select
        End If
    Loop

but the If is still true when the activecell does not equal any of the names in the Vlookup range.

If the square peg won't fit in the round hole, sand off the corners.

 

[SkipVought]

Obviously, you did not try the REVISION I posted immediately after that has the VALUE, not the offset number.

So here's YOUR code modified...

    Dim TestValue
    Do Until ActiveCell.Value = ""
        TestValue = Application.VLookup(ActiveCell.Value, Worksheets("Lists").Range("C:C"), 1, False)
        
        If Not IsError(TestValue) Then
            If ActiveCell.Value = TestValue Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}