Transfer Data to Specific Rows From User Form Inputs

[tony2177]

I have a spreadsheet that has two command buttons on it. Each one brings up a different user form. The first user form will input 6 columns of data. See the schematic below:

	A	B	C	D	E	F	G	H	I
1	x	x	x	x	x	x
2	x	x	x	x	x	x
3	x	x	x	x	x	x
4	x	x	x	x	x	x
5	x	x	x	x	x	x
6	x	x	x	x	x	x

<tbody>
</tbody>

After a couple of weeks progress at our plant, the operator will open the second user form, which will input the final three columns (G, H, and I). The problem is that the operator needs to be able to link those three columns of data to a specific row. I'm not sure how to do that. For example, if the operator needs to put the data into rows 2 and 5, the spreadsheet would look like:

	A	B	C	D	E	F	G	H	I
1	x	x	x	x	x	x
2	x	x	x	x	x	x	x	x	x
3	x	x	x	x	x	x
4	x	x	x	x	x	x
5	x	x	x	x	x	x	x	x	x
6	x	x	x	x	x	x

<tbody>
</tbody>

The code I currently have is:

Private Sub cmdSubmit_Click()


Dim halfRow As Long
Dim rowFound As Range
Set rowFound = Range("B:B").Find(cmbID.Value, LookIn:=xlValues, LookAt:=xlWhole)


'Activate Sheet 1, Work Roll Log
Sheet1.Activate


'determine where remainder of data should be entered
If rowFound Is Nothing Then
    halfRow = Range("B" & Rows.Count).End(xlUp).Row + 1
Else
    halfRow = rowFound.Row
End If


'check for an ID
If Trim(Me.cmbID.Value) = "" Then
  Me.cmbID.SetFocus
  MsgBox "Please enter missing WORK roll ID number."
  Exit Sub
End If


'check for a current footage
If Trim(Me.txtCurrentFootage.Value) = "" Then
  Me.txtCurrentFootage.SetFocus
  MsgBox "Please enter the CURRENT footage of the work roll."
  Exit Sub
End If


'transfer information
Cells(halfRow, 7).Value = txtCurrentFootage.Value
Cells(halfRow, 9).Value = txtDateOut.Value


'Range("H:H").Value = Range("G:G").Value - Range("F:F").Value
'Cells(halfRow, 8).Value = Range("H:H").Value


'clear the data
Me.cmbID.Value = ""
Me.txtCurrentFootage.Value = ""


'save workbook
ActiveWorkbook.Save


End Sub

The code above is starting to work, but there are two problems.

1. If the same product ID is used twice (say the value in column B is in rows 2 and 5), it always overwrites the first row (in this case, row 2). I would like it to write in the first empty G/H/I column set every time (first row 2, then row 5).

2. I would like the column H to be calculated via a formula (simply, G - F = H, not through the user forms). Not sure how to do that.

I'll be glad to attach the file if someone wants to see it, just not sure how. Thanks for the input!

 

[James006]

Hi,

Before getting into the coding of your UserForms ... just make sure you have a properly structured database ...

Indeed, a Unique ID is by by definition Unique ...and cannot be used twice ...