-
Welcome to BirdForum, the internet's largest birding community with thousands of members from all over the world. The forums are dedicated to wild birds, birding, binoculars and equipment and all that goes with it.
Please register for an account to take part in the discussions in the forum, post your pictures in the gallery and more.
Help wanted with Ecxel formula (1 Viewer)
- Thread starter T0ny
- Start date
T0ny
Okillre Member
OK, here goes. I want to create a spreadsheet to print labels, fifteen across by thirteen deep, in numerical sequence, first across then down, in sets of three. i.e. :
1 1 1 2 2 2 3 3 3 4 4 4 5 5 5
6 6 6 7 7 7 8 8 8 9 9 9 10 10 10
11 11 11 12 12 12 13 13 13 14 14 14 15 15 15
and so on, without having to laboriously enter each number individually. I need to create labels from 1 to 4000. I have tried some of the hints from the Office 2000 Bible, which have left me tearing my hair, an activity I can ill-afford to indulge in these days. If anyone has any helpful suggestions, I shall be most grateful.
Tony
1 1 1 2 2 2 3 3 3 4 4 4 5 5 5
6 6 6 7 7 7 8 8 8 9 9 9 10 10 10
11 11 11 12 12 12 13 13 13 14 14 14 15 15 15
and so on, without having to laboriously enter each number individually. I need to create labels from 1 to 4000. I have tried some of the hints from the Office 2000 Bible, which have left me tearing my hair, an activity I can ill-afford to indulge in these days. If anyone has any helpful suggestions, I shall be most grateful.
Tony
jeff
Well-known member
I'm guessing your going to Mail Merge this into Word for your lables, excel is just giving you the data?
If so you could do something like, its a bit raw but should do the trick!:
I've attached the excel file as well:
Sub Aut
pen()
A = InputBox("Input start Number", "Start")
B = InputBox("Input End Number", "End")
j = 1
For i = A To B
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
Next i
End Sub
If so you could do something like, its a bit raw but should do the trick!:
I've attached the excel file as well:
Sub Aut
pen()A = InputBox("Input start Number", "Start")
B = InputBox("Input End Number", "End")
j = 1
For i = A To B
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
C = "A" & j
Range(C).Select
ActiveCell.FormulaR1C1 = i
j = j + 1
Next i
End Sub
T0ny
Okillre Member
Jeff,
My apologies, maybe I didn't explain myself very well.What I had in mind was a spreadsheet where the numbers 1 to 5 appeared triplicated in cells A1 to A15 (ie A1 = 1, A2 = 1, A3 = 1, A4 = 2, A5 = 2, A6 = 2 etc), then dropping down to Row B for 6 to 10 (B1 = 6, B2 = 6, B3 = 6 etc). The formula you so kindly provided actually produces 16 individual sheets, with all the numbers in Column 1 on sheet 16, repeated 3 times - or am I doing something wrong with it ?
Yours, puzzled
Tony
My apologies, maybe I didn't explain myself very well.What I had in mind was a spreadsheet where the numbers 1 to 5 appeared triplicated in cells A1 to A15 (ie A1 = 1, A2 = 1, A3 = 1, A4 = 2, A5 = 2, A6 = 2 etc), then dropping down to Row B for 6 to 10 (B1 = 6, B2 = 6, B3 = 6 etc). The formula you so kindly provided actually produces 16 individual sheets, with all the numbers in Column 1 on sheet 16, repeated 3 times - or am I doing something wrong with it ?
Yours, puzzled
Tony
jeff
Well-known member
Tony,
I guessed when you said lables you were using the Excel data to import into Word Lables.
Do you mean you just want the data to appear in Excel Cells?
A B C D E .....
1 6 11 16 21 ....
1 6 11 16 21 ....
1 6 11 16 21 ....
2 7 12 17 26....
...and so on........
If so its not possible to do 1-4000 in triplicate (not enough columns in Excel, or at least in Excel 2000 as i'm using) you can only get up to 1280.
i've attached an Excel sheet showing this, maybe i've missunderstood again?
I guessed when you said lables you were using the Excel data to import into Word Lables.
Do you mean you just want the data to appear in Excel Cells?
A B C D E .....
1 6 11 16 21 ....
1 6 11 16 21 ....
1 6 11 16 21 ....
2 7 12 17 26....
...and so on........
If so its not possible to do 1-4000 in triplicate (not enough columns in Excel, or at least in Excel 2000 as i'm using) you can only get up to 1280.
i've attached an Excel sheet showing this, maybe i've missunderstood again?
Last edited:
T0ny
Okillre Member
Sorry again Jeff, it was late last night when I posted, and I made another stupid error - I'd forgotten it was the columns that were headed alphabetically. Let's start again.
A1=1, B1=1, C1=1, D1=2, E1=2, F1=2. G1=3, H1=3, I1=3, J1=4, K1=4, L1=4, M1=5, N1=5, O1=5
then
A2=6, B2=6, C2=6, D2=7, E2=7, F2=7, G2=8, H2=8, I2=8, J2=9, K2=9, L2=9, M2=10, N2=10, O2=10
etc.
Is that possible, please ?
Tony
A1=1, B1=1, C1=1, D1=2, E1=2, F1=2. G1=3, H1=3, I1=3, J1=4, K1=4, L1=4, M1=5, N1=5, O1=5
then
A2=6, B2=6, C2=6, D2=7, E2=7, F2=7, G2=8, H2=8, I2=8, J2=9, K2=9, L2=9, M2=10, N2=10, O2=10
etc.
Is that possible, please ?
Tony
T0ny
Okillre Member
Jeff,
Thank you very much - that was exactly what I needed. I am eternally in your debt. Sorry I didn't explain myself better earlier.
For those that have followed this saga of sense (on Jeff's part) and stupidity (on mine), the labels are for my collection of 7" 45rpm singles - one label for each side of the sleeve, and one for the record itself.
Tony
Thank you very much - that was exactly what I needed. I am eternally in your debt. Sorry I didn't explain myself better earlier.
For those that have followed this saga of sense (on Jeff's part) and stupidity (on mine), the labels are for my collection of 7" 45rpm singles - one label for each side of the sleeve, and one for the record itself.
Tony
