Trouble with arrays (transferring values between two arrays)

[Keith R]

XL2003 on WinXP. I have the weirdest thing going on, and can't figure it
out. I get an out of range error [9] when I get to the second sub.

In Module 1, I have:

Option Base 1
Public Expedite_LastRow As Integer
Public ExpediteArrayShort As Variant '(1 To x rows, 1 column)

Sub FirstSub
'identify how many rows are currently used
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row

'prepare to make a larger array to allow addition of new records from user
form
ExpediteArraySize = Expedite_LastRow + 100

'grab a range to use in the array
ExpediteArrayShort = Sheet4.Range("B1:B" & CStr(ExpediteArraySize)).Value

'resize short array to 1D so Application.Match will work
ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To
UBound(ExpediteArrayShort), 0 To 0)
End sub

'In module 2, I have:

Sub SecondSub
'Expedite_LastRow confirmed value as = 14
'sArray(1, i) confirmed value as = "P67G3"
'lBound(ExpediteArrayShort) confirmed value as =1
'uBound(ExpediteArrayShort) confirmed value as =114
ExpediteArrayShort(Expedite_LastRow) = sArray(1, i) '<< code dies here
'e.g. ExpediteArrayShort(14) = "P67G3"
End Sub

But I'm getting thisout of range [error 9]. Normally I'd think that my
variable (Expediate_LastRow) or (i) were outside the range for those arrays,
but sArray is returning a value, and I've confirmed that the Lbound/Ubound
of the destination array is 1 and 114.

I'm not using the array anywhere else in any subs, so there isn't anything
else that should be affecting it.

I created a simple comparison set (all within one sub/module, so not quite
apples to apples) that works fine:
Sub test_set()
Dim Tarray(1 To 10)
Dim DArray(1 To 10)
Tarray(3) = "This is a test"
DArray(3) = Tarray(3)
MsgBox DArray(3) '<< returns "This is a test" in a messagebox
End Sub

Any ideas what might be wrong with my real project?
Thank you very much,
Keith

 

[Tim Zych]

Sub FirstSub()
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
ExpediteArraySize = Expedite_LastRow + 100
ExpediteArrayShort = Sheet4.Range("B1:B" &
CStr(ExpediteArraySize)).Value
End Sub

Sub SecondSub()
' Test Application.Match
ExpediteArrayShort(Expedite_LastRow, 1) = "P67G3"
Debug.Print Application.Match("P67G3", ExpediteArrayShort, 0)

' Uncomment this
' ExpediteArrayShort(Expedite_LastRow, 1) = sArray(1, i)
End Sub

 

[Keith R]

Tim- my apologies, I may need more context to understand your suggestion. It
looks like you are sticking with a 2-D array instead of my switch to a 1D
(although yours is a lot less work, since you avoid the redim altogether).
However, you assign the "new" array value directly, whereas my brain is
jello because I can't figure out why I can't assign my new value to my
existing array from the other existing array. All of my variables seem to be
well within parameters (array sizes, etc.) but it still errors out on me in
the second sub. I included a bunch of comments in my second sub just to show
what I've looked at, but there is only one active line, which is the one
where it dies.

Many thanks,
Keith

Sub FirstSub()
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
ExpediteArraySize = Expedite_LastRow + 100
ExpediteArrayShort = Sheet4.Range("B1:B" &
CStr(ExpediteArraySize)).Value
End Sub

Sub SecondSub()
' Test Application.Match
ExpediteArrayShort(Expedite_LastRow, 1) = "P67G3"
Debug.Print Application.Match("P67G3", ExpediteArrayShort, 0)

' Uncomment this
' ExpediteArrayShort(Expedite_LastRow, 1) = sArray(1, i)
End Sub




--
Tim Zych
SF, CA

XL2003 on WinXP. I have the weirdest thing going on, and can't figure it
out. I get an out of range error [9] when I get to the second sub.

In Module 1, I have:

Option Base 1
Public Expedite_LastRow As Integer
Public ExpediteArrayShort As Variant '(1 To x rows, 1 column)

Sub FirstSub
'identify how many rows are currently used
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row

'prepare to make a larger array to allow addition of new records from
user form
ExpediteArraySize = Expedite_LastRow + 100

'grab a range to use in the array
ExpediteArrayShort = Sheet4.Range("B1:B" &
CStr(ExpediteArraySize)).Value

'resize short array to 1D so Application.Match will work
ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To
UBound(ExpediteArrayShort), 0 To 0)
End sub

'In module 2, I have:

Sub SecondSub
'Expedite_LastRow confirmed value as = 14
'sArray(1, i) confirmed value as = "P67G3"
'lBound(ExpediteArrayShort) confirmed value as =1
'uBound(ExpediteArrayShort) confirmed value as =114
ExpediteArrayShort(Expedite_LastRow) = sArray(1, i) '<< code dies here
'e.g. ExpediteArrayShort(14) = "P67G3"
End Sub

