Updating existing record in excel..

[Cruizer67]

How can I update an existing open record in excel using VB or macro..

For instance. clock in is recorded at one stage for a job.. (clock in includes.. Operator number and part number ) i would like to be able to go back and edit that input record and now add a clock out time as well as qty complete . I would like to be able to do this using search parameters such as part number and operator number so that correct job is bing closed . this would decrease the amount of inputs as well as make it easier to apply formulas to do some simple costing and charting.. I can clock in and out but information is stored on seperate lines...

any help on this?

thanks

 

[Smitty]

Welcome to the Board!

Can you post an example of what you've got now? (You can do that with Colo's HTML Makerl; the link's at the bottom of the page).

Smitty

 

[Cruizer67]

I'm on a company computer so I can't download anything. Sorry.. I could try later this evening at home.

As it stands .. there are two buttons per machine on a page.. one is for clock in which records the oper #, part # and setup time..included is an auto date and time input... the second button also records Oper #, part #, qty complete and cycle time and auto inputs date and time also. this ends up being two different rows of info . I would like to be able to go in and find a job that has been clocked in and add the clock out time and qty complete with out having to use two rows for the info.. this would make doing costing/and production reports a bit easier to figure out.. I'm a machinist/CNC Programmer trying to be a computer programmer/supervisor..

Even partial code helps as I have a fair understanding of how to do things..

thanks

 

[Smitty]

Using the following example:

Book1
	A	B	C	D	E	F
1	Job#	Oper#	Part#	QtyComplete	Clock-InTime	Clock-OutTime
2	123	24	345	200	1/17/2005-10:00:00AM	1/17/2005-11:15:20AM
3	456	12	567	150	1/17/2005-10:15:05AM	1/17/2005-11:22:18AM
Sheet1

The following Command button code will prompt you to enter a new Job #, Operator # & Part #, then add a start Date & Time. The second bit will prompt you for a Job #, find it, then prmopt you for a Quantity and add the end Date & Time.<<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN><SPAN style="color:#00007F">Explicit</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    <SPAN style="color:#007F00">'   Add a new job</SPAN>
        <SPAN style="color:#00007F">Dim</SPAN> LastRow<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Object</SPAN>
            <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet1").Range("A65536").End(xlUp)
        
        <SPAN style="color:#00007F">With</SPAN> LastRow
            .Offset(1, 0) = InputBox("Please enter a Job #", "Job #")
            .Offset(1, 1) = InputBox("Please enter an Operator #", "Operator #")
            .Offset(1, 2) = InputBox("Please enter a Part #", "Part #")
            .Offset(1, 4) =<SPAN style="color:#00007F">Date</SPAN> & " - " & Time
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> CommandButton2_Click()
    <SPAN style="color:#007F00">'   Update Clock-Out time</SPAN>
        <SPAN style="color:#00007F">Dim</SPAN> x<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
        <SPAN style="color:#00007F">Dim</SPAN> c<SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">Dim</SPAN> FirstAddress
        
        x = Application.InputBox("Please enter a job #", "Job # Search", Type:=1)
        
        <SPAN style="color:#007F00">'   Adjust range as necessary</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Worksheets(1).Range("A1:A500")
            <SPAN style="color:#00007F">Set</SPAN> c = .Find(x, LookIn:=xlValues)
            <SPAN style="color:#00007F">If</SPAN><SPAN style="color:#00007F">Not</SPAN> c<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN>
                FirstAddress = c.Address
                <SPAN style="color:#00007F">Do</SPAN>
                    <SPAN style="color:#007F00">'   Update Quantity Completed</SPAN>
                    c.Offset(, 3) = Application.InputBox("Please enter the Quantity Completed", _
                        "Quantity Completed", Type:=1)
                    <SPAN style="color:#007F00">'   Add Clock-Out time</SPAN>
                    c.Offset(, 5) =<SPAN style="color:#00007F">Date</SPAN> & " - " & Time
                <SPAN style="color:#00007F">Loop</SPAN><SPAN style="color:#00007F">While</SPAN><SPAN style="color:#00007F">Not</SPAN> c<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN> And c.Address<> FirstAddress
            <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

Smitty

 

[Cruizer67]

That works really well.. however.. it happens that there are multiple operation for one part which means there will be multiple clock is for the same job #. How would i get around this? by searching for the record that has no clock out time I assume.. ? It sure cut what I had down by a bunch.. here is that code that i have come with so far ... mind you i don't use job #'s but I get the idea........

This is for clock in:

Private Sub Add_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Akira_Input")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a operator number
If Trim(Me.Operator.Value) = "" Then
Me.Operator.SetFocus
MsgBox "Please enter an Operator Number"
Exit Sub
End If

'check for a part number
If Trim(Me.PartNumber.Value) = "" Then
Me.PartNumber.SetFocus
MsgBox "Please enter complete part number"
Exit Sub
End If


'check for setup time
If Trim(Me.Setup.Value) = "" Then
Me.Setup.SetFocus
MsgBox "Please Enter Setup Time"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Operator.Value
ws.Cells(iRow, 2).Value = Me.PartNumber.Value
ws.Cells(iRow, 4).Value = Me.Setup.Value
ws.Cells(iRow, 6).Value = "=Today()"
ws.Cells(iRow, 7).Value = Time

'clear the data
Me.Operator.Value = ""
Me.PartNumber.Value = ""
Me.Setup.Value = ""
Me.Operator.SetFocus


End Sub

Private Sub Cancel_Click()
Unload Me
End Sub

Private Sub Label11_Click()

End Sub



this is for clock out:

Private Sub Finish_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Akira_Input")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a operator number
If Trim(Me.Operator.Value) = "" Then
Me.Operator.SetFocus
MsgBox "Please enter an Operator Number"
Exit Sub
End If

'check for a part number
If Trim(Me.PartNumber.Value) = "" Then
Me.PartNumber.SetFocus
MsgBox "Please enter complete part number"
Exit Sub
End If


'check for setup time
If Trim(Me.Quantity.Value) = "" Then
Me.Quantity.SetFocus
MsgBox "Please Enter Quantity Complete"
Exit Sub
End If

'check for setup time
If Trim(Me.Cycletime.Value) = "" Then
Me.Cycletime.SetFocus
MsgBox "Please Enter Part Cycle time"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Operator.Value
ws.Cells(iRow, 2).Value = Me.PartNumber.Value
ws.Cells(iRow, 3).Value = Me.Quantity.Value
ws.Cells(iRow, 5).Value = Me.Cycletime.Value
ws.Cells(iRow, 6).Value = "=Today()"
ws.Cells(iRow, 8).Value = Time


'clear the data
Me.Operator.Value = ""
Me.PartNumber.Value = ""
Me.Quantity.Value = ""
Me.Cycletime.Value = ""
Me.Operator.SetFocus


End Sub

Private Sub Cancel_Click()
Unload Me
End Sub

Private Sub Label11_Click()

End Sub