Looping Through Visible Cells While Autofilling Several Values

  • Hello there. Longtime listener, first-time caller.

    I apologize in advance but am unable to upload any snippets due to proprietary information.

    What I'm trying to solve for: We're looking to have the user select a group of cells (all visible, may be filtered or unfiltered), Excel count the number of selected cells, and place values A->D in the selection based on pre-determined proportions.

    Number selected: 45
    Value = A for the first (45 x 0.5) rows (here, 23)
    Value = B for the next (45 x 0.15) rows (here, 7)
    Value = C for the next (45 x 0.1) rows (here, 5)
    Value = D for the final (45 x 0.25) rows (here, 11)
    ** If the 23+7+5+11 > 45, Value D would be cut down to 10 due to potential rounding issues.

    Ultimately, based on the above, rows 2->24 would be A, rows 25->31 would be B, and so on. All done via a single (or set of) loop(s).

    Number selected will differ each time along with whether it's filtered or unfiltered. So I imagine CellType.Visible will come into play at some point.

    I already have the counts & proportions worked out (shown below). It's really the Do Loop construction that's been frying my brain. Amateur troubleshooter with VBA here & Googling has only gotten so far to this point.

    Any help would be greatly appreciated! Please let me know if you would have any questions on the above.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!