hi Saarang,

Thank you for identifying & providing the link to the crosspost -
hopefully we can help.




Saarang84;740535 Wrote:

...


VBA Code:
--------------------


Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible) = "Sample_" & i
--------------------



I want to mark only certain number of rows (stored in the variable
randRow) as samples. But the above line of code marks all the rows
filtered. How can this be fixed??



I don't think it's very likely but does the below work?


VBA Code:
--------------------


Resize(.Rows.Count - 1, 1).Offset(i, fCol).SpecialCells(xlCellTypeVisible).resize(randR ow,1).value2 = "Sample_" & i
--------------------



If not, then you may have to loop through each area within the range of
visible cells until you get to the number of rows represented by
randRow. I'll look through your code in detail over the weekend & see if
I can come up with an answer for you...

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

Hi Rob,

I've checked out your code and it doesn't fix this requirement.

Out of the total number of rows filtered, say y, i need to choose x
rows *randomly* (where x<y). This x rows to be marked are stored in
-randRow-. The below line of code marks all the rows filtered. But i
need only x rows to be marked.


Sarang


--
Saarang84
------------------------------------------------------------------------
Saarang84's Profile: http://www.thecodecage.com/forumz/member.php?u=2386
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

Saarang84 expressed precisely :
Hi Rob,

I've checked out your code and it doesn't fix this requirement.

Out of the total number of rows filtered, say y, i need to choose x
rows *randomly* (where x<y). This x rows to be marked are stored in
-randRow-. The below line of code marks all the rows filtered. But i
need only x rows to be marked.


Sarang

How about using a minimum row and a maximum row to construct your list
of filtered rows, then randomly choosing rows from that list? It would
require building a delimited string of the filtered row numbers, and
testing the string for your random row numbers using the InStr()
function. The random numbers would be created by a function that takes
a Min and a Max number, and then returns a random number between those.

Would this work for you?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

hi Sarang,


Saarang84 Wrote:

Hi Rob,
Thanks for the reply. Your piece of code doesn't work.
'Filter Randomly based on Inputs' (http://tinyurl.com/2uqrugj)
Hope this thread (coded by mikerickson) helps...
Sarang


Please keep the responses in the thread so that any other helpers or
people looking for answers can also see the same information.



Out of the total number of rows filtered, say y, i need to choose x
rows randomly (where x<y). This x rows to be marked are stored in
randRow. The below line of code marks all the rows filtered. But i need
only x rows to be marked.


Do you want x rows marked from each time the filter changes on the
below lines of code?
Or do you want a total of x rows (after all the looping is done)?



VBA Code:
--------------------


For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?u=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

broro183;740587 Wrote:

hi Sarang,


Please keep the responses in the thread so that any other helpers or
people looking for answers can also see the same information.


Do you want x rows marked from each time the filter changes on the
below lines of code?
Or do you want a total of x rows (after all the looping is done)?



VBA Code:
--------------------


For i = 1 To 10
Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




Rob



Hi Rob,

Based on the number of rows filtered (by the below line) the value of x
is calculated. Out of the total no. of rows filtered by this line, these
x rows are to be marked as samples *randomly*.



VBA Code:
--------------------


For i = 1 To 10
Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




For example, when i=1, say the number of rows filtered may be 200, then
x=10 rows (5% of 200), need to be marked as samples. When i=2, say the
number of rows filtered is 150, then If x=7.5 rows (5% of 150) or 8 rows
(ceiling value) need to be marked as samples randomly. This 5% of total
visible rows is just an example, actually its z% of total visible rows
in the filter from which x rows are calculated and to be chosen
randomly.

Hope this clarifies better.

Since this is a test data, i use a for loop (from 1 to 10) and filter
each time for 1,2 ...10. Actually, my original data contains a list of
names in an array (called names(i), and i=1 to n, for n names in
general)


Sarang


--
Saarang84
------------------------------------------------------------------------
Saarang84's Profile: http://www.thecodecage.com/forumz/member.php?u=2386
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207872

http://www.thecodecage.com/forumz


--- news://freenews.netfront.net/ - complaints: ---

With a random row chooser of course!


On Sat, 4 Sep 2010 19:15:32 +0100, broro183
wrote:


hi Sarang,


Saarang84 Wrote:

Hi Rob,
Thanks for the reply. Your piece of code doesn't work.
'Filter Randomly based on Inputs' (http://tinyurl.com/2uqrugj)
Hope this thread (coded by mikerickson) helps...
Sarang


Please keep the responses in the thread so that any other helpers or
people looking for answers can also see the same information.



Out of the total number of rows filtered, say y, i need to choose x
rows randomly (where x<y). This x rows to be marked are stored in
randRow. The below line of code marks all the rows filtered. But i need
only x rows to be marked.


Do you want x rows marked from each time the filter changes on the
below lines of code?
Or do you want a total of x rows (after all the looping is done)?



VBA Code:
--------------------


For i = 1 To 10
.Columns("A:J" & Cells(Rows.Count, 1)).AutoFilter field:=1, Criteria1:="=" & i
--------------------




Rob

On 9/5/2010 12:35 AM, Spurious Response wrote:
With a random row chooser of course!

I have to admit the same thing occurred to me, e.g.

http://www.techonthenet.com/excel/formulas/rnd.php

MS Excel: Rnd Function (VBA only)

In Excel, the Rnd function allows you to generate a random number
(integer value). You can specify the random number to be a value between
2 user-specified numbers.

The syntax for the Rnd function is:

Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)

upperbound is the highest value that the random number can be.
lowerbound is the lowest value that the random number can be.

Applies To: Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Int ((6 - 1 + 1) * Rnd + 1) 'return random # between 1 and 6.
Int ((200 - 150 + 1) * Rnd + 150) 'return random # between 150 and 200
Int ((999 - 100 + 1) * Rnd + 100) 'return random # between 100 and 999

VBA Code

The Rnd function can only be used in VBA code. For example:

Dim LRandomNumber As Integer
LRandomNumber = Int ((300 - 200 + 1) * Rnd + 200)

In this example, the variable called LRandomNumber would now contain a
random number between 200 and 300.