List all IP's found between beginning and ending targets

[Danny54]

Is there a way to list all IP's between a starting and ending range?
(Below is sheet1 which contains one row called ranges. Each range has a beginning and ending range separated by a dash. Each group is separated by a comma. There can be many groups on this line. IP ranges end at 255 then increment the value to the left and start over with that octet and continue incrementing again) below are two ranges with starting and ending values for each range

Sheet1
A
Ranges
10.90.168.0-10.90.169.255,10.103.240.2-10.103.241.250

(the ... is removed columns that would actually have data. I removed them to shorten the listing)

Sheet2 Output
A
IP's
10.90.168.0
10.90.168.1
10.90.168.2
.....
10.90.169.254
10.90.169.255
10.103.240.2
10.103.240.3
.....
10.103.241.249
10.103.241.250


Thanks

 

[Marc L]

(removed 'cause of glitch)

 

[Marc L]

A VBA demonstration as a starter :​

Sub Demo1()
    Dim V, S$(), N&, L$(), R$(), A%, T$(3), B%, C%, D%
        V = [A1:A2].Value2
        If Not (V(1, 1) = "Ranges" And V(2, 1) > "") Then Beep: Exit Sub
        ReDim S$(1 To Rows.Count, 0)
        S(1, 0) = "IP's"
        N = 1
    For Each V In Split(V(2, 1), ",")
              L = Split(V, "-")
        If UBound(L) = 1 Then
                R = Split(L(1), ".")
                L = Split(L(0), ".")
            If UBound(L) = 3 And UBound(R) = 3 Then
                For A = L(0) To R(0)
                    T(0) = A
                For B = -L(1) * (A = L(0) * 1) To IIf(A < R(0) * 1, 255, R(1))
                    T(1) = B
                For C = -L(2) * (B = L(1) * 1) To IIf(B < R(1) * 1, 255, R(2))
                    T(2) = C
                For D = -L(3) * (C = L(2) * 1) To IIf(C < R(2) * 1, 255, R(3))
                    T(3) = D
                    N = N + 1
                    S(N, 0) = Join(T, ".")
                Next D, C, B, A
            End If
        End If
    Next
        [A1].Resize(N).Value2 = S
End Sub

 

[Danny54]

Perfect.

Thanks so much

 

[Danny54]

Great Solution.

 

[Danny54]

Marc L,

while processing the data today I found a range that causes the macro to throw a error.

It throws a Run-time error '13' Type mismatch


Snippet of the code you provided
============================================================================
For i = 1 To UBound(arr)
sloc = InStr(arr(i, 1), "-")
If sloc > 0 Then
v1 = Left(arr(i, 1), sloc - 1)
v2 = Mid(arr(i, 1), sloc + 1)
Else
v1 = arr(i, 1)
v2 = arr(i, 1)
End If
v1a = Left(v1, InStrRev(v1, "."))
s1 = Mid(v1, InStrRev(v1, ".") + 1) '<----- heres the line the error is thrown
s2 = Mid(v2, InStrRev(v2, ".") + 1)
For j = s1 To s2
c = c + 1
dic.Add c, v1a & j
Next j
Next i
==============================================================================

this is the range which looks good to me. Any Ideas?

Thanks


(Input range being process when the error is thrown)

10.246.49.96-10.246.49.127

 

[Marc L]

Snippet of the code you provided

As this is very not my VBA demonstration ! …​

 

[Danny54]

Good afternoon
I was thrilled to have Mark L's solution work for my input (#1) but now I find myself in need of a modification. I have a hard time with arrays and have gotten myself lost.

The input data changed to the following

Title	Ranges
A	10.1.1.0-10.1.1.5
B
C	11.2.1.0
D	10.2.2.0-10.2.2.5,10.2.3.0-10.2.4.1

New output sheet

Title	Ranges
A	10.1.1.0
A	10.1.1.1
A	10.1.1.2
A	10.1.1.3
A	10.1.1.4
A	10.1.1.5
C	11.2.1.0
D	10.2.2.0
D	10.2.2.1
D	10.2.2.2
D	10.2.2.3
D	10.2.2.4
D	10.2.2.5
D	10.2.3.0
D	10.2.3.1
D	10.2.3.2
D	10.2.3.3
D	10.2.3.4
D	10.2.3.5
	data rows removed to shorten example (Removed rows 10.2.3.6 - 10.2.3.254)
D	10.2.3.255
D	10.2.4.0
	10.2.4.1

What happened was that Mark L's solution #3 worked for just one line, creating output properly, increment ip ranges 0-255 for each octet, but now I have many lines and need to keep the title for each IP and its calculated range. I tried to modify his solution but got in over my head.

Any help would be appreciated.

Thanks

 

[Marc L]

I was thrilled to have Marc L's solution work for my input

​

Another starter from the initial starter :​

Sub Demo2()
    Dim W, S$(), N&, K&, V, L$(), R$(), A%, T$(3), B%, C%, D%
        W = [Sheet1!A1].CurrentRegion.Value2
        ReDim S(1 To Rows.Count, 1):  S(1, 0) = W(1, 1):  S(1, 1) = W(1, 2)
        N = 1
    For K = 2 To UBound(W)
    For Each V In Split(W(K, 2), ",")
            L = Split(V, "-")
        If UBound(L) = 1 Then
                R = Split(L(1), ".")
                L = Split(L(0), ".")
            If UBound(L) = 3 And UBound(R) = 3 Then
                For A = L(0) To R(0)
                    T(0) = A
                For B = -L(1) * (A = L(0) * 1) To IIf(A < R(0) * 1, 255, R(1))
                    T(1) = B
                For C = -L(2) * (B = L(1) * 1) To IIf(B < R(1) * 1, 255, R(2))
                    T(2) = C
                For D = -L(3) * (C = L(2) * 1) To IIf(C < R(2) * 1, 255, R(3))
                    T(3) = D
                    N = N + 1
                    S(N, 0) = W(K, 1)
                    S(N, 1) = Join(T, ".")
                Next D, C, B, A
            End If
        Else
            N = N + 1
            S(N, 0) = W(K, 1)
            S(N, 1) = V
        End If
    Next V, K
        [Sheet2!A1:B1].Resize(N).Value2 = S
End Sub

 

[Danny54]

Perfect! I'll see what you added to this one against the first one so I can hopefully learn what to add to my vba knowledge.

Thanks Mark L
Have a wonderful day