LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 37
Default Trouble with arrays (transferring values between two arrays)

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


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trouble with Arrays Arturo Excel Programming 2 April 3rd 07 04:06 PM
Working with ranges in arrays... or an introduction to arrays Glen Excel Programming 5 September 10th 06 08:32 AM
Arrays - declaration, adding values to arrays and calculation Maxi[_2_] Excel Programming 1 August 17th 06 04:13 PM
Trouble with Arrays / Collections John[_88_] Excel Programming 9 September 7th 05 04:03 PM
Transferring ranges to/from arrays Jag Man Excel Programming 14 December 28th 03 07:02 PM


All times are GMT +1. The time now is 04:45 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"