Macro to automatically enter serial numbers as per row count

[upendra2206]

Table 1: Sheet 1

Sr No	Salary	Gender	AGE
1	2000	M	23
2	3000	M	24
3	4000	F	25

<tbody>
</tbody>

Table 2: Sheet 2 -Before Macro

Employee Details	Eligibility
3
4
5

<tbody>
</tbody>

Table 2: Sheet 2 - After Macro

Employee Details	Eligibility
3
4
5

<tbody>
</tbody>

I have a macro in Sheet 1 to populate the data in Table 1. Now I want a macro for Table 2 in Sheet 2 such that it counts the number of rows in Table 1 i.e. 3 in this case and insert the Serial Nos in Table 2 but starting with 3. i.e. 3,4,5 since there are three rows in Table 1. Had the number of rows been 4 in Table 1, I would have wanted 3,4,5,6 in Table 2 in employee details. The reason for starting the serial numbers from 3 is that I have used these serial Nos of Table as a Row index number in my HLookup formula in the remaining cell. Is there any away of doing it?

Thanks in Advance

 

[jtrombley24]

Hi upendra - try the following. This is off the top of my head and untested:

dim finalRow as Long
dim i as Long

finalRow = Cells(Rows.Count, 1).End(xlUp).Row

for i = 3 to finalRow
sheet2.cells(i - 2, 0).value = sheet1.cells(i, 0).value
next i

 

[upendra2206]

Hi,

I tried but I am getting an error "Application defined or object defined error" in this line "Sheet2.Cells(i - 2, 0).Value = Sheet1.Cells(i, 0).Value"

Table 1 starts with A1 of Sheet1 and Table 2 starts with A1 of Sheet2

 

[jtrombley24]

Yeah sorry, I gave the wrong column reference. There is no such thing as a '0' column. Change the '0' to 1, which is actually column 'A.' So it should read:

Sheet2.Cells(i - 2, 1).Value = Sheet1.Cells(i, 1).Value

Hope that helps.

 

[jtrombley24]

Also, without seeing your actual spreadsheet and not fully understanding your request, you will need to modify the i variable. Just know that Cells(1, 1).Value means "the value of cell A1." So, in the code, i represents the row (which increments by 1 each time the loop rolls around) and 1 is the column. So column A is 1, column B is 2 and so on.

Likely you will need to change my code to match your columns and rows. You may also need to change the loop back to 1; i.e. For i = 1 to finalRow.

 

[upendra2206]

Hi,

Tried your way but the output is not what I want. My aim is that for count(A2: last row(in this case 4th row)=3 therefore om table 2 in sheet 2 I want the serial numbers to start from A3 to A4,A5,A6 i.e. 3,4,5 in this case. Here serial Numbers expected = Total no of filled rows in table 1. I hope I ma clear now. I have figured out with a formula tough and its : =IF(COUNT(Sheet1!$B$1:$B$3)-ROW()>-3,ROW(),""). and then i drag this formula. Any macro way of doing it please

 

[upendra2206]

Count
12
4551
23
34
5
6

<tbody>
</tbody>

Sheet 1

Sr No.

<tbody>
</tbody>

Sheet 2

So this is the case. Now after macro I want my Sheet 2 table to be:

Sr No.

3

4

5

6

6

7

<tbody>
</tbody>

Since the count of the no of data rows in sheet 1 is 6.

Please help.