How to Fix Loop in a template excel format

Question

I have a workbook for payroll data from which I need to create a ready template payslip. The identifier when creating a new worksheet will be the name of the employee. The template is created but I have trouble reflecting the data in loop. it's not returning the data I need.

I'm using a simple VBA as I've been trying to create the payroll slip manually:

Set wsPay = .Sheets("Payroll")
Set wsTEMP = .Sheets("Template")

Range("F6").Select
ActiveCell.FormulaR1C1 = wsPay.Range("AI" & Rows.Count).End(xlDown).row + 1

Range("J6").Select
ActiveCell.FormulaR1C1 = wsPay.Range("E" & Rows.Count).End(xlDown).row + 1

Range("B7").Select
ActiveCell.FormulaR1C1 = wsPay.Range("C" & Rows.Count).End(xlDown).row + 1

Range("H7").Select
ActiveCell.FormulaR1C1 = wsPay.Range("AJ" & Rows.Count).End(xlDown).row + 1

Range("K7").Select
ActiveCell.FormulaR1C1 = wsPay.Range("H" & Rows.Count).End(xlDown).row + 1

Range("B8").Select
ActiveCell.FormulaR1C1 = wsPay.Range("I" & Rows.Count).End(xlDown).row + 1

Range("H8").Select
ActiveCell.FormulaR1C1 = wsPay.Range("AK" & Rows.Count).End(xlDown).row + 1

Range("K8").Select
ActiveCell.FormulaR1C1 = wsPay.Range("F" & Rows.Count).End(xlDown).row + 1

in the loop, whereas the formula needed to return the corresponding cell data in that column range is not capturing.

Beg your pardon, but what this formula should calculate? For now it just stores a number... — JohnyL, Jun 29 '19 at 18:05
Hi yes it should store data coming from the payroll sheet then loops since im creating a payslip for a hundred people, it does create a payslip template of a hundred but the storing/capturing data from payroll sheet to the created template slips is not capturing in my loop it gives me different or wrong data — Anne, Jun 29 '19 at 18:16
I guess, you need `Value` instead of `FormulaR1C1` and `.Offset(1)` instead of `.Row + 1` — JohnyL, Jun 29 '19 at 18:22

pgSystemTester · Answer 1 · 2019-06-29T22:48:09.940

It's unclear what you're doing, but here's some commentary on your code which maybe you use to figure out the issue.

There's a lot of improvement that can likely be done with these statements:

Range("J6").Select
ActiveCell.FormulaR1C1 = wsPay.Range("E" & Rows.Count).End(xlDown).row + 1

You don't need to use Select (see this article). You can be more succinct by typing Range("J6").FormularR1C1 =
You're setting the value on to be a number. Specifically a row plus 1. Is that intentional? Probably better to use Range("J6").Value2. Assuming you don't want any calculations or anything else in the cell, the FormulaR1C1 will technically work in this case, but not really the best approach.
You're doing something wrong with the wsPage.Range. Assuming you're on 2007 or higher this will ALWAYS return 1,048,577. You're setting it to the bottom cell in Column E (the count of total rows in a cell) but then you're using End(xlDown), which will do nothing since you're already at the bottom of the worksheet. Either use xlUp or start somewhere higher... e1?

How to Fix Loop in a template excel format

1 Answers1