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.

Range	start	end	match	Difference
start 409795 - End 409796 (string field)	409795	409796	FALSE	1
start 556919 - End 556931	556919	556931	FALSE	12
start 559687 - End 559723	559687	559723	FALSE	36

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

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

Range	start	end	match	Difference
start 409795 - End 409795	409795	409795	TRUE	0	2 lines in total
start 409796 - End 409796	409796	409796	TRUE	0
start 556919 - End 556919	556919	556919	TRUE	0	12 lines in total
start 556920 - End 556920	556920	556920	TRUE	0
start 556921 - End 556921	556921	556921	TRUE	0
start 556922 - End 556922	556922	556922	TRUE	0
Through to below
start 556931 - End 556931	556931	556931	TRUE	0
start 559687 - End 559687	559687	559687	TRUE	0	36 lines in total
start 559688 - End 559688	559688	559688	TRUE	0
start 559689 - End 559689	559689	559689	TRUE	0
start 559690 - End 559690	559690	559690	TRUE	0
start 559691 - End 559691	559691	559691	TRUE	0
Through to below
start 559722 - End 559722	559722	559722	TRUE	0
start 559723 - End 559723	559723	559723	TRUE	0

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

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