Build random numbers in column A

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
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
AB
1NumbersDate
211-Jan-20
311-Jan-20
411-Jan-20
511-Jan-20
611-Jan-20
722-Jan-20
822-Jan-20
922-Jan-20
1022-Jan-20
1122-Jan-20
1222-Jan-20
1322-Jan-20
1422-Jan-20
1533-Jan-20
1633-Jan-20
1733-Jan-20
1844-Jan-20
1944-Jan-20
2044-Jan-20
2144-Jan-20
Sheet4
Cell Formulas
RangeFormula
B7:B21B7=IF(A6<>A7,MAX($B$6:B6)+1,B6)
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,031
Office Version
  1. 2010
Platform
  1. Windows
@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!)

Rich (BB code):
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,031
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,245
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,412
Messages
5,641,993
Members
417,250
Latest member
spr1nger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top