CODEWITHSUNDEEP
excelvbafor-loopuserformcommandbutton
Welshdpm
Welshdpm

Reputation: 49

Userform Command Button Next (Matching Criteria)

I'm not an experienced coder, so please excuse my ignorance. I have written a piece of code that cycles through responses from an online request form, where the results are saved to a spreadsheet.

There is a couple of problems I have, the first being that the Row Count Excludes the first row, if I add 1 to the result (which is correct it's fine!!!)

When I run the form it brings up the first record based on the criteria Not x = "Yes", however when I Click Next, it will cycle to the next row, and stop. If I add 1 to the Row Count it goes to the last row.

Private Sub UserForm_Initialize()

Call SetVariables

Dim Count As Long
Dim ReqRow As Long

    For Count = 2 To LRow
        If Not xRequest.Range("AF" & Count).Value = "Yes" Then
            Me.TB_Requester = xRequest.Range("F" & Count).Value
            Me.TB_Email = xRequest.Range("D" & Count)
            
            ReqRow = Count
            Exit For
        End If
    Next Count

'These are just recording the Row and Count 
'Me.TB_PropAction = ReqRow
'Me.TB_UsageScore = LRow

End Sub

Private Sub CmdB_Next_Click()

Call SetVariables

Dim Count As Long
Dim Record As Long

With xRequest
If Record = 0 Then Record = 1

    For Count = (Record + 1) To LRow Step 1
        If Not .Range("AF" & Count).Value = "Yes" Then
            Me.TB_Requester = .Range("F" & Count).Value
            Me.TB_Email = .Range("D" & Count)
            
            ReqRow = Count
        End If
    Next Count
    
    If (Count - 1) = LRow Then
        MsgBox "End of Component Submissions"
    End If
    
End With

Me.TB_PropAction = ReqRow

End Sub

Can anyone advise where I have gone wrong, I only have 6 rows in the spreadsheet, and it should cycle through 3 Requests, but no matter what I do I only get 2 (Rows 3 & 4 or Rows 3 & 6)

Upvotes: 0

Views: 298

Answers (1)

basodre
basodre

Reputation: 5770

It's a bit difficult to follow your code because some of it is being called elsewhere and setting variables used within these modules. I created a generic block of code to find the next value that is not a yes. I made some assumptions and I tried to clear them up in the comments to the code.

Basically, I have a column with values over which we are looping, looking for a value that is not Yes. As far as what the "current" record is, I'm assuming that it's the selected cell in the spreadsheet. If it's something different that is stored elsewhere, it shouldn't be too hard to change the logic to support that. See if the code below puts you in the right direction, or if you need additional help.

Sub Test()
    Dim looper As Long
    Dim lastRow As Long
    
    lastRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    
    'Loop through this range from the 'Current' row
    'until we find the next desired value.
    'Note: I can't really tell how you store the current row.
    'Are you actually using selected cell as a record pointer,
    'or is there a form-level variable holding that information?
    
    looper = Selection.Row
    
    Do
        looper = looper + 1
        If Sheets("Data").Range("A" & looper).Value <> "Yes" Then
            Exit Do
        End If
    Loop While looper <= lastRow

    'If the looping variable is greater than the last row
    'then we are outside of the used range.
    'We can cleanup and exit
    If looper > lastRow Then
        MsgBox "Finished"
        Me.TextBox1.Value = ""
        Me.TextBox2.Value = ""
        Exit Sub
    End If
    
    'Populate controls with found value
    Me.TextBox1.Value = Sheets("Data").Range("B" & looper).Value
    Me.TextBox2.Value = Sheets("Data").Range("C" & looper).Value
    
    'Move the record pointer to this found row
    Sheets("Data").Range("A" & looper).Select
    
End Sub

Upvotes: 1

Related Questions