Start & End Number Further Converted

[ayazgreat]

Hi

I need you help regarding to attached file example data , there are different start and end numbers with each qty equal to 400 , I want them to be further converted into Starat and End Range as mentioned in example attached sheet.

Thanks in advance

[Excel Fox]

Your first set is clear. Can be done. In your second set, how do you decide that a series has started and ended?

[ayazgreat]

Your first set is clear. Can be done. In your second set, how do you decide that a series has started and ended?

Because data is sorted and in ascending order you see the same in second example start and end range data is matched a and Qty of each range is qual to first example

[Rick Rothstein]

Your first set is clear. Can be done. In your second set, how do you decide that a series has started and ended?

I think the question Excel Fox wants to know is... will the second set always be totalling to 100000 or less (you don't say that in its header)?

I have a question of my own for you... Is the 400 shown down Column C always 400 in every cell or will those numbers vary down the column in "real life"?

[ayazgreat]

I think the question Excel Fox wants to know is... will the second set always be totalling to 100000 or less (you don't say that in its header)?

I have a question of my own for you... Is the 400 shown down Column C always 400 in every cell or will those numbers vary down the column in "real life"?

Thanks for your reply, second set can not always be totalling to 100000, it might be less
And column c is always 400, you can say Qty or count is always equal to 400 in Column c

[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.

[Excel Fox]

Are you saying that you now want to generate the input from the output?

[ayazgreat]

Yes Kris, you are absolultly right now I want each 20000 range (col a to c) to be converted into 400 range of different series.

[Rick Rothstein]

Yes Kris, you are absolultly right now I want each 20000 range (col a to c) to be converted into 400 range of different series.

Okay, give the following macro a try. You will be asked 3 questions. The first question asks you to select the cell with the first "Start Range" number in it. In your example case, that would be cell A2 or cell F3 or cell J3 depending on which chart you are going to reference. The second question asks you to input the quantity to break the chart out by (same as asked for in my original code). The third question asks you to select the first cell to start the chart at (again, same as asked for in my original code).

Sub StartEndRanges()
  Dim X As Long, TargetQty As Long, StartRow As Long, LastRow As Long, RangeStart As Long, RangeEnd As Long
  Dim TotalQty As Double, NumberOfFullRows As Long, StartRangeCell As Range, DestinationStartCell As Range
  On Error GoTo NoCell
  Set StartRangeCell = Application.InputBox("Select 1st Start Range cell in table to convert from.", Type:=8)
  StartRow = StartRangeCell.Row
  LastRow = StartRangeCell.End(xlDown).Row
  RangeStart = StartRangeCell.Value
  RangeEnd = StartRangeCell.Offset(LastRow - StartRow, 1).Value
  TotalQty = WorksheetFunction.Sum(StartRangeCell.Offset(, 2).Resize(LastRow - StartRow + 1))
  TargetQty = Application.InputBox("What quantity do you want for each ranges", Type:=1)
  If TargetQty <= 0 Or TargetQty Like "*[!0-9]*" Then
    MsgBox "The number """ & TargetQty & """ is not valid!", vbExclamation
    Exit Sub
  End If
  Set DestinationStartCell = Application.InputBox("Please select the start cell for output?", Type:=8)
  On Error GoTo 0
  NumberOfFullRows = TotalQty \ TargetQty
  With DestinationStartCell
    .Resize(, 3).Merge
    .Value = "Result After Macro: " & TargetQty & " 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
    .Resize(NumberOfFullRows).Offset(2, 2).Value = TargetQty
    If TotalQty - NumberOfFullRows * TargetQty Then
      Cells(.Row + NumberOfFullRows + 2, .Column + 2).Value = TotalQty - NumberOfFullRows * TargetQty
    End If
    For X = 0 To NumberOfFullRows + (TotalQty = NumberOfFullRows * TargetQty)
      Cells(.Row + X + 2, .Column).Value = RangeStart + X * TargetQty
      Cells(.Row + X + 2, .Column + 1).Value = Cells(.Row + X + 2, .Column).Value + _
                                               Cells(.Row + X + 2, .Column + 2).Value - 1
    Next
    If TotalQty > NumberOfFullRows * TargetQty Then
      With Cells(.Row + NumberOfFullRows + 2, .Column + 1)
        .Value = .Value + Cells(.Row + NumberOfFullRows + 2, .Column + 2).Value
      End With
    End If
  End With
NoCell:
End Sub