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.
Sub Dummying() 'Keyboard Shortcut: ctrl-d Dim SelectionCount As Long SelectionCount = Selection.Count Dim Value1Count As Long Value1Count = Application.Round(SelectionCount * 0.5, 0) Dim Value2Count As Long Value2Count = Application.Round(SelectionCount * 0.15, 0) Dim Value3Count As Long Value3Count = Application.Round(SelectionCount * 0.1, 0) Dim Value4Count As Long Value4Count = Application.Round(SelectionCount * 0.25, 0) If Value1Count + Value2Count + Value3Count + Value4Count > SelectionCount Then Value4Count = Application.Round(SelectionCount * 0.25, 0) - 1 End If End Sub