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:
<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:
<tbody>
</tbody>
The code I currently have is:
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!
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:
Code:
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!