Max number for arrays

[Danny54]

Good Afternoon

I've been working on the following macro trying to get all the values in a range for several weeks now. Im at a loss and have had several people help that have me to this point.
the code keeps erroring out and Im not certain of what to do next. Explanation of the macro: it takes the ip values and list them on sheet2 after expanding them by count
ie 10.10.10.1-10.10.10.3 would list 10.10.10.1 and 10.10.10.2 and 10.10.10.3 in column b of sheet2. if we have the values 10.10.10.1-10.10.11.1 would list all the values beginning at
10.10.10.1 thru 10.10.11.1

The code stops on the line show " N = N + 1" below


Any help would be greatly appreciated.

Thanks

xls input

Title	IPs
Car	10.218.68.3-11.218.68.3

Macro

Sub Expandit()
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) ' get col1 and col2 into the array s
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 '<-Error is happening here
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

MsgBox "Completed"

End Sub

Sub Expandit()
    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)  ' get col1 and col2 into the array s
        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                                           '<-Error is happening here
                    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
  
    MsgBox "Completed"
      
End Sub

 

[Danny54]

Thanks for you help

Would there be a simple way to just write a dos file instead of putting it to a excel file?

 

[Peter_SSs]

Should I probably rethink a new solution and abandon this code?

The original objective was to take a range of IP's, by line - expand them in order to create a listing that I could search to see if one exists and if so, get the Title

I have not worked right through this idea but might something like this be feasible?

Sub Make_Ranges()
  Dim a As Variant, b As Variant, splt_1 As Variant, splt_2 As Variant, splt_3 As Variant, itm As Variant
  Dim i As Long, j As Long, K As Long
  Dim s As String
  
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
  ReDim b(1 To Rows.Count, 1 To 3)
  K = 1
  For i = 2 To UBound(a)
    splt_1 = Split(Replace(a(i, 2), " ", ""), ",")
    For Each itm In splt_1
      splt_2 = Split(itm & "-" & itm, "-", 3)
      K = K + 1
      b(K, 1) = a(i, 1)
      
      s = vbNullString
      splt_3 = Split(splt_2(0), ".")
      For j = 0 To 3
        s = s & Format(splt_3(j), "000")
      Next j
      b(K, 2) = s
      
      s = vbNullString
      splt_3 = Split(splt_2(1), ".")
      For j = 0 To 3
        s = s & Format(splt_3(j), "000")
      Next j
      b(K, 3) = s
    Next itm
  Next i
  With Sheets("Sheet2").Range("A1:C1").Resize(K)
    .NumberFormat = "000000000000"
    .Value2 = b
    .Rows(1).Value = Array("Title", "IP Start", "IP End")
    .Columns.AutoFit
  End With
End Sub

This just breaks up the data into individual IP ranges rather than individual IP values, so it would not use nearly as many rows. For example, this sample data in Sheet1

Danny54.xlsm
	A	B
1	Title	IPs
2	Car	10.218.68.3-10.218.68.4
3	Boat	192.218.68.3-192.218.69.3, 12.218.68.3-12.218.68.7,10.4.5.5
4	Other	10.10.10.1, 10.10.10.3-10.10.25.3, 192.168.1.1-192.168.3.3, 192.168.20.1-192.168.20.25
Sheet1

becomes this in Sheet2

Danny54.xlsm
	A	B	C
1	Title	IP Start	IP End
2	Car	010218068003	010218068004
3	Boat	192218068003	192218069003
4	Boat	012218068003	012218068007
5	Boat	010004005005	010004005005
6	Other	010010010001	010010010001
7	Other	010010010003	010010025003
8	Other	192168001001	192168003003
9	Other	192168020001	192168020025
Sheet2

If you then take your IP address of interest, expand it to full 12 digits like these and see which row (if any) has column B <= your IP of interest and column C >= your IP of interest to determine the relevant title?