Userform FindNext Variable Problem...

[Brujah]

Hi all,
I am looking for a little advice with some userform code, (and sorry in advance for the length of this post....) The userform is basically to be used to search through a range of projects using findnext/findprevious on spinbutton spinup/spindown events, and when the user locates one of theirs, they update the record via the textboxes on the userform using a command click event.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
This all works and is great… however… my issue is that I would really like the user to be able to continue to update records from the point of their last search…but I can’t seem to get the Rng variable of the FindNext/FindPrevious search to be maintained through the command click event.
<o></o>
The Rng variable is declared as global (at top of sheet) outside of any functions or events, and I’ve tried a couple of different ways, such as holding the value in a textbox on the userform, but I guess I must be doing something wrong because when I hit the spinup/down(findprevious version) button to try again after updating the record, I keep getting a run-time 5 error on this line of code:<o></o>

<o></o>
Set Rng = Sheets("reference").Columns(2).FindNext(Rng)<o></o>

When I hover the mouse over the variable it is showing as being blank again… please help?!!!<o></o>
<o></o>
I have posted an abbreviated version of the userform coding I’m using (there is still a lot of it though, sorry). I would be forever grateful if anyone would be able to give me a pointer in the right direction!!!!<o></o>
<o>

Dim Rng As Range
Dim countl As Long
Dim Found1 As Boolean

</o>
<o>

Private Sub cmdAdd_Click()
 Dim FindString As String
    Dim Rng1 As Range
        FindString = TxtRecPart.Value
    If Trim(FindString) <> "" Then
        With Sheets("Data").Range("lstProject")
            Set Rng1 = .Find(What:=FindString, _
                            After:=.Cells(1), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False)
            If Not Rng1 Is Nothing Then
             If TxtRecPart <> "" Then
            Rng1.Offset(0, 3).Value = TxtUpdate.Value
            Rng1.Offset(0, 4).Value = TxtOther.Value
            Rng1.Offset(0, 6).Value = CDate(Now())
            Rng1.Offset(0, 6).NumberFormat = "dd-mm-yy"
            Rng1.Offset(0, 12).Value = TxtProgramme.Value
            Rng1.Offset(0, 13).Value = TxtStrategy.Value</o:p>
<o:p>         Else
            End If
            Else
            MsgBox "Error"
            End If
            End With
    End If
TxtUpdate = ""
TxtOther = ""
End If</o:p>
<o:p>End Sub

</o>
<o></o>
<o>

Private Sub CmbProjectNo_Change()
    Set Rng = Range("B1")
    Found1 = False
    TxtProjectTitle.Value = ""
        If Found1 = False Then
        Set Rng = Sheets("reference").Columns(2).Find(CmbProjectNo.Value, Rng, xlValues, xlWhole, xlByRows)
        Found1 = True
    Else
        Set Rng = Sheets("reference").Columns(2).FindNext(Rng)
    End If
 
    If Not Rng Is Nothing Then
TxtProjectNo.Value = Rng.Value
TxtProgramme.Value = Rng.Offset(0, 1).Value
TxtAccount1.Value = Rng.Offset(0, 2).Value
TxtAccount2.Value = Rng.Offset(0, 3).Value
    Else
        MsgBox "Not found"
 
    End If
TxtTarget.Value = Format(TxtTarget.Value, "dd/mm/yyyy")
countl = Application.WorksheetFunction.CountIf(Sheets("reference").Columns(2), CmbProjectNo.Value)
TxtRecords.Value = Count</o:p>
<o:p>SpinButton1.Value = 1
txtRow.Text = SpinButton1.Value
End Sub

</o>
<o>

Sub SpinButton1_SpinDown()
    If Found1 = False Then
        Set Rng = Sheets("reference").Columns(2).Find(CmbProjectNo.Value, Rng, xlValues, xlWhole, xlByRows)
        Found1 = True
    Else
        Set Rng = Sheets("reference").Columns(2).FindPrevious(Rng)
    End If</o:p>
<o:p> If Not Rng Is Nothing Then
TxtProjectNo.Value = Rng.Value
TxtProgramme.Value = Rng.Offset(0, 1).Value
TxtAccount1.Value = Rng.Offset(0, 2).Value
TxtAccount2.Value = Rng.Offset(0, 3).Value
   Else
        MsgBox "Not found"
 
    End If
 
TxtTarget.Value = Format(TxtTarget.Value, "dd/mm/yyyy")
countl = Application.WorksheetFunction.CountIf(Sheets("reference").Columns(2), CmbProjectNo.Value)
TxtRecords.Value = countl
End Sub

</o>
<o>

Sub SpinButton1_SpinUp()
     If Found1 = False Then
        Set Rng = Sheets("reference").Columns(2).Find(CmbProjectNo.Value, Rng, xlValues, xlWhole, xlByRows)
        Found1 = True
    Else
        Set Rng = Sheets("reference").Columns(2).FindNext(Rng)
    End If
    If Not Rng Is Nothing Then
TxtProjectNo.Value = Rng.Value
TxtProgramme.Value = Rng.Offset(0, 1).Value
TxtAccount1.Value = Rng.Offset(0, 2).Value
TxtAccount2.Value = Rng.Offset(0, 3).Value
    Else
        MsgBox "Not found"
 
    End If
TxtTarget.Value = Format(TxtTarget.Value, "dd/mm/yyyy")
countl = Application.WorksheetFunction.CountIf(Sheets("reference").Columns(2), CmbProjectNo.Value)
TxtRecords.Value = countl</o:p>
<o:p>End Sub

</o>
Brujah<o></o>

 

[AlphaFrog]

It looks like Found1 is being set to True no matter if a match was found or not the first time.

     If Found1 = False Then
[COLOR="Red"]        Set Rng = Sheets("reference").Columns(2).Find(CmbProjectNo.Value, Rng, xlValues, xlWhole, xlByRows)
        Found1 = True[/COLOR]
    Else
        Set Rng = Sheets("reference").Columns(2).FindNext(Rng)
    End If

Try this...

     If [COLOR="red"]Rng Is Nothing[/COLOR] Then
        Set Rng = Sheets("reference").Columns(2).Find(CmbProjectNo.Value, Rng, xlValues, xlWhole, xlByRows)
    Else
        Set Rng = Sheets("reference").Columns(2).FindNext(Rng)
    End If

 

[Brujah]

Hi AlphaFrog, thank you for such a quick response, gave your code a go, but no joy, it's still bugging out at the same point, the Rng variable is still coming up as empty after the click update... willing to try just about anything to get this to run, so all idea welcome!!!

 

[Brujah]

would happily attach a sample file for anyone to to have a look at, but cant work out how as my posting rules box says
'You may not post attachments'

 

[Brujah]

Hi again, just thought I'd update, I've solved it, maybe in an untraditional, slightly convoluted manner, but hey... it seems to work

I have added a couple of things, the first is a lonely text box on the userform that only updates during the clickevent with the spinup/down value. Then I added a line into the click event that calls the ProjectNo_change event, which i have altered slightly, it now has a condition at the start that references the lonley textbox. If said box is empty, then carry on as normal, if not then go to a new procedure which basically runs the spin up event on a counter based on the figure in lonely text box!!! as i said... convoluted... but it works!!! no more run time errors!!! YAY!!!!!

Thank you all for looking though, and if anyone does have a better way ropund the problem, I'm still more than happy to learn!!!! Every days a school day!!!

Brujah