Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding values (Excel2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Finding values (Excel2000)

    Shouldn't the line

    With Range(Ezenet)

    be

    With EzenetData

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

  3. #3
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding values (Excel2000)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding values (Excel2000)

    Is this what you are after?

    Steve

    <pre> 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
    </pre>


  5. #5
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding values (Excel2000)

    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding values (Excel2000)

    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

  7. #7
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding values (Excel2000)

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

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding values (Excel2000)

    I see Steve has beaten me on the draw... surprise, surprise! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    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.

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Finding values (Excel2000)

    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
    [MS MVP - Word]

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Finding values (Excel2000)

    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

  11. #11
    Star Lounger
    Join Date
    Sep 2003
    Location
    Toronto, Ontario, Canada
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding values (Excel2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •