Results 1 to 2 of 2

VBA to Search and Replace??

Threaded View

  1. #1
    Registered User
    Join Date
    06-24-2011
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    VBA to Search and Replace??

    Ive created a in/out time time clock using "Userform"
    it works very well, but it's populating my spread sheet row by row. One employee may punch in at 8am and another at 2pm.
    I need a code so when the 8am employee punches "OUT", the Out punch data populates in the original row (instead of creating a NEW row)

    here is the code i have so far

    Private Sub CommandButton1_Click() 
    Dim iRow As Long 
    Dim ws As Worksheet 
    Set ws = Worksheets("sheet1") 
    
    'find first empty row in database 
    iRow = ws.Cells(Rows.Count, 1) _ 
      .End(xlUp).Offset(1, 0).Row 
    
    'check for a part number 
    If Trim(Me.TextBox1.Value) = "" Then 
      Me.TextBox1.SetFocus 
      MsgBox "Please enter Required Information" 
      Exit Sub 
    End If 
    If Trim(Me.TextBox2.Value) = "" Then 
      Me.TextBox2.SetFocus 
      MsgBox "Please enter Required Information" 
      Exit Sub 
    End If 
    
    'copy the data to the database 
    ws.Cells(iRow, 1).Value = Me.TextBox1.Value 
    ws.Cells(iRow, 2).Value = Me.TextBox2.Value 
    
    ws.Cells(iRow, 3).Value = Date 
    ws.Cells(iRow, 4).Value = Time() 
    
    'clear the data 
    Me.TextBox1.Value = "" 
    Me.TextBox2.Value = "" 
    
    
    
    
    
    End Sub 
    
    Private Sub CommandButton2_Click() 
    Dim iRow As Long 
    Dim ws As Worksheet 
    Set ws = Worksheets("sheet1") 
    
    
    
    'find first empty row in database 
    iRow = ws.Cells(Rows.Count, 1) _ 
      .End(xlUp).Offset(0).Row 
    
    
    If Trim(Me.TextBox1.Value) = "" Then 
      Me.TextBox1.SetFocus 
      MsgBox "Please enter Required Information" 
      Exit Sub 
    End If 
    If Trim(Me.TextBox2.Value) = "" Then 
      Me.TextBox2.SetFocus 
      MsgBox "Please enter Required Information" 
      Exit Sub 
    End If 
    
    'clear the data 
    Me.TextBox1.Value = "" 
    Me.TextBox2.Value = "" 
    
    
    ws.Cells(iRow, 5).Value = Time() 
    End Sub
    Attached Files Attached Files
    Last edited by riosaaron; 06-24-2011 at 01:22 PM. Reason: Changed PHP Tags to Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1