Split Range

[edwardj3]

Hi all,
Searched a number of forums/website but can't find a solution to my issue.

I have the following range examples. I have split the string field below to create start and end fields. Match to identify if numbers match and the difference between the two numbers.

[TABLE="width: 602"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]Range[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]match[/TD]
[TD]Difference[/TD]
[/TR]
[TR]
[TD]start 409795 - End 409796 (string field)[/TD]
[TD]409795[/TD]
[TD]409796[/TD]
[TD]FALSE[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]start 556919 - End 556931[/TD]
[TD]556919[/TD]
[TD]556931[/TD]
[TD]FALSE[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]start 559687 - End 559723[/TD]
[TD]559687[/TD]
[TD]559723[/TD]
[TD]FALSE[/TD]
[TD]36[/TD]
[/TR]
</tbody>[/TABLE]

What I want is to create a macro to return the following result:


[TABLE="width: 666"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Range[/TD]
[TD]start[/TD]
[TD]end[/TD]
[TD]match[/TD]
[TD]Difference[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]start 409795 - End 409795[/TD]
[TD]409795[/TD]
[TD]409795[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[TD]2 lines in total[/TD]
[/TR]
[TR]
[TD]start 409796 - End 409796[/TD]
[TD]409796[/TD]
[TD]409796[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 556919 - End 556919[/TD]
[TD]556919[/TD]
[TD]556919[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[TD]12 lines in total[/TD]
[/TR]
[TR]
[TD]start 556920 - End 556920[/TD]
[TD]556920[/TD]
[TD]556920[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 556921 - End 556921[/TD]
[TD]556921[/TD]
[TD]556921[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 556922 - End 556922[/TD]
[TD]556922[/TD]
[TD]556922[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 5"]Through to below[/TD]
[/TR]
[TR]
[TD]start 556931 - End 556931[/TD]
[TD]556931[/TD]
[TD]556931[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 559687 - End 559687[/TD]
[TD]559687[/TD]
[TD]559687[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[TD]36 lines in total[/TD]
[/TR]
[TR]
[TD]start 559688 - End 559688[/TD]
[TD]559688[/TD]
[TD]559688[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 559689 - End 559689[/TD]
[TD]559689[/TD]
[TD]559689[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 559690 - End 559690[/TD]
[TD]559690[/TD]
[TD]559690[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 559691 - End 559691[/TD]
[TD]559691[/TD]
[TD]559691[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="colspan: 5"]Through to below[/TD]
[/TR]
[TR]
[TD]start 559722 - End 559722[/TD]
[TD]559722[/TD]
[TD]559722[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]start 559723 - End 559723[/TD]
[TD]559723[/TD]
[TD]559723[/TD]
[TD="align: center"]TRUE[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Think I would need to macro to initially find where the numbers don't match, insert the required number of blank lines and then populate with increments of one until all the numbers in start and end match and then move on - probably needs some loop.

so for range start 409795 - End 409796 I would need 1 line for Start 409795 - End 409795 and another line for Start 409796 - End 409796

Hope this makes sense. Be grateful for a solution. Happy to provide further details if required.

Thanks

 

[mse330]

Hey edwardj3

Try the below code ... Assuming your data is in columns A to E and the output will be in the same sheet in columns H to L

Sub edwardj3()

Dim lRow As Long, Diff As Long, Start As Long
lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

For x = 2 To lRow
Start = Cells(x, 2)
Diff = Cells(x, 5) + 1
    For y = 1 To Diff
        lRow = ActiveSheet.Range("H" & Rows.Count).End(xlUp).Row + 1
        Cells(lRow, 8) = "start " & Start & " - End " & Start
        Cells(lRow, 9) = Start
        Cells(lRow, 10) = Start
        Cells(lRow, 11) = "TRUE"
        Cells(lRow, 12) = 0
        Start = Start + 1
    Next y
Next x

End Sub