+ Reply to Thread
Results 1 to 6 of 6

find next finds too much.

  1. #1
    Guest

    find next finds too much.

    Greeting,
    I am developing something for a user.
    i have 2 cascading combo that corrosponds with data on the
    sheet. the user wants the combo to change the color on the
    data on the sheet when she selects something from the
    combo box.
    I have achieved this. however... the find next part of the
    code goes through the range what seems like 50 times be
    for it stops causing a delay of about 3-5 seconds.
    meanwhile the now colored cells just flicker as the code
    loops through the range over and over. I have tried
    several thing but nothing works. i'm stumped. How do i
    have the code go through the range only once.
    heres the code i have so far....
    it is the for next loop that is doing it.
    Private Sub ComboBox2_Change()
    Dim c As String
    Dim rng As Range
    Dim cell As Range
    c = Me.ComboBox2.Value
    Set rng = Range("A1:R33")
    rng.Interior.ColorIndex = xlNone
    If c = "" Then
    rng.Interior.ColorIndex = xlNone
    Else
    Cells.Find(What:=c, _
    After:=Range("A1"), _
    LookIn:=xlFormulas, _
    LookAt:=xlPart, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False).Activate
    ActiveCell.Interior.ColorIndex = 42
    If ActiveCell.Interior.ColorIndex = 42 Then
    For Each cell In rng
    Cells.FindNext(After:=ActiveCell).Activate
    ActiveCell.Interior.ColorIndex = 42
    Next
    End If
    End If
    End Sub


  2. #2
    Chip
    Guest

    Re: find next finds too much.

    Is that for next loop simply supposed to color the range of A1:R33?


  3. #3
    Guest

    Re: find next finds too much.

    no. It is looking of what is in the combo box and if it
    finds a match, it colors the interia on that cell green.

    >-----Original Message-----
    >Is that for next loop simply supposed to color the range

    of A1:R33?
    >
    >.
    >


  4. #4
    Tom Ogilvy
    Guest

    Re: find next finds too much.

    Private Sub ComboBox2_Change()
    Dim c As String
    Dim rng As Range
    Dim cell As Range
    Dim sAddr as String
    c = Me.ComboBox2.Value
    Range("A1:R33").Interior.ColorIndex = xlNone
    If c = "" Then Exit Sub
    set rng = Range("A1:R33").Find(What:=c, _
    After:=Range("A1"), _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    if not rng is nothing then
    sAddr = rng.Address
    Do
    rng.Interior.ColorIndex = 42
    set rng =Range("A1:R33").FindNext(rng)
    loop until rng.Addr = sAddr
    End if
    End Sub

    --
    Regards,
    Tom Ogilvy

    <anonymous@discussions.microsoft.com> wrote in message
    news:17e901c4ffe0$ce692150$a601280a@phx.gbl...
    > Greeting,
    > I am developing something for a user.
    > i have 2 cascading combo that corrosponds with data on the
    > sheet. the user wants the combo to change the color on the
    > data on the sheet when she selects something from the
    > combo box.
    > I have achieved this. however... the find next part of the
    > code goes through the range what seems like 50 times be
    > for it stops causing a delay of about 3-5 seconds.
    > meanwhile the now colored cells just flicker as the code
    > loops through the range over and over. I have tried
    > several thing but nothing works. i'm stumped. How do i
    > have the code go through the range only once.
    > heres the code i have so far....
    > it is the for next loop that is doing it.
    > Private Sub ComboBox2_Change()
    > Dim c As String
    > Dim rng As Range
    > Dim cell As Range
    > c = Me.ComboBox2.Value
    > Set rng = Range("A1:R33")
    > rng.Interior.ColorIndex = xlNone
    > If c = "" Then
    > rng.Interior.ColorIndex = xlNone
    > Else
    > Cells.Find(What:=c, _
    > After:=Range("A1"), _
    > LookIn:=xlFormulas, _
    > LookAt:=xlPart, _
    > SearchOrder:=xlByRows, _
    > SearchDirection:=xlNext, _
    > MatchCase:=False).Activate
    > ActiveCell.Interior.ColorIndex = 42
    > If ActiveCell.Interior.ColorIndex = 42 Then
    > For Each cell In rng
    > Cells.FindNext(After:=ActiveCell).Activate
    > ActiveCell.Interior.ColorIndex = 42
    > Next
    > End If
    > End If
    > End Sub
    >




  5. #5
    Guest

    Re: find next finds too much.

    thanks tom. your rewrite worked perfectly.

    >-----Original Message-----
    >Private Sub ComboBox2_Change()
    >Dim c As String
    >Dim rng As Range
    >Dim cell As Range
    >Dim sAddr as String
    >c = Me.ComboBox2.Value
    >Range("A1:R33").Interior.ColorIndex = xlNone
    >If c = "" Then Exit Sub
    >set rng = Range("A1:R33").Find(What:=c, _
    > After:=Range("A1"), _
    > LookIn:=xlValues, _
    > LookAt:=xlWhole, _
    > SearchOrder:=xlByRows, _
    > SearchDirection:=xlNext, _
    > MatchCase:=False)
    >if not rng is nothing then
    > sAddr = rng.Address
    > Do
    > rng.Interior.ColorIndex = 42
    > set rng =Range("A1:R33").FindNext(rng)
    > loop until rng.Addr = sAddr
    >End if
    >End Sub
    >
    >--
    >Regards,
    >Tom Ogilvy
    >
    ><anonymous@discussions.microsoft.com> wrote in message
    >news:17e901c4ffe0$ce692150$a601280a@phx.gbl...
    >> Greeting,
    >> I am developing something for a user.
    >> i have 2 cascading combo that corrosponds with data on

    the
    >> sheet. the user wants the combo to change the color on

    the
    >> data on the sheet when she selects something from the
    >> combo box.
    >> I have achieved this. however... the find next part of

    the
    >> code goes through the range what seems like 50 times be
    >> for it stops causing a delay of about 3-5 seconds.
    >> meanwhile the now colored cells just flicker as the code
    >> loops through the range over and over. I have tried
    >> several thing but nothing works. i'm stumped. How do i
    >> have the code go through the range only once.
    >> heres the code i have so far....
    >> it is the for next loop that is doing it.
    >> Private Sub ComboBox2_Change()
    >> Dim c As String
    >> Dim rng As Range
    >> Dim cell As Range
    >> c = Me.ComboBox2.Value
    >> Set rng = Range("A1:R33")
    >> rng.Interior.ColorIndex = xlNone
    >> If c = "" Then
    >> rng.Interior.ColorIndex = xlNone
    >> Else
    >> Cells.Find(What:=c, _
    >> After:=Range("A1"), _
    >> LookIn:=xlFormulas, _
    >> LookAt:=xlPart, _
    >> SearchOrder:=xlByRows, _
    >> SearchDirection:=xlNext, _
    >> MatchCase:=False).Activate
    >> ActiveCell.Interior.ColorIndex = 42
    >> If ActiveCell.Interior.ColorIndex = 42 Then
    >> For Each cell In rng
    >> Cells.FindNext

    (After:=ActiveCell).Activate
    >> ActiveCell.Interior.ColorIndex = 42
    >> Next
    >> End If
    >> End If
    >> End Sub
    >>

    >
    >
    >.
    >


  6. #6
    Tom Ogilvy
    Guest

    Re: find next finds too much.

    There was a typo near the bottom

    Private Sub ComboBox2_Change()
    Dim c As String
    Dim rng As Range
    Dim cell As Range
    Dim sAddr as String
    c = Me.ComboBox2.Value
    Range("A1:R33").Interior.ColorIndex = xlNone
    If c = "" Then Exit Sub
    set rng = Range("A1:R33").Find(What:=c, _
    After:=Range("A1"), _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    if not rng is nothing then
    sAddr = rng.Address
    Do
    rng.Interior.ColorIndex = 42
    set rng =Range("A1:R33").FindNext(rng)
    loop until rng.Address = sAddr '<====
    End if
    End Sub

    --
    Regards,
    Tom Ogilvy


    "Tom Ogilvy" <twogilvy@msn.com> wrote in message
    news:%23knQqE$$EHA.2728@TK2MSFTNGP10.phx.gbl...
    > Private Sub ComboBox2_Change()
    > Dim c As String
    > Dim rng As Range
    > Dim cell As Range
    > Dim sAddr as String
    > c = Me.ComboBox2.Value
    > Range("A1:R33").Interior.ColorIndex = xlNone
    > If c = "" Then Exit Sub
    > set rng = Range("A1:R33").Find(What:=c, _
    > After:=Range("A1"), _
    > LookIn:=xlValues, _
    > LookAt:=xlWhole, _
    > SearchOrder:=xlByRows, _
    > SearchDirection:=xlNext, _
    > MatchCase:=False)
    > if not rng is nothing then
    > sAddr = rng.Address
    > Do
    > rng.Interior.ColorIndex = 42
    > set rng =Range("A1:R33").FindNext(rng)
    > loop until rng.Addr = sAddr
    > End if
    > End Sub
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    > <anonymous@discussions.microsoft.com> wrote in message
    > news:17e901c4ffe0$ce692150$a601280a@phx.gbl...
    > > Greeting,
    > > I am developing something for a user.
    > > i have 2 cascading combo that corrosponds with data on the
    > > sheet. the user wants the combo to change the color on the
    > > data on the sheet when she selects something from the
    > > combo box.
    > > I have achieved this. however... the find next part of the
    > > code goes through the range what seems like 50 times be
    > > for it stops causing a delay of about 3-5 seconds.
    > > meanwhile the now colored cells just flicker as the code
    > > loops through the range over and over. I have tried
    > > several thing but nothing works. i'm stumped. How do i
    > > have the code go through the range only once.
    > > heres the code i have so far....
    > > it is the for next loop that is doing it.
    > > Private Sub ComboBox2_Change()
    > > Dim c As String
    > > Dim rng As Range
    > > Dim cell As Range
    > > c = Me.ComboBox2.Value
    > > Set rng = Range("A1:R33")
    > > rng.Interior.ColorIndex = xlNone
    > > If c = "" Then
    > > rng.Interior.ColorIndex = xlNone
    > > Else
    > > Cells.Find(What:=c, _
    > > After:=Range("A1"), _
    > > LookIn:=xlFormulas, _
    > > LookAt:=xlPart, _
    > > SearchOrder:=xlByRows, _
    > > SearchDirection:=xlNext, _
    > > MatchCase:=False).Activate
    > > ActiveCell.Interior.ColorIndex = 42
    > > If ActiveCell.Interior.ColorIndex = 42 Then
    > > For Each cell In rng
    > > Cells.FindNext(After:=ActiveCell).Activate
    > > ActiveCell.Interior.ColorIndex = 42
    > > Next
    > > End If
    > > End If
    > > End Sub
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1