Results 1 to 8 of 8
  • Thread Tools
  1. Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    FindNext (XP;SR3)

    I am having an issue with the Case vbNo where I am trying to find the next "c" in the rangename "R_Names". I can find the first item but if it is not what I'm looking for I want to select the "NO" button from the message box which will then bring be to the next "c" and etc..

    Var1 = ActiveCell
    Do Until IsEmpty(ActiveCell)
    With ThisWorkbook.Sheets("Unique_Users").Range("R_Names ")
    Set c = .Find(Var1, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False)

    If Not c Is Nothing Then

    intResult = MsgBox(c.Offset(0, -2).Value & Chr(13) & _
    c.Offset(0, -1).Value & Chr(13) & _
    c.Offset(0, 0).Value, vbYesNoCancel + vbQuestion)

    Select Case intResult
    Case vbYes
    ActiveCell.Offset(0, 2) = c.Offset(0, -2).Value
    ActiveCell.Offset(0, 3) = c.Offset(0, -1).Value
    ActiveCell.Offset(0, 4) = c.Offset(0, 0).Value
    Case vbNo
    Set c = .FindNextŠ
    Case vbCancel
    End
    End Select
    End If
    End With
    ActiveCell.Offset(1, 0).Select
    Var1 = ActiveCell
    Loop


    Any assistance would be appreciated.
    John

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: FindNext (XP;SR3)

    Could you post a sample workbook to test on?

  4. Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindNext (XP;SR3)

    Without the workbook to test on, I could not test the code below. See if it gets you any closer to what you want.

    <pre> Var1 = ActiveCell
    With ThisWorkbook.Sheets("Unique_Users").Range("R_Names ")
    Do Until IsEmpty(ActiveCell)
    Set c = .Find(Var1, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False)
    If Not c Is Nothing Then
    Do While True
    intResult = MsgBox(c.Offset(0, -2).Value & Chr(13) & _
    c.Offset(0, -1).Value & Chr(13) & _
    c.Offset(0, 0).Value, vbYesNoCancel + vbQuestion)
    Select Case intResult
    Case vbYes
    ActiveCell.Offset(0, 2) = c.Offset(0, -2).Value
    ActiveCell.Offset(0, 3) = c.Offset(0, -1).Value
    ActiveCell.Offset(0, 4) = c.Offset(0, 0).Value
    ActiveCell.Offset(1, 0).Select
    Var1 = ActiveCell
    Exit Do
    Case vbNo
    Set c = .FindNextŠ
    Case vbCancel
    End
    End Select
    Loop
    End If
    Loop
    End With
    </pre>

    Legare Coleman

  5. Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindNext (XP;SR3)

    Hans,

    Please find attached a file with an example of what I am trying to accomplish.

    Thanks,
    John

  6. Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindNext (XP;SR3)

    Legare,

    Your recommendation puts the code in a never ending loop. I just attached an example file.

    John

  7. Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindNext (XP;SR3)

    It runs fine on my system. Could you be more specific about what you did to get it into a loop?
    Legare Coleman

  8. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: FindNext (XP;SR3)

    Try this slightly extended version of Legare's code:

    Sub Test()
    Dim var1 As String
    Dim c As Range
    Dim intResult As Integer
    Dim r As Range
    var1 = ActiveCell
    With ThisWorkbook.Sheets("Unique_Users").Range("R_Names ")
    Do Until IsEmpty(ActiveCell)
    Set c = .Find(var1, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False)
    If Not c Is Nothing Then
    Set r = c
    Do
    intResult = MsgBox(c.Offset(0, -2).Value & Chr(13) & _
    c.Offset(0, -1).Value & Chr(13) & _
    c.Offset(0, 0).Value, vbYesNoCancel + vbQuestion)
    Select Case intResult
    Case vbYes
    ActiveCell.Offset(0, 2) = c.Offset(0, -2).Value
    ActiveCell.Offset(0, 3) = c.Offset(0, -1).Value
    ActiveCell.Offset(0, 4) = c.Offset(0, 0).Value
    ActiveCell.Offset(1, 0).Select
    var1 = ActiveCell
    Exit Do
    Case vbNo
    Set c = .FindNextŠ
    If c = r Then
    ActiveCell.Offset(1, 0).Select
    var1 = ActiveCell
    Exit Do
    End If
    Case vbCancel
    Exit Sub
    End Select
    Loop
    End If
    Loop
    End With
    End Sub

  9. Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: FindNext (XP;SR3)

    Hans/Legare,

    Thank you.

    John

Posting Permissions

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