But I'm getting thisout of range [error 9]. Normally I'd think that my
variable (Expediate_LastRow) or (i) were outside the range for those
arrays, but sArray is returning a value, and I've confirmed that the
Lbound/Ubound of the destination array is 1 and 114.

I'm not using the array anywhere else in any subs, so there isn't
anything else that should be affecting it.

I created a simple comparison set (all within one sub/module, so not
quite apples to apples) that works fine:
Sub test_set()
Dim Tarray(1 To 10)
Dim DArray(1 To 10)
Tarray(3) = "This is a test"
DArray(3) = Tarray(3)
MsgBox DArray(3) '<< returns "This is a test" in a messagebox
End Sub

Any ideas what might be wrong with my real project?
Thank you very much,
Keith

 

[Tim Zych]

When it errors, what does these evaluate to? In the immediate window:

? i
? sArray(1, i)
? Expedite_LastRow
? ExpediteArrayShort(Expedite_LastRow, 1)

Do any of them return errors?

Tim- my apologies, I may need more context to understand your suggestion.
It looks like you are sticking with a 2-D array instead of my switch to a
1D (although yours is a lot less work, since you avoid the redim
altogether). However, you assign the "new" array value directly, whereas
my brain is jello because I can't figure out why I can't assign my new
value to my existing array from the other existing array. All of my
variables seem to be well within parameters (array sizes, etc.) but it
still errors out on me in the second sub. I included a bunch of comments
in my second sub just to show what I've looked at, but there is only one
active line, which is the one where it dies.

Many thanks,
Keith

Sub FirstSub()
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row
ExpediteArraySize = Expedite_LastRow + 100
ExpediteArrayShort = Sheet4.Range("B1:B" &
CStr(ExpediteArraySize)).Value
End Sub

Sub SecondSub()
' Test Application.Match
ExpediteArrayShort(Expedite_LastRow, 1) = "P67G3"
Debug.Print Application.Match("P67G3", ExpediteArrayShort, 0)

' Uncomment this
' ExpediteArrayShort(Expedite_LastRow, 1) = sArray(1, i)
End Sub




--
Tim Zych
SF, CA

XL2003 on WinXP. I have the weirdest thing going on, and can't figure it
out. I get an out of range error [9] when I get to the second sub.

In Module 1, I have:

Option Base 1
Public Expedite_LastRow As Integer
Public ExpediteArrayShort As Variant '(1 To x rows, 1 column)

Sub FirstSub
'identify how many rows are currently used
Expedite_LastRow = Sheet4.Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row

'prepare to make a larger array to allow addition of new records from
user form
ExpediteArraySize = Expedite_LastRow + 100

'grab a range to use in the array
ExpediteArrayShort = Sheet4.Range("B1:B" &
CStr(ExpediteArraySize)).Value

'resize short array to 1D so Application.Match will work
ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To
UBound(ExpediteArrayShort), 0 To 0)
End sub

'In module 2, I have:

Sub SecondSub
'Expedite_LastRow confirmed value as = 14
'sArray(1, i) confirmed value as = "P67G3"
'lBound(ExpediteArrayShort) confirmed value as =1
'uBound(ExpediteArrayShort) confirmed value as =114
ExpediteArrayShort(Expedite_LastRow) = sArray(1, i) '<< code dies here
'e.g. ExpediteArrayShort(14) = "P67G3"
End Sub

But I'm getting thisout of range [error 9]. Normally I'd think that my
variable (Expediate_LastRow) or (i) were outside the range for those
arrays, but sArray is returning a value, and I've confirmed that the
Lbound/Ubound of the destination array is 1 and 114.

I'm not using the array anywhere else in any subs, so there isn't
anything else that should be affecting it.

I created a simple comparison set (all within one sub/module, so not
quite apples to apples) that works fine:
Sub test_set()
Dim Tarray(1 To 10)
Dim DArray(1 To 10)
Tarray(3) = "This is a test"
DArray(3) = Tarray(3)
MsgBox DArray(3) '<< returns "This is a test" in a messagebox
End Sub

Any ideas what might be wrong with my real project?
Thank you very much,
Keith

 

[Alan Beban]

XL2003 on WinXP. I have the weirdest thing going on, and can't figure it
out. I get an out of range error [9] when I get to the second sub.
. . .

'resize short array to 1D so Application.Match will work
ReDim Preserve ExpediteArrayShort(LBound(ExpediteArrayShort, 1) To
UBound(ExpediteArrayShort), 0 To 0)

ExpediteArrayShort is a single-column 2-D array; its elements must be
accessed with two index numbers, one for the "row" and one for the
"column." When you try to access with just one index number you get the
"Subscript out of range" error message.

Alan Beban