Mr_Ragweed
Board Regular
- Joined
- Dec 10, 2012
- Messages
- 74
Hello all. I'm looking to do a number format on the data in a pivot table grouping. Currently when you Group the data each range is filled with decimals
(Excel 2010)
I would like the ranges to be 0.15-0.23 vs 0.152222222222222-0.232222222222222 which is what i get (by the way i dont want those exact ranges, this is just an example).
The grouping is done via a userform where the user inputs the "By" increments. I have tried inserting NumberFormat = "0.00" in various places but to no avail. "Start" and "End" are never known, therefore i just left them as true. Would a LEN statement of some kind where i just pull the data up to the first two decimals be the way to go? Each pivot table is obviously of different length and each time it is regrouped the length will be changed so i think grabbing the "End" would be difficult if even possible.
Sample code below:
(Excel 2010)
I would like the ranges to be 0.15-0.23 vs 0.152222222222222-0.232222222222222 which is what i get (by the way i dont want those exact ranges, this is just an example).
The grouping is done via a userform where the user inputs the "By" increments. I have tried inserting NumberFormat = "0.00" in various places but to no avail. "Start" and "End" are never known, therefore i just left them as true. Would a LEN statement of some kind where i just pull the data up to the first two decimals be the way to go? Each pivot table is obviously of different length and each time it is regrouped the length will be changed so i think grabbing the "End" would be difficult if even possible.
Sample code below:
Code:
Private Sub CmdRegroup_Click()
Dim FinalRow As Long
FinalRow = ActiveSheet.Cells(Application.Rows.Count, 1).End(xlUp).Row
If TextBox1.Value = True Then
If Range("I13").Value = "List" Then
Cells(4, 6).Select
Selection.Group Start:=True, End:=True, By:=TextBox1.Value
ElseIf Range("N13").Value = "List" Then
Cells(4, 11).Select
Selection.Group Start:=True, End:=True, By:=TextBox1.Value
ElseIf Range("J13").Value = "List" Then
Cells(4, 7).Select
Selection.Group Start:=True, End:=True, By:=TextBox1.Value
ElseIf Range("O13").Value = "List" Then
Cells(4, 12).Select
Selection.Group Start:=True, End:=True, By:=TextBox1.Value
End If
End If
Unload Me
End Sub