Handling Array Error

[mferrisi]

I run this query for several accounts and then find a value for 'Other'. However, in a new account, there is only one 1 set of data returned, meaning my ubound is 4. Thus, when I run the FOR loop, I recieve an error.

Is there an easy way to make it so that I can handle a 1 by 4 array? (All of the other accounts produce an array of 1 to 4, 1 to about 400, so the ubound method is fine.)


Sub End_MV(Name As String, EndDate, YearRange As Range)
Dim TempM()
Dim TempW()
Dim qrs
Dim tuv As Integer
Dim Other As Double
databasename = DbConnectionHF
querystring = "SELECT history.name, history.hist_date, history.invest_type, history.other_assets_mny FROM DATABASE..history history WHERE (history.name ='" & Name & "')"

TempW = sqlrequest(databasename, querystring, , 4, False)

vb = UBound(TempW, 1)

For tuv = 1 To vb
If TempW(tuv, 2) = EndDate And TempW(tuv, 3) = "something" Then
Other = TempW(tuv, 4)
Exit For
End If

Next tuv

[Charlize]

You could check for a value in the second item (tuv,2) of the array ? If no value is present exit the for next.

Charlize

[mferrisi]

Thanks Charlize. What would that look like?

[Bob Phillips]

If IsEmpty(tuv(i)) Then
Exit For
End If

[mferrisi]

I get an Error: Expected Array error when I use that code....

[Bob Phillips]

I think you have a 2D array, so it needs adapting to the 2D, probably



If IsEmpty(tuv(1, i)) And IsEmpty(tuv(2, i)) And _
IsEmpty(tuv(3, i)) And IsEmpty(tuv(4, i)) Then

[mferrisi]

I get "subscript out of range" errors. Why do you use the i? In the watch window, this array loads as a 1D, 1 to 4. I need to catch the error if any account is not imported in 2D (since it won't have a full data set). Is there a way to determine if an array is in 1D?