Note~ Sample file already attached with the mail which I sent on 18th
march 2011 for the same Subject Line.
HI Team,
I have written a code for selecting random data from master data.
Please find the checkpoints below.
Data Format:
Master Data will have n number of records.
Each Row will have 4 columns.
Each row in master data will have an Cite Count (i.e. Column 3 value)
as a Numeric value.
Requirements:
Random rows has to be selected from Master Data as per the below
request.
If the Cite Count (i.e. Column 3 value) is greater than 32 and Less
than 500, Then we have to select 32 rows randomly.
If the Cite Count (i.e. Column 3 value) is greater than 500 and Less
than 3200, Then we have to select 125 rows randomly.
If the Cite Count (i.e. Column 3 value) is greater than 3200 and Less
than 10000, Then we have to select 200 rows randomly.
If the Cite Count (i.e. Column 3 value) is greater than 10000 and Less
than 35000, Then we have to select 315 rows randomly.
There should not be repeated rows. (Random Data should not contain
duplicate rows.
Help Required for:
I have written the below code for the above requirement
Sub RandData()
Dim CiteCnt, Rcnt As Long
Dim RandCiteCnt As Integer
Worksheets("Sample_Audit").Activate
Worksheets("Random_Data").Range("A2:IV65536").Clear
CiteCnt = WorksheetFunction.Sum(Range("C:C"))
RandCiteCnt = 0
If CiteCnt >= 32 And CiteCnt <= 500 Then
Do While RandCiteCnt < 32
Sheets("Sample_Audit").Select
rndrow = Int(Selection.CurrentRegion.Rows.Count * Rnd + 1)
If rndrow = 1 Then
Exit Do
End If
Rcnt = Rows(rndrow).Cells(3).Value
Rows(rndrow).EntireRow.Select
Selection.Copy
Sheets("Random_Data").Select
lastrow = Range("A65536").End(xlUp).Row + 1
Range("A" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Paste
RandCiteCnt = RandCiteCnt + Rcnt
Loop
ElseIf CiteCnt >= 501 And CiteCnt <= 3200 Then
Do While RandCiteCnt >= 125
Sheets("Sample_Audit").Select
rndrow = Int(Selection.CurrentRegion.Rows.Count * Rnd + 1)
Rcnt = Rows(rndrow).Cells(3).Value
Rows(rndrow).EntireRow.Select
Selection.Copy
Sheets("Random_Data").Select
lastrow = Range("A65536").End(xlUp).Row + 1
Range("A" & lastrow).Select
ActiveSheet.Paste
RandCiteCnt = RandCiteCnt + Rcnt
Loop
ElseIf CiteCnt >= 3201 And CiteCnt <= 10000 Then
Do While RandCiteCnt <= 200
Sheets("Sample_Audit").Select
rndrow = Int(Selection.CurrentRegion.Rows.Count * Rnd
+ 1)
Rcnt = Rows(rndrow).Cells(3).Value
Rows(rndrow).EntireRow.Select
Selection.Copy
Sheets("Random_Data").Select
lastrow = Range("A65536").End(xlUp).Row + 1
Range("A" & lastrow).Select
ActiveSheet.Paste
RandCiteCnt = RandCiteCnt + Rcnt
Loop
ElseIf CiteCnt >= 10001 And CiteCnt <= 35000 Then
Do While RandCiteCnt <= 315
Sheets("Sample_Audit").Select
rndrow = Int(Selection.CurrentRegion.Rows.Count * Rnd
+ 1)
Rcnt = Rows(rndrow).Cells(3).Value
Rows(rndrow).EntireRow.Select
Selection.Copy
Sheets("Random_Data").Select
lastrow = Range("A65536").End(xlUp).Row + 1
Range("A" & lastrow).Select
ActiveSheet.Paste
RandCiteCnt = RandCiteCnt + Rcnt
Loop
End If
End Sub
I could not get the exact output from the above coding as it results
the below:
The macro is terminated before the required random data requirement
exists.
Its selecting Duplicate rows.
It would be helpful if the above code had been evaluated and
redesigned to get the Required Output.
I have attached the sample documents for the above issue for your
reference.
WIth Thanks in advance,
R. K. M.
[email protected]
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
