Selected Answer
For your 4th day at VBA here is a totally different approach. You can run the code on your project without modification but if you want to hitch it to your button on the Entry sheet you need to assign the macro there by its name "PostPaymentReceipt", or change this name to what you have assigned to the button.
Sub PostPaymentRceipt()
' 229
Dim Inputs As Variant ' array of inputs
Dim R As Long ' loop counter: input Rows
Dim Ws As Worksheet ' variable assignments
Dim i As Integer ' loop counter: worksheets
ReDim Inputs(1 To Int((25 - 11) / 2) + 1) ' define size of array
For R = 11 To 25 Step 2
' read the cell values into the array
Inputs(((R - 11) / 2) + 1) = Sheet1.Cells(R, "C").Value
If (R = 15) And IsEmpty(Inputs(((R - 11) / 2) + 1)) Then Exit Sub
Next R
Set Ws = Worksheets("Data") ' tab to write to on first loop
For i = 1 To 2
' last used row in column 1 (=A) from the bottom plus one row
R = Ws.Cells(Ws.Rows.Count, 1).End(xlUp).Row + 1
' make Cells(R, "A") the same size as the array and paste the value
Ws.Cells(R, 1).Resize(1, UBound(Inputs)).Value = Inputs
Set Ws = Worksheets("Fin. Affairs") ' tab to write to on second loop
Next i
With Sheet1
.Range("C15:C25").ClearContents ' delete the recorded data
.Cells(15, 3).Select 'ready to enter next payment
End With
End Sub
So, what's different in the approach? The macro has 3 distinct sections.
- Read the data.
- Write the entries
- Clear up the mess
It also has a deeper layer of organization: each time something is read or written a worksheet is specified. Observe that the Entry tab is referred to as Sheet1. That is its CodeName which you can see in VBE's Project Explorer and change in the Properties. The point is that the CodeName stays the same even while the user may change the tab's name. For practical purposes you can use Sheet1 or Worksheets("Entry") interchangably. But not specifying the sheet you write to or delete from is generally considered a loose cannon.
Read the data.
Your ouput ranges have 8 fields and are identical in both output sheets (excellent planning!). You need an array of 8 elements to write to them. Your input form also has 8 fields, regularly spaced. So it's just a matter of juggling the numbers 11 (first row), 25 (last row) and 2 (interval) until a simple loop generates the row numbers 11 to 25 in intervals of 2 and puts these numbers next to a count from 1 to 8.
You end up with an array (1 To 8) and each of the input cells' values assigned to one element. Observe that you save the task of declaring named ranges.
Write the entries.
The task is repetetitive. Therefore you employ another loop, twice. On each turn all action is the same but the subject (the worksheet) changes. So, you define one worksheet before the loop is entered and the second one at the end of the first loop so that it will be treated on the second round.
The action itself, within each loop, is just to write the ready array to a particular row. So, the code picks the row, defines the first cell in that row, expands that one cell to include the next 8 cells (same size as the array in case you want to add fields to it in the future), and paste the array to the defined range.
Clear up the mess.
The entered data have been posted. I hate to run to the boss all the time and tell him that I did my job. I treat my code the same way. Therefore I just delete the data expecting the user to see that the job is done - better than MsgBox "Mission accomplished". An additional encouragement for him to keep track of his wits is to directy his focus to the next entry. So the code sets the cursor for him to C15 which is where he should enter something next.
Now, just in case the sleepy colleague will press the Submit button again nevertheless, I added this line above Next R in the frist (Read the data) section.
If (R = 15) And IsEmpty(Inputs(((R - 11) / 2) + 1)) Then Exit Sub
It simply says that nothing should be done if C15 is empty, and this idea could be expanded to include other essential fields and give a message after he has done something wrong, letting him work peacefully and efficiently, without useless interruption, while he is doing everything right.
Observe that the question of what to delete at what stage doesn't come up in my essay because the structure is so clear that no doubt can persist. This advantage will also stay with this code as it gets developed over time. It will always be simple code that looks complicated, as opposed to your first attempt that looks simple but throws up unexpected complications.
