How to fill columns in excel with variable data?
I would like to fill the first A1 column with numbers from 600001 to 800000. After it reaches 2000 it should be 10 cells with "######" or zero or something like that.
Example:
601999
602000
"######" (ten times)
602001
.
.
604000
"######" (ten times)
and so on...It should be like this after every 2000
Do you know a solution for this?
Thank you in advance!
2 Answers
Copy the following into rows 1-200990 (or 1-201000 if you want the ###### lines at the bottom):
=IF(MOD(ROW()-1,2010)>=2000,"######",INT((ROW()-1)/2010)*2000+MOD(ROW(),2010)+600000)It works by identifying the last 10 cells of each 2010 rows and writing the padding text there, then eliminating the extra 10 rows from the number calculation on the other cells.
Because this generates a mixture of text and numbers, which by default will have opposite justification, you will probably want to left- or right-justify the whole column; or you can convert the numbers to text with:
=IF(MOD(ROW()-1,2010)>=2000,"######",TEXT(INT((ROW()-1)/2010)*2000+MOD(ROW(),2010)+600000,"######"))You will need to add offsets into the calculations if there are column headers.
Something like this might work:
myRow = 1
r = 600001
For i = 1 to 100 for j = 1 to 2000 Range("A", myRow).value = r r = r+1 myRow = myRow + 1 next j for k = 1 to 10 Range("A", myRow).value = "######" myRow = myRow + 1 next k
Next i
