Finding values (Excel2000)

Topic

New Reply

There’s a fundamental flaw in my code which about 4,000 people will find in a flash!
I have a list of names in the range, OSFI1, on the sheet OSFIList.
I want to look for those names in the range of cells, EzenetData on the sheet Ezenet.
If a name is found, I want the row containing the found name (on the Ezenet sheet) to be shaded.
Help. please.
Ross

Reply | Quote

Replies

Shouldn’t the line

With Range(Ezenet)

be

With EzenetData

There is no variable named Ezenet in the code you attached.

Reply | Quote

Shouldn’t the line

With Range(Ezenet)

be

With EzenetData

There is no variable named Ezenet in the code you attached.

Reply | Quote

Hans,
Thanks for the quick response. Indeed, the range should be EzenetData, but that did not fix it. The code runs merrily along but does not find names it should!!
Ross

Reply | Quote

Is this what you are after?

Steve

    Dim OSFI1 As Range
    Dim EzenetData As Range
    Dim cell As Range
    Dim c
    Dim numrows As Long
    
    Sheets("Ezenet").Activate
    Set EzenetData = Range("B2").CurrentRegion
    
    Sheets("OSFIList").Activate
    
    numrows = Range("A1").CurrentRegion.Rows.Count
   
    Set OSFI1 = Range("B2").Resize(numrows + 1, 1)

    For Each cell In OSFI1
        With EzenetData
            Set c = .Find(What:=cell.Value, LookIn:=xlValues)
            If Not c Is Nothing Then
                With c.Rows.EntireRow.Interior
                    .ColorIndex = 35
                    .Pattern = xlSolid
                End With
                Set c = .FindNext©
            End If
        End With
    Next cell

Reply | Quote

Steve,
Thanks a bundle – works like a charm.
Comparing your code to mine identifies a number of errors on my part.
Find has always given me trouble. But I will study your code carefully for future work.
Ross

Reply | Quote

One thing I failed to mention this line:
Set OSFI1 = Range(“B2”).Resize(numrows + 1, 1)

Mimics what you did, since I was not entirely sure of your goal and how the data was set up. The range you set up is 2 rows longer than the last row in the “current region” (ie if the current region is A1:C100, you would “set” B2:B102 since your “offset”100 rows from B2. If you only meant to select B2:B100 then use:

Set OSFI1 = Range(“B2”).Resize(numrows – 1, 1)

Steve

Reply | Quote

Thank you. Yes, that much I was able to figure.

Reply | Quote

Thank you. Yes, that much I was able to figure.

Reply | Quote

One thing I failed to mention this line:
Set OSFI1 = Range(“B2”).Resize(numrows + 1, 1)

Mimics what you did, since I was not entirely sure of your goal and how the data was set up. The range you set up is 2 rows longer than the last row in the “current region” (ie if the current region is A1:C100, you would “set” B2:B102 since your “offset”100 rows from B2. If you only meant to select B2:B100 then use:

Set OSFI1 = Range(“B2”).Resize(numrows – 1, 1)

Steve

Reply | Quote

Steve,
Thanks a bundle – works like a charm.
Comparing your code to mine identifies a number of errors on my part.
Find has always given me trouble. But I will study your code carefully for future work.
Ross

Reply | Quote

Is this what you are after?

Steve

    Dim OSFI1 As Range
    Dim EzenetData As Range
    Dim cell As Range
    Dim c
    Dim numrows As Long
    
    Sheets("Ezenet").Activate
    Set EzenetData = Range("B2").CurrentRegion
    
    Sheets("OSFIList").Activate
    
    numrows = Range("A1").CurrentRegion.Rows.Count
   
    Set OSFI1 = Range("B2").Resize(numrows + 1, 1)

    For Each cell In OSFI1
        With EzenetData
            Set c = .Find(What:=cell.Value, LookIn:=xlValues)
            If Not c Is Nothing Then
                With c.Rows.EntireRow.Interior
                    .ColorIndex = 35
                    .Pattern = xlSolid
                End With
                Set c = .FindNext©
            End If
        End With
    Next cell

Reply | Quote

Hans,
Thanks for the quick response. Indeed, the range should be EzenetData, but that did not fix it. The code runs merrily along but does not find names it should!!
Ross

Reply | Quote

I see Steve has beaten me on the draw… surprise, surprise!
But here’s something that seems to do what you’re after, using 2 named ranges rng1 & rng2 in the same workbook.

Alan

Edited – I have amended this code so that it now correctly (I hope) searches the second range for duplicate entries.

Reply | Quote

I see Steve has beaten me on the draw… surprise, surprise!
But here’s something that seems to do what you’re after, using 2 named ranges rng1 & rng2 in the same workbook.

Alan

Edited – I have amended this code so that it now correctly (I hope) searches the second range for duplicate entries.

Reply | Quote

Hi Ross,

Just a thought: If you’re using named ranges, it should be quite easy to achieve the same result using conditional formatting, without the need to resort to vba. Although conditional formatting might noticeably slow down a large workbook, you wouldn’t need to remember to run a macro and conditional formatting would probably work faster than the macro anyway.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Conditional formatting would probably be faster.

I have been assuming that his code would eventually delete the rows and he was only highlighting the rows that he wanted to delete. I use this trick alot when I step thru code to make sure if does what I want without actually deleting it.

But perhaps I was jumping to conclusions.

Steve

Reply | Quote

Steve,
I have used conditional formatting where I want exceptions to stand out. But in this case I need to isolate those records containing a match in an indeterminate number of cells, and there are almost 40,000 records. It’s the rows with no hits that are ultimately deleted.
Ross

Reply | Quote

Steve,
I have used conditional formatting where I want exceptions to stand out. But in this case I need to isolate those records containing a match in an indeterminate number of cells, and there are almost 40,000 records. It’s the rows with no hits that are ultimately deleted.
Ross

Reply | Quote

Conditional formatting would probably be faster.

I have been assuming that his code would eventually delete the rows and he was only highlighting the rows that he wanted to delete. I use this trick alot when I step thru code to make sure if does what I want without actually deleting it.

But perhaps I was jumping to conclusions.

Steve

Reply | Quote

Hi Ross,

Just a thought: If you’re using named ranges, it should be quite easy to achieve the same result using conditional formatting, without the need to resort to vba. Although conditional formatting might noticeably slow down a large workbook, you wouldn’t need to remember to run a macro and conditional formatting would probably work faster than the macro anyway.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote