My intention was to separate the generated data into different sheets by year, but obvioulsy I have a conflict between the IF year section and the array/loop section. I really have no idea where to go from here. Anyone have any ideas please.
The array/loop section was originally written to work with only one sheet, my logic didn't quite pan out.
The array/loop section was originally written to work with only one sheet, my logic didn't quite pan out.
Code:
Private Sub EnterPrint_cmd_Click()
Dim iRow As Long
Dim ws13 As Worksheet
Dim ws14 As Worksheet
Dim ws15 As Worksheet
Dim ws16 As Worksheet
Dim vws As Worksheet
Set ws13 = Worksheets("SampleLog 2013")
Set ws14 = Worksheets("SampleLog 2014")
Set ws15 = Worksheets("SampleLog 2015")
Set ws16 = Worksheets("SampleLog 2016")
Dim txtSamp, i, j, k
Dim ts As Date
ts = Now
Application.ScreenUpdating = False
'find year and first empty row in database
If Year(ts) = 2013 Then
iRow = ws13.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
Set vws = ws13
ElseIf Year(ts) = 2014 Then
iRow = ws14.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
Set vws = ws14
ElseIf Year(ts) = 2015 Then
iRow = ws15.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
Set vws = ws15
ElseIf Year(ts) = 2016 Then
iRow = ws16.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
Set vws = ws16
ElseIf (Year(ts) > 2016) Then
MsgBox "Year out of range"
End If
'check for Person
If Trim(Me.txtPerson.Value) = "" Then
Me.txtPerson.SetFocus
MsgBox "Please enter Person"
Exit Sub
End If
'check for a Sample Id
If Trim(Me.txtSample1.Value) = "" Then
Me.txtSample1.SetFocus
MsgBox "Please enter a Sample Id"
Exit Sub
End If
'Create array for textboxes
txtSamp = Array(txtSample1, txtSample2, txtSample3, txtSample4, txtSample5, txtSample6, txtSample7, _
txtSample8, txtSample9, txtSample10, txtSample11, txtSample12, txtSample13, txtSample14)
For i = 0 To 13
If txtSamp(i).Value <> "" Then 'Test each tb for value
vws.Cells(iRow + i, 1) = Left(txtSamp(i), 13) 'Post value
End If
Next
For j = 0 To 13
If vws.Cells(iRow + j, 1) <> "" Then 'Test if value entered in range col A
vws.Cells(iRow + j, 2) = ts 'Date/time stamp
End If
Next................