Subscript out of range, error 9 with array

[cg85cg]

I am writing a section of code that would go through an array and set repeat values to "" (null). I am using a For-Next loop to look at each element in the array. on the first iteration of the loop i get the runtime error 9, subscript out of range. It is clear to me that the variable i am using falls within the declared array size and i have no idea why it would be out of range. the code is as follows.

ReDim tickerarr(numrows + numrowsday)
tickerarr = Workbooks("ML_" & year & ".xls").Worksheets(1).Range("a1:a" & numrows + numrowsday).Value
For i = 1 To numrows + numrowsday
If tickerarr(i) = tickerarr(i + 1) Then
tickerarr(i + 1) = ""
End If
Next i

when the error pops up:
numrows = 0
numrowsday = 1940
tickerarr = array filled with strings
i = 1

 

[SkipVought]

Hi,

When i = numrows + numrowsday

then tickerarr([red]i + 1[/red]) will be out of bounds.

Skip,

_[red][/red]

 

[combo]

When you pass range values into array this way, that the target array is a 2D one, even in case of one column source range. In your case tickerarr is redimmed again, refer to it with tickerarr(i,1).

combo

 

[stevexff]

The algorithm breaks if you have more than 2 consecutive entries the same.

Sub DeDupe()

    Dim tickers As Variant
    tickers = Array(1, 2, 2, 3, 3, 3, 4, 4, 4, 4, 5)
    Dim current As Variant
    current = "."
    
    For i = 0 To UBound(tickers)
        If tickers(i) = current Then
            tickers(i) = "."
        Else
            current = tickers(i)
        End If
    Next
    
    For Each ticker In tickers
        Debug.Print ticker
    Next
    
End Sub

might be the kind of thing you need?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[mscallisto]

I find it useful to debug problems like this buy step by step processing the code.

While in edit mode, press F8 to start the step by step process.

Right click each variable you want to monitor and click "add to watch"

Press F8 to advance your code logic line by line and watch the variables in the watch window to see their values at each step.

Make any necessary code changes if you find problems.

You can also drag the code line arrow up to repeat steps if necessary.

Placing the cursor over any variable reveals it's value at that time.

There are many more debug tactics but you may need to visit excel help.

If you already know how to do this, please forgive me.

good luck

 

[cg85cg]

hey combo, that solved the problem, thanks. But now i've got another problem. Another program that I am writing requires that i put two different columns into a two dimensional array the code line that gives the problem is:

daydata = Application.Union(Range("a1:a" & dayrows), Range(datatype$ & "1:" & datatype$ & dayrows))

The array has been previously ReDimed as daydata(dayrows, 2) and Option Base 1 is true

When the problem line runs it assigns the first range just fine, but changes the array into a one dimensional array.

dayrows = 1940
datatype$ = "f"

I can't really find anything online. Any help would be great. Thanks.

 

[SkipVought]

Just loop thru each separate range and stuff in the appropriate array element.

Skip,

_[red][/red]

 

[cg85cg]

i'm just afraid taht would take too long. it takes about 12 seconds for VBA to loop through 3000 cells. it will do this 252 times per year and for 7 years for a total of 5.88 hours. I need to write faster code. I need to take the entire range and put it in. i know i can do it with a 1D range into a 1D array. perhaps there is a way to take one range at a time and specifiy which column in the array it should go into.

 

[stevexff]

As you seem to have 5M+ rows of data across a number of files, have you considered that Excel might not really be the tool of choice for manipulating it?

Databases are built to do this kind of thing, and would allow you to query, summarise, and chart the data pretty much any way you want without having to write complex, slow code. You can even link Excel sheets via ODBC queries.

SQL Server is good, Access might struggle a bit with 5M rows, MySql (free) and Oracle

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[combo]

Do not loop in range, it is very slow. Either assign two ranges to temporary variant arrays (n x 1, as above) and transfer their values into target n x 2 array, or create single area range first, and pass its values into variant array.

combo