Sequential numbers

[neilp]

Hi

Got a spreadsheet whereby row one contains important info:
B1 is a constant code (501107), D1 is a constant code (171205) and F1 is a number starting at 0001. H1 contains a number that i type in.

What needs to happen with this spreadsheet is, A2 needs to have B1&D1&F1 and it needs to carry on down column A until it has repeated by the number of times in H1.
eg A2 would be 501107171205001, A3 would be 501107171205002 etc. Happy for it to work in VBA if that is easier, but I am a bit stumped.

Any Ideas?

Thanks in advance

Neil

 

[FormR]

Hi, something like this, copied down as far as may be required.

Excel 2013/2016
	A	B	C	D	E	F	G	H
1		501107		171205		1		10
2	501107171205001
3	501107171205002
4	501107171205003
5	501107171205004
6	501107171205005
7	501107171205006
8	501107171205007
9	501107171205008
10	501107171205009
11	501107171205010
Audit Sheet

Cell Formulas
Range	Formula
A2	=IF(ROWS($A$2:A2)>$H$1,"",$B$1&$D$1&TEXT($F$1+ROWS($A$2:A2)-1,"000"))

 

[neilp]

Thanks FormR. certainly works. The file will get uploaded to another system, so it might kick it out for having formulas in cells that aren't being used.

Will post again if that is a problem and then I'd have to look at the VBA option.

Thanks for your help

Neil

 

[FormR]

it might kick it out for having formulas in cells that aren't being used.

You could try something like this to only populate the required cells with the formula.

Sub m()
Range("A2").Resize(Range("H1").Value).Formula = "=$B$1&$D$1&TEXT($F$1+ROWS($A$2:A2)-1,""000"")"
End Sub