VBA code problem with random numbers generations

[alihadi]

I want to generate random numbers between (1 &10) in column B starting with B4 and generating ones based on the generated number in B4, for example: if number 5 was generated in B4 , generate 5 ones in range (C4 to C8), and in column A generate random numbers between (1 & 3) starting with A4. Every click the model should give random numbers.
Would you please have a look at the attached example.
I will be very grateful if you can assist me.
Regards.
Ali

[Kenneth Hobs]

Should be easy enough. I guess you would want all the existing data in A:C to be deleted with each run. Could the random integers 1-3 be repeated? If random, you could get 1,1,1. The same thing for the 3 numbers in column B 1-10. Cold they be repeated.

What about sort order for the random numbers? I see that column A numbers are sorted but that may be coincidence. Column B number are not sorted.

[Paul_Hossler]

Maybe something like this





Option Explicit

Const ciTotalNumberOfRandoms As Long = 10

Sub GenerateSomeRandomNumbers()
Dim N As Long, i As Long, iRow As Long, j As Long


ActiveSheet.Columns("A:C").Clear

iRow = 4

For i = 1 To ciTotalNumberOfRandoms
N = Application.WorksheetFunction.RandBetween(1, 10)

ActiveSheet.Cells(iRow, 1).Value = i
ActiveSheet.Cells(iRow, 2).Value = N

For j = 1 To N
ActiveSheet.Cells(iRow, 3).Value = 1
iRow = iRow + 1
Next j
Next I

End Sub

[alihadi]

Thank you so much Paul for your promptly reply. the code you sent is very useful.

Regards.
Ali

[alihadi]

Dear Paul,


Can you make the numbers that you generated between 1 & 10 randomly be sometimes 3 numbers or 8 numbers or nothing every time you click run?
Thanks
Ali

[Paul_Hossler]

A random (0 - 10) number of random numbers (1 - number_of_randoms)?

Like this??




Option Explicit

Sub GenerateSomeRandomNumbers()
Dim iNumberOfRandoms As Long, iRandom As Long
Dim i As Long, iRow As Long, j As Long

ActiveSheet.Columns("A:C").Clear

iNumberOfRandoms = Application.WorksheetFunction.RandBetween(0, 10)

iRow = 4

For i = 1 To iNumberOfRandoms
iRandom = Application.WorksheetFunction.RandBetween(1, iNumberOfRandoms)

ActiveSheet.Cells(iRow, 1).Value = i
ActiveSheet.Cells(iRow, 2).Value = iRandom

For j = 1 To iRandom
ActiveSheet.Cells(iRow, 3).Value = 1
iRow = iRow + 1
Next j
Next i

End Sub

[alihadi]

Thanks Paul for the code.

Do you know how to generate random times between two times. For example: how to generate random times between (09:00 & 12:00) in VBA?

Regards.
Ali

[Paul_Hossler]

Since a 'Time' is the fractional part of a double counted from Jan 1 1990 something like this would work

There might be cleverer ways to do it




Option Explicit
Sub RandomTime()
Dim i As Long
Dim dStart As Double, dEnd As Double, dRandom As Double


dStart = CDbl(TimeSerial(6, 0, 0))
dEnd = CDbl(TimeSerial(10, 0, 0))

For i = 1 To 100
dRandom = dStart + dEnd * Rnd
ActiveSheet.Cells(i, 1).Value = CDate(dRandom)
ActiveSheet.Cells(i, 2).Value = Format(ActiveSheet.Cells(i, 1).Value, "hh:mm:ss")
Next I
End Sub

[alihadi]

Thanks Paul,

I want to generate random times between two times. For example how to generate random times between (14:00 & 16:00) in VBA?
the code you sent I did not get it, I think there something wrong with it. Please assist me.

Regards.
Ali

[alihadi]

Hi,

I want to sort numbers ascending without removing the blank cells.
Thanks
Ali

[Paul_Hossler]

I lost a term somehow when I was pasting it




Option Explicit
Sub RandomTime()
Dim i As Long
Dim dStart As Double, dEnd As Double, dRandom As Double


dStart = CDbl(TimeSerial(14, 0, 0))
dEnd = CDbl(TimeSerial(16, 0, 0))

