Excel compare input text to cell and get row-CodePudding

I am trying to take an input for a user text box and if the same as what is in column B insert the data from a different user text box2 and insert into column E.

I can get it to go in to the next empty row but I do not know how to do the search to find same and put it in that same row.

Any help is appreciated.

Private Sub CommandButton1_Click()

    If TextBox1.Value = "" Or TextBox2.Value = "" Then
        If MsgBox("data is not complete. do you want to continue?", vbQuestion   vbYesNo) <> vbYes Then
        Exit Sub
        End If
    End If
    
    
    Call find
    
    ActiveCell.Offset(0, 5) = TextBox1.Value
    ActiveCell.Offset(0, 1) = TextBox2.Value
    ActiveCell.Offset(0, 5) = Time
    ActiveCell.Offset(1, 0).Select
    
    Call resetForm
    Unload Me

End Sub

Sub resetForm()
    TextBox1.Value = ""
    TextBox2.Value = ""
    UserForm1.TextBox1.SetFocus
End Sub



Private Sub CommandButton2_Click()
    Unload Me
End Sub


Private Sub Label4_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Sub find()
    Dim compld As Range
    Set Compid = Range("B:B").find(what:=Range("TextBox1.Value").Value, _
                      LookIn:=xlValues, lookat:=xlWhole).Select
End Sub
 
Sub HighlightMatches()
    Application.ScreenUpdating = False
    
    'Declare variables
    Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long, bln As Boolean
       
       'Set up the count as the number of filled rows in the first column of Sheet1.
       iRowL = Cells(Rows.Count, 1).End(xlUp).Row
       
       'Cycle through all the cells in that column:
       For iRow = 1 To iRowL
          'For every cell that is not empty, search through the first column in each worksheet in the
          'workbook for a value that matches that cell value.

          If Not IsEmpty(Cells(iRow, 1)) Then
             For iSheet = ActiveSheet.Index   1 To Worksheets.Count
                bln = False
                var = Application.Match(Cells(iRow, 1).Value, Worksheets(iSheet).Columns(1), 0)
                
                'If you find a matching value, indicate success by setting bln to true and exit the loop;
                'otherwise, continue searching until you reach the end of the workbook.
                If Not IsError(var) Then
                   bln = True
                   Exit For
                End If
             Next iSheet
          End If
          
          'If you don't find a matching value, don't bold the value in the original list;
          'if you do find a value, bold it.
          If bln = False Then
             Cells(iRow, 1).Font.Bold = False
             If MsgBox("dno match foudn?", vbQuestion   vbYesNo) <> vbYes Then
          Exit Sub
             End If
             Else
             Cells(iRow, 1).Font.Bold = True
          End If
       Next iRow
    Application.ScreenUpdating = True
End Sub

CodePudding user response：

Something like this:

Private Sub CommandButton1_Click()

    Dim ws As Worksheet, id, v, m
    
    Set ws = ActiveSheet
    id = TextBox1.Value
    v = TextBox2.Value
    
    If Len(id) = 0 Then
        MsgBox "Id is required!", vbExclamation
        Exit Sub
    End If
    
    If TextBox2.Value = "" Then
        If MsgBox("data is not complete. do you want to continue?", _
                                     vbQuestion   vbYesNo) <> vbYes Then
            Exit Sub
        End If
    End If
    
    m = Application.Match(id, ws.Columns("B"), 0)       'try to match an existing row
    If IsError(m) Then                                  'no match?
        m = ws.Cells(ws.Rows.Count, "B").End(xlUp).row   1 'next empty cell
        ws.Cells(m, "B").Value = id                     'add the id to the empty cell
    End If
    
    With ws.Rows(m)
        .Columns("E").Value = v
        .Columns("F").Value = Time 'for example
    End With
    
    Call resetForm
    Unload Me

End Sub

CodePudding user response：

Private Sub CommandButton1_Click()

 

'check to see is all data is filled in

If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Or TextBox5.Value = "" Then
   MsgBox "YOU DID NOT FILL IN ALL THE INFO."
    
    Exit Sub
    
End If


Call Submit_Data

Call resetForm
Unload Me


End Sub
Sub resetForm()

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
UserForm1.TextBox1.SetFocus


End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub UserForm_Click()

End Sub

Sub Submit_Data()
Application.ScreenUpdating = False
 
Dim App As New Excel.Application
Dim wBook As Excel.Workbook
 
Dim FileName As String
 
Dim iRow As Long
 
FileName = ThisWorkbook.Path & "\test.xlsm"
 
'Check File Exist or Not
 
'If Dir(FileName) = "" Then
 
    'MsgBox "Database File is missing. Unable to proceed.", vbOKOnly vbCritical, "Error"
    
    'Exit Sub
 
'End If
 
Set wBook = App.Workbooks.Open(FileName)
 
App.Visible = False
 
If wBook.ReadOnly = True Then
    MsgBox "Database is in use. Please try after sometimes.", vbookonly   vbCritical, "error"
    
    Exit Sub
End If
 
'Transfer the Data
 
id = TextBox2.Value
 
With wBook.Sheets("test")
 
  Set ws = wBook.Sheets("test")
  
    m = Application.Match(id, ws.Columns("B"), 0)      'try to match an existing row
    
    If IsError(m) Then                                  'no match?
        
        iRow = .Range("A" & Application.Rows.Count).End(xlUp).Row   1
        
           .Range("A" & iRow).Value = TextBox1.Value 'Cell
 
         .Range("B" & iRow).Value = TextBox2.Value   'workorder number
 
         .Range("C" & iRow).Value = TextBox3.Value   'product number
 
        .Range("D" & iRow).Value = TextBox4.Value    'Work order quanity
 
        .Range("E" & iRow).Value = Date  'date
 
         .Range("F" & iRow).Value = Time   'time
 
        .Range("M" & iRow).Value = TextBox5.Value   'crew size
        
 
  Else
    
    MsgBox "JOB ALREADY CLOCKED IN!"
    
 
    Exit Sub
    
    End If
 
   End With
 
wBook.Close Savechanges:=True
 
App.Quit
 
Set App = Nothing
 
'Reset the form
 
Call resetForm
 
Application.ScreenUpdating = True
 
MsgBox "Data submitted successfully!"

        
        
      
End Su