Why would the lower bound not always be one? Would not an array be
populated from 1 to x?

You can have any lower and upper bound you want.

Dim Arr(-100 To 100) As Long
Debug.Print LBound(Arr), UBound(Arr)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Mike H." wrote in message
...
Why would the lower bound not always be one? Would not an array be
populated from 1 to x?

"Bernie Deitrick" wrote:

Mike,

LBound and UBound are used to read the lower and upper bounds of arrays
whose size you don't know.

If you had coded

Dim DataArray(1 to 5000) As Double

then it would be okay to use

For i = 1 To 5000

But a lot of operations return arrays of unknown size, so using LBound
and UBound to read the size
makes sense. There is one other complication - Option Base, which can be
used to set the Lower
bound of arrays automatically...

HTH,
Bernie
MS Excel MVP


"Mike H." wrote in message
...
Bernie, This works tremendously! Instead of 45 minutes, it takes less
than
45 seconds. Just one question, the LBound and UBound, does that stand
for
lower and upper boundary or something like that?

"Bernie Deitrick" wrote:

Mike,

Not sure where your Account#/Account desc table is, but assuming that
it is in columns A and B

For i = LBound(DataArray) To UBound(DataArray)
DataArray(i, 2) = Application.WorksheetFunction.VLookup(DataArray(i,
1), Range("A:B"), 2, False)
Next i

But why are you putting this into an array? You could just use the
VLOOKUP whenever you want the
value.

HTH,
Bernie
MS Excel MVP


"Mike H." wrote in message
...
Here is what I do. I read into Dataarray() all my entries in a
spreadsheet
(about 5000). The array is like this: Dataarray(REC#,1)=Account #
DataArray(Rec#,2)=Account description if there is one (if it is a
valid
account). To fill in the second element I do this:



Windows("Chart of Accounts.xls").Activate
Sheets("Entire Chart").Select
Let Counterx = 0
For YY = 1 To X
Let XX = 0
Set myRange = Range("A1:A65000")
flag = 0
For Each c In myRange
Let XX = XX + 1
If c.Value = DataArray(YY, 1) Then flag = 1
If flag = 1 Then
Exit For
End If
Next
If flag = 1 Then
DataArray(YY, 2) = Cells(XX, 2)
Else
Let Counterx = Counterx + 1
BadAccts(Counterx, 1) = DataArray(YY, 1)
'MsgBox ("Account " & DataArray(YY, 1) & " is not in the
chart of
accounts!")
End If
Next

This code takes about 45 minutes to go through 5000 records. Any
suggestions would be welcomed.