Select items at random based on date range

[kelly mort]

HelloWorld,


I have this challenge here with me and I need some help.


I want to look compare the day and month of the date in cell A2 with the date ranges below. Then when I find a match , I use that match to select at random one item from one of the arrays.


So if the match is seen in range A of the dates, then we select at random one item from arr(1).
If in date range B then arr (2)


In that order.




These are sample of date ranges :

A = 1st May – 30th June 
B = 1st July – 31st August 
C = 1st September – 31st October 
D = 1st November – 31st December 
E = 1st January – 29th February

Note :
These dates will be hard coded and will vary.


Say 23rd may – 17th June is a good example of what dates I will be using .


Only the day and month is needed here, the year is not needed in this situation.


My dates are in the format “dd-mm-yy”


These are the examples for the array I want to use :

Arr (1) = (“mango”, “apple”, “pear”, “orange”, “lime”)
Arr (2) = (“Tilapia”, “whale”, “Tuna”, “salmon”)
Arr (3) = (“Red”, “white”, “black”, “ash”, “violet”, “blue”, “green”)
Arr (4) = (“kia”, “hundai”, “Nissan”, “Toyota”)
Arr (5) = (“Dell”, “Lenovo”, “HP”, “Toshiba”, “Accer”)

 

[mse330]

Hey kelly,

Try the below code, is this what you're trying to achieve ? Note that this is not reading from cell A2 I was just testing … And there is always a better way to do it

Sub RandomArray()

Dim RndItem As String, dDate As Long
Dim Arr1 As Variant, Arr2 As Variant, Arr3 As Variant, Arr4 As Variant, Arr5 As Variant

Arr1 = Array("mango", "apple", "pear", "orange", "lime")
Arr2 = Array("Tilapia", "whale", "Tuna", "salmon")
Arr3 = Array("Red", "white", "black", "ash", "violet", "blue", "green")
Arr4 = Array("kia", "hundai", "Nissan", "Toyota")
Arr5 = Array("Dell", "Lenovo", "HP", "Toshiba", "Accer")

dDate = WorksheetFunction.RandBetween(#1/1/2019#, #12/31/2019#)
dDate = DateSerial(2020, Month(dDate), Day(dDate))

Select Case dDate
    Case #5/1/2020# To #6/30/2020#: RndItem = Arr1(WorksheetFunction.RandBetween(LBound(Arr1), UBound(Arr1)))
    Case #7/1/2020# To #8/31/2020#: RndItem = Arr2(WorksheetFunction.RandBetween(LBound(Arr2), UBound(Arr2)))
    Case #9/1/2020# To #10/31/2020#: RndItem = Arr3(WorksheetFunction.RandBetween(LBound(Arr3), UBound(Arr3)))
    Case #11/1/2020# To #12/31/2020#: RndItem = Arr4(WorksheetFunction.RandBetween(LBound(Arr4), UBound(Arr4)))
    Case #1/1/2020# To #2/29/2020#: RndItem = Arr5(WorksheetFunction.RandBetween(LBound(Arr5), UBound(Arr5)))
End Select

Debug.Print Format(dDate, "d-mmm"), RndItem

End Sub

 

[kelly mort]

Sure!!!

I tried reading from cell A2 and it worked fine.

My question :

What's that 2020 for?

 

[mse330]

You said you don't care about the year so ​

I used the year 2020 becuase its a leap year where Feb has 29 days

 

[kelly mort]

You said you don't care about the year so ​

I used the year 2020 becuase its a leap year where Feb has 29 days

Okay so it can handle every year right?

 

[mse330]

Yes, it should handle any year whether leap year or not

 

[kelly mort]

Yes, it should handle any year whether leap year or not

Sharp!!!


Thanks

 

[kelly mort]

Yes, it should handle any year whether leap year or not

I am having trouble with date range like thus:

Case #12/25/2020# To #1/4/2020#

It didn't work out.

So I did

Case #12/25/2020# To #1/4/2021#

And this can pick dates from 25th Dec to 30th Dec but not any date in the new year 2021.

I believe I am doing something wrongly somewhere.

Can you pull me out again ?

Regards

 

[mse330]

Ok, I didn't think about that scenario but was more concerned about the 29th of Feb based on your exaplme ...

Try to have your range in the below format

Case #12/25/2020# To #12/31/2020#, #1/1/2020# To #1/4/2020#:

 

[kelly mort]

Ok, I didn't think about that scenario but was more concerned about the 29th of Feb based on your exaplme ...

Try to have your range in the below format

Case #12/25/2020# To #12/31/2020#, #1/1/2020# To #1/4/2020#:

You are a genius! !!

I don't know why that thing skipped my mind.