Code not working

[Jayliam]

This code is supposed to lookup the value in textbox1 and enter the other textbox data in the associated row. It was working and now it only enters the data in the last row found.

Can someone please look at my code and tell me what I am doing wrong?

Option Explicit

Private Sub CommandButton2_Click()
Dim m As Variant
Dim iRow As Long
Dim ws As Worksheet
Dim FindString As String
Dim Rng As Range
Set ws = Worksheets("DATALOG")
'Dim n As Long
'n = ws.Range("A" & Application.Rows.Count).End(xlUp).Row
'ws.Range("A" & n + 1).Value = Val(Me.TextBox1.Value)

    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row
   
    If Len(Me.TextBox1.Value) > 0 Then
           
        m = Application.Match(Val(Me.TextBox1.Value), ws.Range("A:A"), False)
        If Not IsError(m) Then
        m = CLng(m)
          
            With ws
               
                 .Cells(iRow, 18).Value = Me.TextBox18.Value
                 .Cells(iRow, 19).Value = Me.TextBox19.Value
                 .Cells(iRow, 20).Value = Me.TextBox20.Value
                 .Cells(iRow, 21).Value = Me.TextBox21.Value
                 .Cells(iRow, 22).Value = Me.TextBox22.Value
                 .Cells(iRow, 23).Value = Me.TextBox23.Value
                 .Cells(iRow, 24).Value = Me.TextBox24.Value
                 .Cells(iRow, 25).Value = Me.TextBox25.Value
                 .Cells(iRow, 26).Value = Me.TextBox26.Value
                 .Cells(iRow, 27).Value = Me.TextBox27.Value
                 .Cells(iRow, 28).Value = Me.TextBox28.Value
                 .Cells(iRow, 29).Value = Me.TextBox29.Value
                 .Cells(iRow, 30).Value = Me.TextBox30.Value
                 .Cells(iRow, 31).Value = Me.TextBox31.Value
                 .Cells(iRow, 32).Value = Me.TextBox32.Value
                 .Cells(iRow, 33).Value = Me.TextBox33.Value
                 .Cells(iRow, 34).Value = Me.TextBox34.Value
                 .Cells(iRow, 35).Value = Me.TextBox35.Value
                 .Cells(iRow, 36).Value = Me.TextBox36.Value
                 .Cells(iRow, 37).Value = Me.TextBox37.Value
                 .Cells(iRow, 38).Value = Me.TextBox38.Value
                 .Cells(iRow, 39).Value = Me.TextBox39.Value
                 .Cells(iRow, 40).Value = Me.TextBox40.Value
                 .Cells(iRow, 41).Value = Me.TextBox41.Value
                 .Cells(iRow, 42).Value = Me.TextBox42.Value
                 .Cells(iRow, 43).Value = Me.TextBox43.Value
                 .Cells(iRow, 44).Value = Me.TextBox44.Value
                 .Cells(iRow, 45).Value = Me.TextBox45.Value
                 .Cells(iRow, 46).Value = Me.TextBox46.Value
                 .Cells(iRow, 47).Value = Me.TextBox47.Value
                 .Cells(iRow, 48).Value = Me.TextBox48.Value
                 .Cells(iRow, 49).Value = Me.TextBox49.Value
                 .Cells(iRow, 50).Value = Me.TextBox50.Value
                 .Cells(iRow, 51).Value = Me.TextBox51.Value
                 .Cells(iRow, 52).Value = Me.TextBox52.Value
                 .Cells(iRow, 53).Value = Me.TextBox53.Value
                 .Cells(iRow, 54).Value = Me.TextBox54.Value
                 .Cells(iRow, 55).Value = Me.TextBox55.Value
                 .Cells(iRow, 56).Value = Me.TextBox56.Value
                 .Cells(iRow, 57).Value = Me.TextBox57.Value
                 .Cells(iRow, 58).Value = Me.TextBox58.Value
                 .Cells(iRow, 59).Value = Me.TextBox59.Value
                 .Cells(iRow, 60).Value = Me.TextBox60.Value
                 .Cells(iRow, 61).Value = Me.TextBox61.Value
                 .Cells(iRow, 62).Value = Me.TextBox62.Value
                 .Cells(iRow, 63).Value = Me.TextBox63.Value
                 .Cells(iRow, 64).Value = Me.TextBox64.Value
                 .Cells(iRow, 65).Value = Me.TextBox65.Value
                 .Cells(iRow, 66).Value = Me.TextBox66.Value
                 .Cells(iRow, 67).Value = Me.TextBox67.Value
                 .Cells(iRow, 68).Value = Me.TextBox68.Value
                 .Cells(iRow, 69).Value = Me.TextBox69.Value
                 .Cells(iRow, 70).Value = Me.TextBox70.Value
                 .Cells(iRow, 71).Value = Me.TextBox71.Value
                 .Cells(iRow, 72).Value = Me.TextBox72.Value
                 .Cells(iRow, 73).Value = Me.TextBox73.Value
                 .Cells(iRow, 74).Value = Me.TextBox74.Value
                 .Cells(iRow, 75).Value = Me.TextBox75.Value
                 .Cells(iRow, 76).Value = Me.TextBox76.Value
                                         
            End With
           
            Else
                 MsgBox "SALE ORDER: " & Me.TextBox1 & Chr(10) & "Record Not Found", 48, "Not Found"
                 Me.TextBox1.SetFocus
        End If

End If
   
End Sub
Private Sub CommandButton3_Click()
  Unload Me
End Sub

 

[dmt32]

Hi
untested but see if this update to your code does what you want

Private Sub CommandButton2_Click()
    Dim c           As Long
    Dim iRow        As Variant, FindString As Variant
    Dim ws          As Worksheet
   
    Set ws = ThisWorkbook.Worksheets("DATALOG")
   
    FindString = Me.TextBox1.Value
    If Len(FindString) = 0 Then Exit Sub
   
    'search existing record
    iRow = Application.Match(Val(FindString), ws.Range("A:A"), False)
    If Not IsError(iRow) Then
   
    'update record
    For c = 18 To 76
        ws.Cells(CLng(iRow), c).Value = Me.Controls("TextBox" & c).Value
    Next
   
    Else
   
        MsgBox "SALE ORDER: " & FindString & Chr(10) & "Record Not Found", 48, "Not Found"
        Me.TextBox1.SetFocus
   
    End If


End Sub

Dave

 

[Jayliam]

Hi
untested but see if this update to your code does what you want

Private Sub CommandButton2_Click()
    Dim c           As Long
    Dim iRow        As Variant, FindString As Variant
    Dim ws          As Worksheet
  
    Set ws = ThisWorkbook.Worksheets("DATALOG")
  
    FindString = Me.TextBox1.Value
    If Len(FindString) = 0 Then Exit Sub
  
    'search existing record
    iRow = Application.Match(Val(FindString), ws.Range("A:A"), False)
    If Not IsError(iRow) Then
  
    'update record
    For c = 18 To 76
        ws.Cells(CLng(iRow), c).Value = Me.Controls("TextBox" & c).Value
    Next
  
    Else
  
        MsgBox "SALE ORDER: " & FindString & Chr(10) & "Record Not Found", 48, "Not Found"
        Me.TextBox1.SetFocus
  
    End If


End Sub

Dave

WORKS!!!

That's a lot easier. Your a life saver Dave. Thanks

 

[dmt32]

WORKS!!!

That's a lot easier. Your a life saver Dave. Thanks

most welcome & appreciate your feedback

Dave