Selected Answer
Here is the code I introduced to you yesterday, but applied to the setup you now published.
Option Explicit
Sub data_input()
Dim Ws_Output As Worksheet
Dim Next_Row As Long
Set Ws_Output = Worksheets("Data")
' using the With statement:
' the object specified at "With ..." is connected to each of the lines
' in the block (ends with "End With") by a leading period,
' e.g. .Cells(Next_Row, 1) refers to Ws_Output
' but Cells(Next_Row, 1), without leading period, refers to the ActiveSheet.
' Observe the necessary leading period in .Rows.Count
With Ws_Output
Next_Row = .Range("A" & .Rows.Count).End(xlUp).Row + 1
' all of the named ranges are on the ActiveSheet (no leading period)
.Cells(Next_Row, 1).Value = Range("employee_id").Value
.Cells(Next_Row, 2).Value = Range("employee_name").Value
.Cells(Next_Row, 9).Value = Range("resignation_date").Value
.Cells(Next_Row, 10).Value = Range("notice_period").Value
.Cells(Next_Row, 11).Value = Range("termination_date").Value
.Cells(Next_Row, 13).Value = Range("leave_reason").Value
.Cells(Next_Row, 14).Value = Range("vol_unvol").Value
.Cells(Next_Row, 15).Value = Range("new_employer").Value
.Cells(Next_Row, 16).Value = Range("termination_obligations").Value
.Cells(Next_Row, 17).Value = Range("regretted").Value
End With
ClearFormFields
End Sub
Private Sub ClearFormFields()
Const AllFields As String = "employee_id,employee_name,resignation_date,notice_period" & _
"termination_date,leave_reason,vol_unvol,new_employer" & _
"termination_obligations,regretted"
Dim Fld() As String
Dim i As Integer
Fld = Split(AllFields, ",")
For i = 0 To UBound(Fld)
Range(Fld(i)).Value = ""
Next i
End Sub
Please study the comments I added. After you understand the above code you might advance to use the code below which is doing exactly the same thing - but differently, and perhaps more efficiently. I have also included the provision for creating mandatory fields which we discussed in an earlier thread.
Option Explicit
Sub data_input()
' 070
Const AllFields As String = "employee_id,employee_name,resignation_date,notice_period," & _
"termination_date,leave_reason,vol_unvol,new_employer," & _
"termination_obligations,regretted"
Dim Ws_Output As Worksheet ' "Data" tab
Dim Fld() As String ' array of named range names
Dim Clms As Variant ' target columns (match with Fld)
Dim i As Integer ' index to both Fld() and Clms()
Dim Rt As Long ' target row
Dim Miss As Range ' missing entry
Fld = Split(AllFields, ",")
' match sequence of column numbers to array Fld()
Clms = Array(1, 2, 9, 10, 11, 13, 14, 15, 16, 17)
Set Miss = MissingEntry(Fld)
If Miss Is Nothing Then
Set Ws_Output = Worksheets("Data")
With Ws_Output
Rt = .Range("A" & .Rows.Count).End(xlUp).Row + 1
For i = 0 To UBound(Fld)
.Cells(Rt, Clms(i)).Value = Range(Fld(i)).Value
Range(Fld(i)).Value = ""
Next i
End With
Else
MsgBox "'" & Miss.Offset(-1).Value & "' is a mandatory field." & vbCr & _
"Please make an appropriate entry.", _
vbExclamation, "Missing entry"
Miss.Select
End If
End Sub
Private Function MissingEntry(Fld() As String) As Range
' 070
' list the fields which are NOT mandatory
Const Options As String = "resignation_date,notice_period," & _
"termination_date,leave_reason,vol_unvol,new_employer," & _
"termination_obligations,regretted"
Dim f As Integer ' loop index of Fld()
For f = 0 To UBound(Fld)
If Range(Fld(f)) = "" Then
If InStr(1, Options, Fld(f), vbTextCompare) = 0 Then
' if the missing entry is not optional
Set MissingEntry = Range(Fld(f))
Exit For
End If
End If
Next f
End Function
The trade-off you can observe is between preparation and code volume. The more time you invest in setting up an environment in which your code can run the less code you will need. The time given and saved is often nearly the same. But you permanently gain efficiency and save on maintenance effort .