For i = 1 To 100
dRandom = dStart + (dEnd - dStart) * Rnd
ActiveSheet.Cells(i, 1).Value = CDate(dRandom)
ActiveSheet.Cells(i, 2).Value = Format(ActiveSheet.Cells(i, 1).Value, "hh:mm:ss")
Next i
End Sub

Function OneRandomTime(Earliest As Date, Latest As Date) As Date
Dim dStart As Double, dEnd As Double, dRandom As Double


dStart = CDbl(Earliest)
dStart = dStart - Int(dStart)

dEnd = CDbl(Latest)
dEnd = dEnd - Int(dEnd)

dRandom = dStart + (dEnd - dStart) * Rnd

OneRandomTime = CDate(dRandom)
End Function

[alihadi]

Thank you so much Paul for the code.

Do you know how to sort numbers ascending without removing blank cells?

Regards.
Ali

[alihadi]

Hi,

Do you know how to sort numbers ascending with blank cells in VBA without removing blank cells?
I will be very grateful if you assist me

Regards.
Ali

[Paul_Hossler]

Probably

Please provide an example

[alihadi]

Hi Paul,

Lets say, I want to sort the numbers which are in column(A) to sorted ascending without removing the blank cells as in the column (D) as shown in the attached file.
Regards.
Ali

[Paul_Hossler]

I think I understand. The sheet 'Test' has a macro button to see




Option Explicit
Sub SortWithBlanks()
Dim iSortCol As Long, iDataCol As Long
Dim iSortColLast As Long, iDataColLast As Long, iLongestCol As Long
Dim iLastCol As Long, iRow As Long
Dim rSort As Range

iSortCol = 1
iDataCol = 4


Application.ScreenUpdating = False

With ActiveSheet
iSortColLast = .Cells(.Rows.Count, iSortCol).End(xlUp).Row
iDataColLast = .Cells(.Rows.Count, iDataCol).End(xlUp).Row
If iSortColLast >= iDataColLast Then
iLongestCol = iSortColLast
Else
iLongestCol = iDataColLast
End If

iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
iLastCol = iLastCol + 1

For iRow = 1 To iLongestCol
.Cells(iRow, iLastCol).Value = .Cells(iRow, iSortCol).Value
If Len(.Cells(iRow, iLastCol).Value) = 0 Then .Cells(iRow, iLastCol).Value = .Cells(iRow - 1, iLastCol).Value
Next iRow
Set rSort = .Cells(1, 1).Resize(iLongestCol, iLastCol)

.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Columns(iLastCol), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SetRange rSort
.Sort.Header = xlNo
.Sort.MatchCase = False
.Sort.Orientation = xlTopToBottom
.Sort.SortMethod = xlPinYin
.Sort.Apply

.Columns(iLastCol).ClearContents
End With
Application.ScreenUpdating = True

End Sub

[alihadi]

Hi,
Does anyone know how to generate random numbers without repetition in VBA excel.
Thanks
Ali

[mikerickson]

Yes.


Sub test()
Dim Numbers() As Long
Dim LowPossible As Long, HighPossible As Long, Size As Long
Dim randIndex As Long, temp As Long
Dim i As Long

LowPossible = 100: HighPossible = 120: Rem adjust

ReDim Numbers(LowPossible To HighPossible)
For i = LowPossible To HighPossible
Numbers(i) = i
Next i

For i = LowPossible To HighPossible
randIndex = WorksheetFunction.RandBetween(LowPossible, HighPossible)
temp = Numbers(i)
Numbers(i) = Numbers(randIndex)
Numbers(randIndex) = temp
Next i

Do
Size = Application.InputBox("How Many Numbers (no duplicates)", Default:=(HighPossible - LowPossible + 1), Type:=1)
If Size <= 0 Then Exit Sub: Rem canceled
If (HighPossible - LowPossible + 1) < Size Then
MsgBox "The most you can enter is " & (HighPossible - LowPossible + 1)
Size = 0
End If
Loop Until 0 < Size

With Range("a1")
.EntireRow.ClearContents
.Resize(1, Size).Value = Numbers
End With

End Sub

[Kenneth Hobs]

Yes. If you had said so after I asked in post #2, it would have saved a lot of time.

Sub Test_RndIntPick() MsgBox Join(RndIntPick(1, 100, 3), vbLf)
MsgBox Join(RndIntPick(1, 100, 3, True, True), vbLf)
MsgBox Join(RndIntPick(1, 100, 3, True, False), vbLf)
End Sub

