Start & End Number Further Converted

[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

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

[Rick Rothstein]

Dear Rick Rothstein

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

	A	B	C
1	Start Range	End Range	Qty
2	1000394600	1000414599	20000
3	1000825600	1000845599	20000
4	1002832000	1002851999	20000

In the above snapshot from your worksheet, the two highlighted ranges overlap... is that correct? I have assumed (even in my first posted code) that the ranges link together one-to-the-other without an breaks in the ranges. Are you telling us that the ranges do not have to be contiguous throughout the table? If so, then I do not think my original code actually works for your first posted question. Please let us know about your ranges and whether they can be non-contiguous or overlap.

[ayazgreat]

Dear Rick Rothstein
In my all posts from starting to end all ranges data provided by me in breaks , in both posts case ranges do not have contiguous throughout the table.They can be non-contiguous or overlap

[Rick Rothstein]

In my all posts from starting to end all ranges data provided by me in breaks , in both posts case ranges do not have contiguous throughout the table.They can be non-contiguous or overlap

Sorry about the delay in getting back to you... I had some personal stuff to take care of.

Okay, I think I am confused. Your first posting showed ranges that were continuous, without breaks or overlaps, and you indicated the code I gave you worked fine for you. Now you are showing ranges with breaks and overlaps... is this a separate question, using different source data than than you showed us in your first message. If not, that is, if your data for the first question can also have breaks and overlaps, then the function I posted (which you said works for you) does not really work correctly. If you are now asking for help with a similar looking data structure, but one that is different from the first in that it can have breaks and overlaps in its ranges, then I need to know this. The problem I have right now is in figuring out if I have to fix my first function or not. Can you please clarify all of this for me?

[ayazgreat]

Sorry about the delay in getting back to you... I had some personal stuff to take care of.

Okay, I think I am confused. Your first posting showed ranges that were continuous, without breaks or overlaps, and you indicated the code I gave you worked fine for you. Now you are showing ranges with breaks and overlaps... is this a separate question, using different source data than than you showed us in your first message. If not, that is, if your data for the first question can also have breaks and overlaps, then the function I posted (which you said works for you) does not really work correctly. If you are now asking for help with a similar looking data structure, but one that is different from the first in that it can have breaks and overlaps in its ranges, then I need to know this. The problem I have right now is in figuring out if I have to fix my first function or not. Can you please clarify all of this for me?

Hi Rick
How are you ? Have a good day.
As I have said before that in my first post ranges are in breaks , you can see my first attached sheet ranges and you will find there ranges in breaks, and come to your 2nd question about having result after running your code , you are right in saying that your code doesn't do right work on break ranges, that was time I did not see result carefully but seen when you mentioned about it.
Now could It b possible to correction in both result?

Thanks in advance
Ayaz

[Rick Rothstein]

Hi Rick
How are you ? Have a good day.
As I have said before that in my first post ranges are in breaks , you can see my first attached sheet ranges and you will find there ranges in breaks, and come to your 2nd question about having result after running your code , you are right in saying that your code doesn't do right work on break ranges, that was time I did not see result carefully but seen when you mentioned about it.
Now could It b possible to correction in both result?

Okay, thanks for the confirmation. I might be able to handle both situations with a single macro. Be patient and give me a little time to work out the details... I'll be back with a solution as soon as I am able to develop one.