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

 

[DanteAmor]

Change the declaration of variables to:

Dim W, S$(), N as long, K&, V, L$(), R$(), A%, T$(3), B%, C%, D%

---
What does the error message say?
At the time of the error what is the value of N?

 

[Danny54]

Thanks DanteAmor

I changed my declaration by adding your line and re-ran

I receive a
Run-time error '9';
subscript out of range

hitting debug
it highlights the following line which is the line after "N = N + 1"

S(N, 0) = W(K, 1)

in Locals
N = 1048577

 

[Danny54]

here is better test data

Title	IPs
Car	10.218.68.3-11.218.69.3
Boat	192.218.68.3-192.218.69.3,12.218.68.3-12.218.69.3,10.4.5.5

 

[DanteAmor]

Here's the problem: you size the array to a maximum of "rows.count"
ReDim S(1 To Rows.Count, 1)

The number of rows on the sheet is 1048576
Then s(1 to 1048576)
So when your N counter reaches 1048577 and tries to pass the data in the array, it sends error.

I guess something wrong is in your macro as it's reaching a million records. And it doesn't end.

Title	IPs
Car	10.218.68.3-11.218.69.3
Boat	192.218.68.3-192.218.69.3,12.218.68.3-12.218.69.3,10.4.5.5

If that's your test data, the results should be:

10.218.68.3
10.218.68.4
...
to
10.218.68.255

then
10.218.69.1
to
10.218.69.255

And then what's next?
10.218.70.1?
to
10.218.70.255?

And so on until
11.218.69.3

------
You should review how you want to expand the ips. From the fourth segment 255 * the third segment 255 is equal to 65,000 records * 255 of the second segment equal to 16 million records.

 

[Danny54]

Title	IPs
Car	10.218.68.3-10.218.68.4
Boat	192.218.68.3-192.218.69.3, 12.218.68.3-12.218.68.7,10.4.5.5

Would produce

Car	10.218.68.3
Car	10.218.68.4
Boat	192.218.68.3
Boat	192.218.68.4
Boat	192.218.68.5
Boat	192.218.68.6
Boat	192.218.68.7
Boat	192.218.68.8
Boat	192.218.68.9
Boat	.
Boat	.
Boat	.
Boat	192.218.68.254
Boat	192.218.68.255
Boat	192.218.69.0
Boat	192.218.69.1
Boat	192.218.69.2
Boat	192.218.69.3
Boat	12.218.68.3
Boat	12.218.68.4
Boat	12.218.68.5
Boat	12.218.68.6
Boat	12.218.68.7
Boat	10.4.5.5

 

[DanteAmor]

10.218.68.3-11.218.69.3

That from above is different from this from below:

10.218.68.3-10.218.68.4

With that you should not have problems with the number of records. Did you test your macro with those IP ranges?

 

[Peter_SSs]

@Danny54
When posting vba code in the forum, please use the available code tags to preserve indentation formatting and to make your code much easier to read, debug or copy/paste.
My signature block below has more details.

Compare your original code in post #1 above with the code below it where I replicated your original code but used the code tags.

 

[Danny54]

That from above is different from this from below:

With that you should not have problems with the number of records. Did you test your macro with those IP ranges?

Good Morning Dante and Thanks for your help.

Yes the code works for this shortened list of test data which doesn't show all the actual data lines.

Also, I don't believe any IP's actually go into the 1st octet 10.XXX.XXX.XXX or second octet 10.218.XXX.XXX.

That being said there are lines of data containing a hundred or so ip ranges - 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 which would
cause the N number to be high

So if I understand, i'm maxing the value
"So when your N counter reaches 1048577 and tries to pass the data in the array, it sends error."

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

Again
Thank you very much for your help

 

[DanteAmor]

So when your N counter reaches 1048577 and tries to pass the data in the array, it sends error.

When you size your array to rows.count, the limit is the number of rows of a sheet per column, so when it fills you can move to the next column and so on. The problem is when you change the first segment:

10.218.68.3-11.218.69.3

If the changes are in the third and fourth segments, then I don't think you have problems with the numbering, even as I mentioned you can take it to other columns.
192.168.1.1-192.168.3.3