Build random numbers in column A

[FryGirl]

Hello all,

In column A starting with A2, I would like to fill down with 1, 2, 3, 4, 5, 6, etc., but I would like to get random numbers between 3 and 8.

So, for example, see below. I will then fill in column B with dates. In the end, I will drag this down as far as today's date.

Budget.xlsm
	A	B
1	Numbers	Date
2	1	1-Jan-20
3	1	1-Jan-20
4	1	1-Jan-20
5	1	1-Jan-20
6	1	1-Jan-20
7	2	2-Jan-20
8	2	2-Jan-20
9	2	2-Jan-20
10	2	2-Jan-20
11	2	2-Jan-20
12	2	2-Jan-20
13	2	2-Jan-20
14	2	2-Jan-20
15	3	3-Jan-20
16	3	3-Jan-20
17	3	3-Jan-20
18	4	4-Jan-20
19	4	4-Jan-20
20	4	4-Jan-20
21	4	4-Jan-20
Sheet4

Cell Formulas
Range	Formula
B7:B21	B7	=IF(A6<>A7,MAX($B$6:B6)+1,B6)

 

[joeu2004]

@joeu2004 - When I run your code starting at January 1, 2020, it stops at December 30th, not December 31st.

Works fine for me. Even if you just download the file that I cited, you can see 366 31-Dec-2020 in row 2064.

Thanks for the addition Rick. This works well.

Not if we use a start date other than 1-Jan.

For example, with 1-Apr-2021 in B2, my version goes down 365 31-Mar-2022. @FryGirl, isn't that what you would want?

Rick's version stops at 275 31-Dec-201, by design, namely: Do While D <= DateSerial(Year([B2]), 12, 31).

@FryGirl, which do you want?

You wrote: ``generate the sequenced numbers in column A with the dates in column B for a year's worth``.

I interpret that to mean for 12 months, not stopping at the end of a year arbitrarily.

-----

Moreover, Rick's implementation leaves text values in column A. I would think numeric values are preferred. A simple thing to correct in Rick's code.

But I don't see significant difference, nor benefit. If you prefer Rick's square-bracket notation, the equivalent of my implementation would be the following. (Meh!)

Sub doit()
Dim v(1 To 2928, 1 To 2) As Variant
Dim curDate As Double
Dim i As Long, j As Long, r As Long

curDate = [B2]
For i = 1 To WorksheetFunction.EDate(curDate, 12) - curDate
    For j = 1 To WorksheetFunction.RandBetween(3, 8)
        r = r + 1
        v(r, 1) = i
        v(r, 2) = curDate + i - 1
    Next
Next
[A2:B2929] = v
With [B2:B2929]
    .NumberFormat = [B2].NumberFormat
    .EntireColumn.AutoFit
End With
End Sub

PS.... My original version had some unnecessary variables and calculations on purpose, in order to make the purpose of the code clear to less-trained eyes.

-----

Late addition....

I don't see it anymore either... it now goes down to December 31st like it should. I must have done something wrong

Thanks.

 

[FryGirl]

For example, with 1-Apr-2021 in B2, my version goes down 365 31-Mar-2022. @FryGirl, isn't that what you would want?

Yes, that's what I want, but honestly they are all great choices.

As for the square brackets and the hard coding of the dates, no, I would rather stay away from those things. I'm not the best at coding so I would prefer to qualify everything as well as possible so I don't get caught referencing a sheet I don't actually intend to be working on.

Yes. I also noticed column A was text, so I used pastespecial and multiplied by 1, but with your code that would not be a necessary step.

@joeu2004 I'm indeed very thankful for your help.

 

[joeu2004]

I'm not the best at coding so I would prefer to qualify everything as well as possible so I don't get caught referencing a sheet I don't actually intend to be working on.

Ironically, that negates my final thoughts on the matter.

I was going to point out that I had put my VBA code into a worksheet object, instead of a normal module. I was going to suggest the latter, so that it is applicable more generally.

But by putting it into a worksheet object, it avoids "referencing a sheet [we] don't actually intend to" (without extra effort) -- whether we use the square-bracket or Range notation.

Which is why I did that way initially, too.

 

[FryGirl]

Funny indeed. I did notice at first it was in the worksheet object versus a module. I've qualified the code and placed it in a module, so all is well. Thanks again.