Posting Excel userform data to multiple rows matching criteria-CodePudding

I have created a userform in Excel with checkboxes, that posts a date to a database. The dates are posted in the row matching the ID (an ordernumber) that's put into the userform.

My problem by using "lastrow", is that it only posts to the first row found with the correct ordernumber, and my very limited skills in VBA are holding me back from figuring out how i should re-write this code.

I managed so far to put this together, and is working fine besides my problem that it doesn't find all the instances of an ordernumber.

Private Sub CommandButton2_Click()
Dim LastRow As Long
Dim IDnum As String
Dim rngidnum As Range
Dim ws As Worksheet

Set ws = Worksheets("Data")
With ws
LastRow = .Cells(.Rows.Count, "AB").End(xlUp).Row
IDnum = TextBox1.Value
Set rngidnum = .Range("AB1:AB" & LastRow).Find(IDnum, .Range("AB" & LastRow))
End With
'Tjek om Ordrenummer findes
If rngidnum Is Nothing Then MsgBox "Order Number not found": Exit Sub

With rngidnum
If CheckBox1.Value = True Then
.Offset(0, 69).Value = Date
End If
If CheckBox2.Value = True Then
.Offset(0, 70).Value = Date
End If
If CheckBox3.Value = True Then
.Offset(0, 71).Value = Date
End If
If CheckBox4.Value = True Then
.Offset(0, 72).Value = Date
End If
If CheckBox5.Value = True Then
.Offset(0, 73).Value = Date
End If
If CheckBox6.Value = True Then
.Offset(0, 74).Value = Date
End If
If CheckBox7.Value = True Then
.Offset(0, 75).Value = Date
End If
End With

Unload UserForm1

End Sub
Private Sub CommandButton1_Click()
Unload UserForm1
End Sub


Private Sub Frame4_Click()

End Sub

Private Sub UserForm_Initialize()
TextBox1.Value = Sheets("Form").Range("C3").Value
End Sub

I hope someone can help me figure out how the code can find and post the dates to all the corresponding rows.

Thanks in advance!

CodePudding user response：

If you want to put the values in several rows, I suggest to use a For-loop like this

For i = 1 To LastRow
    If ws.Range("AB" & i).Value = IDnum Then
        Set rngidnum = ws.Range("AB" & i)
        With rngidnum
            'Code to put dates in
        End With
    End If
Next i

CodePudding user response：

This uses .FindNext to loop through all matches.

I changed some of your With statements because I would have needed to nest them and I don't like doing that.

Private Sub CommandButton2_Click()
Dim LastRow As Long
Dim IDnum As String
Dim rngidnum As Range
Dim firstrng As Range
Dim ws As Worksheet

Set ws = Worksheets("Data")

LastRow = ws.Cells(Rows.Count, "AB").End(xlUp).Row

IDnum = TextBox1.Value
'Tjek om Ordrenummer findes
With ws.Range("AB1:AB" & LastRow)
    Set rngidnum = .Find(IDnum, ws.Range("AB" & LastRow))
    If rngidnum Is Nothing Then MsgBox "Order Number not found": Exit Sub
    Do
        If firstrng Is Nothing Then
            Set firstrng = rngidnum 'Avoiding infinite loop
        Else
            If firstrng = rngidnum Then Exit Do 'Avoiding infinite loop
        End If
    
        If CheckBox1.Value = True Then
            rngidnum.Offset(0, 69).Value = Date
        End If
        If CheckBox2.Value = True Then
            rngidnum.Offset(0, 70).Value = Date
        End If
        If CheckBox3.Value = True Then
            rngidnum.Offset(0, 71).Value = Date
        End If
        If CheckBox4.Value = True Then
            rngidnum.Offset(0, 72).Value = Date
        End If
        If CheckBox5.Value = True Then
            rngidnum.Offset(0, 73).Value = Date
        End If
        If CheckBox6.Value = True Then
            rngidnum.Offset(0, 74).Value = Date
        End If
        If CheckBox7.Value = True Then
            rngidnum.Offset(0, 75).Value = Date
        End If
        Set rngidnum = .FindNext(rngidnum)
    Loop
End With

Unload UserForm1

End Sub

CodePudding user response：

To use a loop can be a solution.

    Private Sub CommandButton2_Click()

    Dim LastRow As Long
    Dim IDnum As String
    Dim rngidnum As Range
    Dim ws As Worksheet
    Dim i As Long
    Dim NotFound As Boolean
    
    Set ws = Worksheets("Data")
    LastRow = ws.Cells(.Rows.Count, "AB").End(xlUp).Row
    IDnum = TextBox1.Value
    NotFound = True
        
        For i = 1 To LastRow
            If ws.Range("AB" & i).Value = IDnum Then
                    Set rngidnum = ws.Range("AB" & i)
                NotFound = False
                
                With rngidnum
                    If CheckBox1.Value = True Then .Offset(0, 69).Value = Date
                    If CheckBox2.Value = True Then .Offset(0, 70).Value = Date
                    If CheckBox3.Value = True Then .Offset(0, 71).Value = Date
                    If CheckBox4.Value = True Then .Offset(0, 72).Value = Date
                    If CheckBox5.Value = True Then .Offset(0, 73).Value = Date
                    If CheckBox6.Value = True Then .Offset(0, 74).Value = Date
                    If CheckBox7.Value = True Then .Offset(0, 75).Value = Date
                End With
                
            End If
        Next i
        
        

        Set rngidnum = Nothing
        Set ws = Nothing
        
    'Tjek om Ordrenummer findes
    If NotFound Then MsgBox "Order Number not found": Exit Sub
    
    Unload UserForm1

    End Sub

CodePudding user response：

You can loop over the checkboxes.

For n = 1 To 7
   If Me.Controls("Checkbox" & n).Value = True Then
       rngidnum.Offset(0, n   68).Value = Date
   End If
Next