Separating zipcode range

[holliday50]

I need VB code to extrapolate the below origin & destination zip codes to show ALL possible combinations, with each combination being a separate row.

Origin Zip Dest Zip V1 V2
350-359,362 350-359,362 $1.00 $250
350-359,362 360-361,363-369 $1.00 $250


In the above example, my result should start as follows:

Ozip Dzip V1 V2
350 350 $1.00 $250
350 351 $1.00 $250
350 352 $1.00 $250
350 353 $1.00 $250

Please advise if you have any ideas. Thanks in advance for your help.

 

[JimmyG]

This is not quite the same, but maybe gives you some ideas in the right direction...

R3, R4, R7-R12, R16, R18

becomes....

R3, R4, R7, R8, R9, R10, R11, R12, R16, R18



Found the code somewhere on this forum....


Function ExpandData(sStr As String) As String
Dim oMatches As Object, oMatch As Object

With CreateObject("VBSCRIPT.REGEXP")
.Global = True
.Pattern = "R(\d+)-R(\d+)"

Set oMatches = .Execute(sStr)
For Each oMatch In oMatches
sStr = Replace(sStr, oMatch, Join(Evaluate("""R""&transpose(row(" & _
oMatch.submatches(0) & ":" & oMatch.submatches(1) & "))"), ", "))
Next
End With
ExpandData = sStr
End Function




Regards, JimmyG

 

[jindon]

try

Sub test()
Dim a, b(), i As Long, ii As Long, n As Long, x, y, e, v
Dim xStart As Long, xEnd As Long, yStart As Long, yEnd As Long
a = Range("a1").CurrentRegion.Resize(,4).Value
ReDim b(1 To Rows.Count, 1 To 4)
For i = 2 To UBound(a,1)
    x = Split(a(i,1),",")
    y = Split(a(i,2),",")
    For Each e In x
        If InStr(e,"-") Then
            xStart = Val(Split(e,"-")(0)) : xEnd = Val(Split(e,"-")(1))
        Else
           xStart = Val(e) : xEnd = Val(e) 
        End If
        For Each v In y
            If InStr(e,"-") Then
                yStart = Val(Split(v,"-")(0)) : yEnd = Val(Split(v,"-")(1))
            Else
               yStart = Val(v) : yEnd = Val(v) 
            End If
            For ii = xStart To xEnd
                n = n + 1
                b(n,1) = xStart : b(n,2) = ii : b(n,3) = a(i,3) : b(n,4) = a(i,4)
            Next
        Next
    Next
Next
Range("f1").Resize(n,4).Value = b
End Sub

 

[jindon]

            For ii = xStart To xEnd
                n = n + 1
                b(n,1) = xStart : b(n,2) = ii : b(n,3) = a(i,3) : b(n,4) = a(i,4)
            Next

Should be

            For ii = xStart To xEnd
                For iii = yStart To yEnd
                    n = n + 1
                    b(n,1) = ii : b(n,2) = iii : b(n,3) = a(i,3) : b(n,4) = a(i,4)
                Next
            Next