Good Evening everyone.
Long story short.
I have made a UserForm in which I am able to populate the relevant data to the relevant 'Machine' data sheet
E.g. All work codes for number 1 are submitted to number 1s data sheet.
My current code finds the next empty row and populates.
I wish to change this to: finding the 4 digit work code that is entered into the Userform and populating against this number on the data sheet.
Numbers are 0000 to 9999 (in order) and are labelled as the SNOW. AC is the machine label.
I have tried a few ways through google and 'VBA for dummies' and I am yet to understand how to do it.
My current code:
Private Sub Submit_Click()
Dim iRow As Long
Dim ws As Worksheet
If cboAC.Value = "Z001" Then
Set ws = Worksheets("Z001")
ElseIf cboAC.Value = "Z002" Then
Set ws = Worksheets("Z002")
ElseIf cboAC.Value = "Z003" Then
Set ws = Worksheets("Z003")
ElseIf cboAC.Value = "Z004" Then
Set ws = Worksheets("Z004")
ElseIf cboAC.Value = "Z005" Then
Set ws = Worksheets("Z005")
ElseIf cboAC.Value = "Z006" Then
Set ws = Worksheets("Z006")
ElseIf cboAC.Value = "Z007" Then
Set ws = Worksheets("Z007")
ElseIf cboAC.Value = "Z008" Then
Set ws = Worksheets("Z008")
End If
ws.Activate
Dim Start
Set Start = ws.Range("A1")
'Start.End(xlDown).Offset(1).Select
Range("A1").End(xlDown).Offset(1).Select
'Cells("SNOW").value = txtsnow.value
ActiveCell.Value = txtSNOW.Value
Start.End(xlDown).Offset(RowOffset:=0, ColumnOffset:=3).Select
'Cells("INFO").value = txtINFO.value
ActiveCell.Value = txtINFO.Value
Start.End(xlDown).Offset(RowOffset:=0, ColumnOffset:=2).Select
'Cells("DATE").value = txtDATE.value
ActiveCell.Value = txtDATE.Value
Start.End(xlDown).Offset(RowOffset:=0, ColumnOffset:=4).Select
'Cells("SNIN").value = txtSNIN.value
ActiveCell.Value = txtSNIN.Value
Start.End(xlDown).Offset(RowOffset:=0, ColumnOffset:=5).Select
'Cells("SNOUT").value = txtSNOUT.value
ActiveCell.Value = txtSNOUT.Value
'check for a SNOW
If Trim(Me.txtSNOW.Value) = "" Then
Me.txtSNOW.SetFocus
MsgBox "Please enter SNOW details"
Exit Sub
End If
'clear the data
Me.txtSNOW.Value = ""
Me.txtDATE.Value = ""
Me.txtINFO.Value = ""
Me.txtSNIN.Value = ""
Me.txtSNOUT.Value = ""
Me.cboAC.Value = ""
Me.txtSNOW.SetFocus
End Sub
I made this from scratch by playing around for a couple of weeks, so it may not be the easiest to read. I'm not sure I could even elaborate further on every step.
I hope I have made my question clear and the formatting is correct.
This is my first post and I'm learning as I go.
Thank you in advance
T
Bookmarks