Random Sampler Macro

[rangudu_2008]

Hi,

I've coded a macro where i can pick out specific number of sample rows based on the user input. I've coded this using the rand() function, so it picks and gives me the specific number of rows required in a new sheet.

I want to add a little bit more functionality into this code (for which i need some help) and make it pick specific number of rows calculated at runtime (not input by the user). i.e. I want the code to automatically choose rows randomly based on a specific rate (%) (say 5% or 10%)

I've attached two files.. The dump file contains the data that needs to be sampled and the other file is the macro code.

I've also one other thing to be taken care of. The dump sheet which contains the the source data has a specific column called LAST_UPDATED_NAME. I actually need to sample out specific number of rows for each name, based on the percentage. My code actually gives out a random set of rows in a separate sheet (which needs to be tweaked a bit) which needs to be chosen randomly at a different rate for each person and should be collated into one single sheet finally when the macro stops execution.

Can someone help me to code in such a functionality??


FYI.. I coded this macro in Excel 2003, but now i'm using Excel 2007.

Ranga

[rangudu_2008]

Hi,

Just got this sheet merging code from one of the KB articles from VBAX:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=151

This would take care of the second part in my macro. Would anyone be able to help me out to code for choosing rows randomly based on a specific sampling rate (%) which can be set as per user's requirement?

Currently, the rate i would require to sample for the user names under LAST_UPDATED_NAME field falls into 2 different categories – one EXISTING and another NEW. Samples taken for NEW staff will be @ 5% and for EXISTING staff samples will be taken @ 2.5%

Ranga

[rangudu_2008]

I've coded a module called Filter_N_Transfer which can be used to export any number of filtered records from a excel worksheet. Filters can be upto any level.

I need some help to go about integrating this module with the Random Sampling module so as to choose a specific % of records that can be filtered out to another sheet.


Ranga

[rangudu_2008]

Hi,

Is it possible to random choose x no. of rows from a set of filtered records in a worksheet? Here, x is a whole number which will be calculated at runtime.

I'm able to pick out and identify rows that are filtered with the code below, but i need some help to tweak it a bit to suit my requirement.

How can it be done? Can some help me?

[vba]
Sub FilterTest()
Dim rngFilter As Range
Dim rngVisible As Range

'Is there an autofilter on Worksheet?
If Sheet1.AutoFilterMode Then
'Are any filters being used?
If Sheet1.FilterMode Then
'Get a reference to the autofilter range
'Exclude Header row
With Sheet1.AutoFilter.Range
Set rngFilter = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count)
End With
End If
End If

If rngFilter Is Nothing Then
MsgBox "No Filter has been applied!"
Else
'Find the visible cells, if there are none then an error will be raised
On Error Resume Next
Set rngVisible = rngFilter.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If rngVisible Is Nothing Then
MsgBox "There are no visible cells!"
Else
MsgBox rngVisible.Address 'Needs tweaking here
End If
End If
End Sub
[/vba]


Ranga