loop macro only bringing back 1 row of data...

[tmcwilson]

I am working on a form for bonus payment tracking that allows users to enter up to 12 payment dates/amounts (cells C29:D40) per bonus that will feed over to a "database worksheet with a separate row for each payment.

Doesn't seem like it should be too hard, but the code I have is only bringing back the first payment row of data (Row 29).... I'm clearly missing something, but am just not seeing it! I would appreciate some suggestions on how to get this to work? I need it to add 4 rows to the database worksheet - one for each payment. Thanks!

		Referral / Retention / Sign on Bonus Form
Bonus Type		Sign-on Bonus				Please complete/verify all YELLOW cells
Recruiter		Wilma, Flinstone
Approved Date:		9/21/2022
Employee ID		59999
Name		Barney Rubble
Position		Mechanic (C)
Location		SK999 - Bedrock
Hire Date		9/21/2022
Is shop paying part of bonus		No
* Communicate with the GM and DO about Bonus.
* Submission must be completed by Friday at Noon to be paid the following week.
* Bonus payments will be included on weekly (Friday) payroll checks.
*Please DO NOT adjust amounts for past dates. All Current and Past Due amounts will be paid the following Friday.
Days from Hire Date to Payment	Calendar Date	Pay Date	Amount		Comments
30	10/21/2022	10/14/2022	$ 100
60	11/20/2022	11/18/2022	$ 200
90	12/20/2022	12/16/2022	$ 300
120	1/19/2023	1/13/2023	$ 400
Total Bonus			$ 1,000

HR Bonus Database 2.xlsm
	A	B	C	D	E	F	G	H	I	J							Q	R	S	T
1	Referral / Retention / Sign on Bonus Tracking						Enter Pay Date to be validated
2	Total Records	48
3	Bonus Type	Recruiter	Approved Date	Employee ID	Name	Position	Location	Dept.	Market	Most Recent Hire Date							Comments	Total Bonus	Bonus Payment Date	Bonus Payment
51	Sign-on Bonus	Wilma, Flinstone	9/21/2022	59999	Barney Rubble	Mechanic (C)	SK999 - Bedrock			9/21/2022								$ 1,000	10/14/2022	$100.00
Database

Cell Formulas
Range	Formula
B2	B2	=COUNT(D:D)

The Code I have so far...

Sub MoveToDatasheet()

Set Form = ThisWorkbook.Sheets("Form")
Set Database = ThisWorkbook.Sheets("Database")
Dim last_row As Long, active_row As String, Cell As Range
'''Find next empty row in database'''
last_row = Database.Range("b2").Value + 4
active_row = ActiveCell.Row

        For Each Cell In Range("C29", Range("C30").End(xlDown))
        If Cell.Value = "" Then Exit For
            Form.Range("c" + active_row).Select
            '''Bonus Type''''
                Database.Range("A" & last_row).Value = Form.Range("c4").Value
            '''Recruiter''''
                Database.Range("B" & last_row).Value = Form.Range("c5").Value
            '''Approved Date''''
                Database.Range("c" & last_row).Value = Form.Range("c6").Value
            '''Employee ID''''
                Database.Range("d" & last_row).Value = Form.Range("c7").Value
            '''Employee Name''''
                Database.Range("e" & last_row).Value = Form.Range("c8").Value
            '''Position''''
                Database.Range("f" & last_row).Value = Form.Range("c9").Value
            '''Location''''
                Database.Range("g" & last_row).Value = Form.Range("c10").Value
            '''MR Hire Date''''
                Database.Range("j" & last_row).Value = Form.Range("c11").Value
            '''Referred TM ID''''
                Database.Range("k" & last_row).Value = Form.Range("c13").Value
            '''Referred TM Name''''
                Database.Range("l" & last_row).Value = Form.Range("c14").Value
            '''Referred TM Position''''
                Database.Range("m" & last_row).Value = Form.Range("c15").Value
            '''Referred TM Location''''
                Database.Range("n" & last_row).Value = Form.Range("c16").Value
            '''Referred TM MRHD''''
                Database.Range("o" & last_row).Value = Form.Range("c17").Value
            '''Shop paying part of Bonus''''
                Database.Range("p" & last_row).Value = Form.Range("c18").Value
            '''Comments''''
                Database.Range("q" & last_row).Value = Form.Range("f29").Value
            '''Total Bonus''''
                Database.Range("r" & last_row).Value = Form.Range("d41").Value
            '''Bonus Payment Date''''
                Database.Range("s" & last_row).Value = Form.Range("c" + active_row).Value
            '''Bonus Payment Amount''''
                Database.Range("t" & last_row).Value = Form.Range("D" + active_row).Value
       Next Cell
End Sub

 

[StephenCrump]

Welcome to the Forum!

A couple of observations about the way your code is structured (because of these issues, it's not clear to me what your code is actually trying to do):

- You're setting: active_row = ActiveCell.Row but what is ActiveCell? Are you relying on the user to select the correct cell to start with? It's rarely necessary to use .Select, ActiveCell etc, and much better practice to refer directly to the cell you want.
- You are not subsequently incrementing active_row. Presumably you should be?
- You are also not incrementing last_row, so in each iteration of the loop you will overwrite the same row.
- Why are you hardcoding cell references like C4, C5, C6 .. etc? Shouldn't these vary with each iteration of the loop?

I hope this will point you in the right direction. We'll be happy to take a look at your next version of the code if it's still not working as intended.

 

[tmcwilson]

Welcome to the Forum!

A couple of observations about the way your code is structured (because of these issues, it's not clear to me what your code is actually trying to do):

- You're setting: active_row = ActiveCell.Row but what is ActiveCell? Are you relying on the user to select the correct cell to start with? It's rarely necessary to use .Select, ActiveCell etc, and much better practice to refer directly to the cell you want.
- You are not subsequently incrementing active_row. Presumably you should be?
- You are also not incrementing last_row, so in each iteration of the loop you will overwrite the same row.
- Why are you hardcoding cell references like C4, C5, C6 .. etc? Shouldn't these vary with each iteration of the loop?

I hope this will point you in the right direction. We'll be happy to take a look at your next version of the code if it's still not working as intended

I had an increment in for active_row, but accidentally removed it from the code I posted. I had thought Next cell would increment the last_row...but see now why that didn't work.

Your observations were enough to help me figure out the issues and correct my code. It is working now!!

Thanks!

 

[StephenCrump]

Your observations were enough to help me figure out the issues and correct my code. It is working now!!

Well done! I am glad we could help.