Announcement

Collapse
No announcement yet.

Pick 5 Random Numbers from each Day

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pick 5 Random Numbers from each Day

    Hi Programmers,

    I have a file with 5 days data, where in the data in column B is unique value and the last column have the date.

    I have a part of code where in it will ask for the number of random numbers you want ? if i give 5

    My requirement is, i need to pick the 5 random values from column B from each single date.

    Sample Data file attached. could you please help me with a vba code to get the result. Sample result is also mentioned in the file.
    Attached Files

  • #2
    Re: Pick 5 Random Numbers from each Day

    I have a part of code where in it will ask for the number of random numbers you want ?
    I don't see this code in your sample file. Please provide so that we can amend it with the ask.
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      Re: Pick 5 Random Numbers from each Day

      Here is the part of my code, which is picking 2 or more random numbers from a same date.
      Code:
      Sub Random_Values()
          Dim Row_Count As Long
          Dim HowMany As Long
          Dim i As Long
          Dim Arr() As String
          Dim Msg As String
          Dim ireply As Integer
          
          Windows("Required Data.xlsx").Activate
          Sheets("Sheet1").Select
          Row_Count = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1
          HowMany = InputBox("How Many Express #'s You Want to Audit?")
          Msg = "Are you Sure, You want to Audit " & HowMany & " Express #'s ?"
          
          ireply = MsgBox(Msg, vbQuestion + vbYesNo)
          Select Case ireply
              Case vbYes
                  ReDim Arr(1 To Row_Count)
                  For i = 1 To Row_Count
                      Arr(i) = ActiveSheet.Cells(i, 2).Value
                  Next i
                  Call Scramble(Arr)
                  For i = 1 To HowMany
                      ActiveCell.Offset(i, 7).Value = Arr(i)
                  Next i
                  Call Audit_Data
              Case vbNo
                  Call Random_Values
              End Select
      End Sub
      Public Sub Scramble(InOut() As String)
          Dim i As Long, j As Long
          Dim Temp
          
          ReDim ExpressNos(LBound(InOut) To UBound(InOut)) As Double
          
          Randomize
              For i = LBound(ExpressNos) To UBound(ExpressNos)
              ExpressNos(i) = Rnd()
          Next
          
          j = (UBound(ExpressNos) - LBound(ExpressNos) + 1) / 2
          
          Do While j > 0
              For i = LBound(ExpressNos) To UBound(ExpressNos) - j
                  If ExpressNos(i) > ExpressNos(i + j) Then
                      Temp = ExpressNos(i)
                      ExpressNos(i) = ExpressNos(i + j)
                      ExpressNos(i + j) = Temp
                      Temp = InOut(i)
                      InOut(i) = InOut(i + j)
                      InOut(i + j) = Temp
                  End If
              Next i
                  
              For i = UBound(ExpressNos) - j To LBound(ExpressNos) Step -1
                  If ExpressNos(i) > ExpressNos(i + j) Then
                      Temp = ExpressNos(i)
                      ExpressNos(i) = ExpressNos(i + j)
                      ExpressNos(i + j) = Temp
                      Temp = InOut(i)
                      InOut(i) = InOut(i + j)
                      InOut(i + j) = Temp
                  End If
              Next i
              j = j / 2
          Loop
      End Sub

      Comment


      • #4
        Re: Pick 5 Random Numbers from each Day

        Hi Alan,

        Any Luck Here ?

        Comment


        • #5
          Re: Pick 5 Random Numbers from each Day

          Hi All,

          I have made a code for this query and it got executed successfully.

          Thanks for your time. But, I would suggest all the programmer here to know the importance of the query and try to reply at least to the thread, so that we will be assuming that someone is looking into it.

          Comment


          • #6
            Re: Pick 5 Random Numbers from each Day

            This is an all volunteer site. No one here gets paid. We answer questions when we have time. If you have an urgent request, then you should seek professional paid help. We work in all time zones and while you may be awake, a person interested in your issue may be working or asleep. Patience is of the utmost importance when posting to this site. Remember the advise and help you get here is free and if time is of the essence this may not be the place to seek help.

            We look forward to your continued participation in this forum.
            Alan

            Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
            FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

            If someone has helped you, say "thank you" by clicking on the Like Button.

            Comment

            Working...
            X