Start & End Number Further Converted

[Rick Rothstein]

Thanks for your reply, second set can not always be totalling to 100000, it might be less

Okay, if that is the case, then how do we know what number to total up to in order to determine the ranges? Is the number being stored in a cell somewhere (if so, where)?

[ayazgreat]

Okay, if that is the case, then how do we know what number to total up to in order to determine the ranges? Is the number being stored in a cell somewhere (if so, where)?

All numbers are stored from column a to b with their Qty in column c and this the query to be developed in code to to determine start range and end range of the same number as I mentioned in attached sheet in two different examples.

[Rick Rothstein]

Give the following macro a try and see if you can make use of it...

Sub StartEndRanges()
  Dim X As Long, TargetNumber As Long, NumberOfRows As Long, LastRow As Long, Increment As Long, DestinationStartCell As Range
  Const FixedQty As Long = 400
  Const StartRow As Long = 2
  TargetNumber = Application.InputBox("What quantity total do you want ranges for (note... must be a multiple of 400)?", Type:=1)
  If TargetNumber = 0 Or TargetNumber Mod 400 <> 0 Then
    MsgBox "The number """ & TargetNumber & """ is not valid!", vbExclamation
  End If
  On Error GoTo NoCell
  Set DestinationStartCell = Application.InputBox("Please select the start cell for output?", Type:=8)
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  NumberOfRows = TargetNumber \ FixedQty
  With DestinationStartCell
    .Resize(, 3).Merge
    .Value = "Result After Macro: " & TargetNumber & " or less"
    .HorizontalAlignment = xlHAlignCenter
    .Interior.ColorIndex = 48
    .Font.Bold = True
    With .Offset(1).Resize(, 3)
      .Value = Array("Start Range", "End Range", "Qty")
      .Interior.ColorIndex = 15
      .HorizontalAlignment = xlHAlignCenter
      .Font.Bold = True
      .ColumnWidth = 15
    End With
  End With
  For X = StartRow To LastRow Step NumberOfRows
    DestinationStartCell.Offset(2 + Increment).Value = Cells(X, "A").Value
    DestinationStartCell.Offset(2 + Increment).Offset(, 1).Value = Cells(X + NumberOfRows - 1, "B").Value
    DestinationStartCell.Offset(2 + Increment).Offset(, 2).Value = WorksheetFunction.Sum(Cells(X, "C").Resize(NumberOfRows))
    Increment = Increment + 1
  Next
  DestinationStartCell.Offset(1 + Increment).Offset(, 1).Value = Cells(LastRow, "B").Value
NoCell:
End Sub

Note: The code will ask you for the quantity total you want the list broken down by (you would 20000 for your first chart, 100000 for your second chart) and the starting cell (top left corner) to output the chart to (which does not have to be on Row 1 if you do not want it to be).

[ayazgreat]

Thank you very Rick Rothstein
It works really great , more than my expectation.

[Rick Rothstein]

Thank you very Rick Rothstein
It works really great , more than my expectation.

You are quite welcome... I am glad I was able to be of help to you.

[ayazgreat]

Rick Rothstein
I want to ask something more regarding above post that if Column a & b Start & End number but Column C Qty of each range is 20000 and I want to convert each range from col a to c same into 400 as in previouse code we have qty in col c and we can convert them to 20000 or more but Now case is oposite it.

Thanks in advance