Function RndIntPick(first As Long, last As Long, _
noPick As Long, Optional bSort As Boolean = False, _
Optional bAscending As Boolean = True) As Variant
Dim i As Long, r As Long, temp As Long, k As Long
ReDim iArr(first To last) As Variant
Dim a() As Variant

For i = first To last
iArr(i) = i
Next i

Randomize
For i = 1 To noPick
r = Int(Rnd() * (last - first + 1 - (i - 1))) + (first + (i - 1))
temp = iArr(r)
iArr(r) = iArr(first + i - 1)
iArr(first + i - 1) = temp
Next i

ReDim Preserve iArr(first To first + noPick - 1)
ReDim a(1 To noPick)
For r = 1 To noPick
a(r) = iArr(LBound(iArr) + r - 1)
Next r

If bSort = True Then
RndIntPick = ArrayListSort(a(), bAscending)
Else
RndIntPick = a()
End If
End Function

Function ArrayListSort(sn As Variant, Optional bAscending As Boolean = True)
With CreateObject("System.Collections.ArrayList")
Dim cl As Variant
For Each cl In sn
.Add cl
Next

.Sort 'Sort ascendending
If bAscending = False Then .Reverse 'Sort and then Reverse to sort descending
ArrayListSort = .toarray()
End With
End Function

[Paul_Hossler]

I'm confused




Post #1 -- I want to generate random numbers between (1 &10) in column B starting with B4 and generating ones based on the generated number in B4, for example: if number 5 was generated in B4 , generate 5 ones in range (C4 to C8), and in column A generate random numbers between (1 & 3) starting with A4.

Post #5 -- Can you make the numbers that you generated between 1 & 10 randomly be sometimes 3 numbers or 8 numbers or nothing every time you click run?

Post #8 -- Do you know how to generate random times between two times. For example: how to generate random times between (09:00 & 12:00) in VBA?

Post #12 -- I want to sort numbers ascending without removing the blank cells.

Post #22 -- Does anyone know how to generate random numbers without repetition in VBA excel.

[alihadi]

Thanks mikerickson for the code. But Can you generate random numbers between (1 & 4) without repetition without using MsgBox in VBA code and every click it shows different numbers and may shows one number, two numbers or three or four numbers or does not show any numbers? I attached a file to show how I want it.

Regards.
Ali

[snb]

Sub M_snb()
[A1:A4] = "=rand()"
[A1:A4] = [index(rank(A1:A4,A1:A4),)]
End Sub

[Aussiebear]

I'm confused

I don't blame you. Alihadi, please do not simply quote the previous reply. If it is necessary to quote a previous comment or part thereof, then add your comments in the same post as the quoted statements.

[Paul_Hossler]

Alihadi -- can you specifically say what you were asking for? I get the impression that you are looking for 5 or 6 different things, which keep changing

If you do have 5 or 6 different requests, it might be better to keep each one in a separate thread so people don't get confused

[mikerickson]

.. But Can you generate random numbers between (1 & 4) without repetition without using MsgBox in VBA code and every click it shows different numbers and may shows one number, two numbers or three or four numbers or does not show any numbers? ...

I think this will do that.
I agree that your request seems to be changing.

Sub test()
Dim Numbers() As Long
Dim Size As Long, temp As Long, randIndex As Long
Dim i As Long
Size = 4
ReDim Numbers(1 To Size)
For i = 1 To Size: Numbers(i) = i: Next i

For i = 1 To Size
randIndex = WorksheetFunction.RandBetween(1, Size)
temp = Numbers(i)
Numbers(i) = Numbers(randIndex)
Numbers(randIndex) = temp
Next i

randIndex = WorksheetFunction.RandBetween(0, Size)

With Range("A1")
.EntireColumn.ClearContents
If 0 < randIndex Then .Resize(randIndex, 1).Value = Application.Transpose(Numbers)
End With
End Sub

[alihadi]

Hi Paul,
I want to generate random numbers between (1 & 4) with no repetition and sort them ascending using VBA excel without removing blank cells? I want to generate the numbers randomly in random cells and sort them without removing the blank cells as shown in the attached excel file.
I will be very grateful if you assist me in this matter.

Thanks
Ali