Start & End Number Further Converted

[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

[ayazgreat]

Dear Rick Rothstein

The macro is giving error , I am attaching here a workbook with expected result.