CODEWITHSUNDEEP
excelvba
Renin Mathew
Renin Mathew

Reputation: 47

VBA to enter value into top X visible cells of filtered data within a loop

I am working on sampling solution for my QA team where the production file on excel is randomized and a sample of each user is considered for QA purposes. I need to loop through the visible cells of a filtered range and select the top x (variable defined) rows. I am unable to paste the code I have because of the restrictions on my client's systems so just going to type out the code on the exact area I'm stuck at. I've tried different combinations on the loop below but either all visible cells are getting the value "QA Selected" or it is just the first row. I only need it for the number of rows as per the sample. The For Each cell in range section is not stopping once the sample count is met.

For example, if user John123 has processed 500 alerts on a certain day, my sample of 10% would mean 50 randomly selected alerts. On any given day I have about 20 different users processing alerts. My code currently picks the data, randomizes it, turns on autofilter and loops through each user's production data. I just need VBA to select the top 50 (in this case) alerts i.e visible rows by entering "QA Selected" into the cell. The code would then filter on the next user and select the top 30 alerts (if production is 300 alerts) and so on.

       Set filter_rng = Sheets("PS_Extract").Range("AL2:AL" & lastrow2)
 
       For i = 1 to lastrow
         Sheets("PS_Extract").Range("A:AL").Autofilter
         Sheets("PS_Extract").Range("A:AL").Autofilter field:=21, Criteria:=Sheets("Notes").Range("A") & i)

     SampleSize = Sheets("Notes").Range("C" & i)
     
     For each cell in filter_rng.SpecialCells(xlCellTypeVisible) 'this loop is the block where I am facing the issue
        For j = 1 to SampleSize
           Sheets("PS_Extract").Range("AL" & cell.Row).Value = "QA Selected"
           j = j + 1
           If j = SampleSize Then Exit For
        Next j
     Next cell

Upvotes: 1

Views: 1109

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

After you made sure that SampleSize is an integer value (a whole number), you could try the following:

j = 0
For Each cell In filter_rng.SpecialCells(xlCellTypeVisible)
   j = j + 1
   Sheets("PS_Extract").Range("AL" & cell.Row).Value = "QA Selected"
   If j = SampleSize Then Exit For ' maybe '> SampleSize - 1' if not integer
Next cell

When counting inside an inner loop, you have to reset the counter (j = 0).

Upvotes: 2

Related Questions