Update data via Userform Excel VBA

[Cakz Primz]

Guys,

I have data of ID number (cboSequence) in column B in Sheet1 formatting in number, such as 233, 1070 etc.
I have a userform to search based on ID number, so far the code is working
But when I am trying to edit and save the changes, it is not working.
Hope that someone can help to fix this problem.

Below the code of command button Save

Before I used this code:

Private Sub cb_Save_Click()
    
    answer = MsgBox("Are you sure want to save the record?", vbYesNo + vbQuestion, "save")
    
    Dim lRow As Long, i As Long
    lRow = Sheet1.Range("B5").CurrentRegion.Rows.Count
    
    For i = 5 To lRow 'data start from row # 5
    If Trim(Sheet1.Cells(i, 2)) = Trim(cboSequence.Text) Then 'cboSequence = ComboBox for ID number
            cell.Offset(i, 40).Value = Me.cboSubGroup.Value
            cell.Offset(i, 41).Value = Me.cboLocation.Value
    End If
    Next
 
End Sub

Private Sub cb_Save_Click()
    
    answer = MsgBox("Are you sure want to save the record?", vbYesNo + vbQuestion, "save")
    
    Dim lRow As Long, i As Long
    lRow = Sheet1.Range("B5").CurrentRegion.Rows.Count
    
    For i = 5 To lRow 'data start from row # 5
    If Trim(Sheet1.Cells(i, 2)) = Int(cboSequence.Value) Then
            cell.Offset(, 3).Value = Me.cboSubGroup.Value
            cell.Offset(, 4).Value = Me.cboLocation.Value
    End If
    Next
 
End Sub

Thank you
Prima
Indonesia

 

[Siddharth Rout]

I would recommend using Option Explicit. It will force you to declare objects and variables.

In your code what is cell?

Is this what you want? (Untested) I just quickly wrote this

Option Explicit

Private Sub cb_Save_Click()
    Dim lRow As Long, i As Long
    Dim answer As Variant
  
    answer = MsgBox("Are you sure want to save the record?", vbYesNo + vbQuestion, "save")
  
    If answer = vbYes Then
        With Sheet1
            lRow = .Range("B" & .Rows.Count).End(xlUp).Row
  
            For i = 5 To lRow
                If Trim(.Cells(i, 2).Value2) = Trim(cboSequence.Text) Then
                    .Cells(i, 2).Offset(, 40).Value = Me.cboSubGroup.Value
                    .Cells(i, 2).Offset(i, 41).Value = Me.cboLocation.Value
                  
                    '~~> Exit the loop. No point continuing the loop
                    Exit For
                End If
            Next i
        End With
    End If
End Sub

Or since you already know the columns then no need to use offset.

Option Explicit

Private Sub cb_Save_Click()
    Dim lRow As Long, i As Long
    Dim answer As Variant
  
    answer = MsgBox("Are you sure want to save the record?", vbYesNo + vbQuestion, "save")
  
    If answer = vbYes Then
        With Sheet1
            lRow = .Range("B" & .Rows.Count).End(xlUp).Row
  
            For i = 5 To lRow
                If Trim(.Range("B" & i).Value2) = Trim(cboSequence.Text) Then
                    .Range("AP" & i).Value = Me.cboSubGroup.Value
                    .Range("AQ" & i).Value = Me.cboLocation.Value
                  
                    '~~> Exit the loop. No point continuing the loop
                    Exit For
                End If
            Next i
        End With
    End If
End Sub

 

[Cakz Primz]

I would recommend using Option Explicit. It will force you to declare objects and variables.

In your code what is cell?

Is this what you want? (Untested) I just quickly wrote this

Option Explicit

Private Sub cb_Save_Click()
    Dim lRow As Long, i As Long
    Dim answer As Variant
 
    answer = MsgBox("Are you sure want to save the record?", vbYesNo + vbQuestion, "save")
 
    If answer = vbYes Then
        With Sheet1
            lRow = .Range("B" & .Rows.Count).End(xlUp).Row
 
            For i = 5 To lRow
                If Trim(.Cells(i, 2).Value2) = Trim(cboSequence.Text) Then
                    .Cells(i, 2).Offset(, 40).Value = Me.cboSubGroup.Value
                    .Cells(i, 2).Offset(i, 41).Value = Me.cboLocation.Value
                 
                    '~~> Exit the loop. No point continuing the loop
                    Exit For
                End If
            Next i
        End With
    End If
End Sub

Or since you already know the columns then no need to use offset.

Option Explicit

Private Sub cb_Save_Click()
    Dim lRow As Long, i As Long
    Dim answer As Variant
 
    answer = MsgBox("Are you sure want to save the record?", vbYesNo + vbQuestion, "save")
 
    If answer = vbYes Then
        With Sheet1
            lRow = .Range("B" & .Rows.Count).End(xlUp).Row
 
            For i = 5 To lRow
                If Trim(.Range("B" & i).Value2) = Trim(cboSequence.Text) Then
                    .Range("AP" & i).Value = Me.cboSubGroup.Value
                    .Range("AQ" & i).Value = Me.cboLocation.Value
                 
                    '~~> Exit the loop. No point continuing the loop
                    Exit For
                End If
            Next i
        End With
    End If
End Sub

Dear Siddarth Rout,

Thank you very much, for your help and assistance. Your code is working !!!
Problem solved.

Again, thank you very much
prima - Indonesia

 

[Siddharth Rout]

Dear Siddarth Rout,

Thank you very much, for your help and assistance. Your code is working !!!
Problem solved.

Again, thank you very much
prima - Indonesia

Thank you for sharing your feedback. Much appreciated.