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.
Example:
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.
Thanks,
Alex
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