Hi,

I am doing something wrong in trying to read a range from a spreadsheet into
an array. Below are the relevant portions of my code. I have omitted the
extraneous stuff.

Sub UpdateSheet()
'\Three string names
Dim sSpreadShtNameRng As String '\Range Name1
Dim sPwdNameRng As String '\Range Name2
Dim sWriteResPwdNameRng As String '\Range Name3

Dim vaSpreadShtName As Variant
Dim vaPwd As Variant
Dim vaWriteResPwd As Variant

Dim iCounter As Integer

sHiddenFileName = "Hidden"

'\ 3 range name are on the sheet "Hidden"
'\ All ranges are local..but I tried global
'\too and it failed.

sSpreadShtNameRng = "xrnSpreadShtNameCC" '\Range Name1
sPwdNameRng = "xrnPwd1CC" ' \Range Name2
sWriteResPwdNameRng = "xrnPwd2CC" '\Range Name3


'\Activate Hidden sheet.
Worksheets(sHiddenFileName).Activate

'\ I think below is where I am going wrong...

vaSpreadShtName = Range(sSpreadShtNameRng).Value
vaPwd = Range(sPwdNameRng).Value
vaWriteResPwd = Range(sPwdNameRng).Value

'\ Below I get iCounter=1, but the next step yields an error message
'\ Run-time error: 9, subscript out of range.

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

End Sub


Can you see my error(s)? And if so, can you please shed some light on this
matter for me.

Thank you.

Regards,
Kevin

Keven, try one of the two examples, be sure to at least use parenthese
when declaring the array:

Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3

ReDim Preserve vaSpreadShtName(1 To 5) 'for dynamic (1 To
VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter



OR...

Dim sSpreadShtNameRng(5) As String '\Range Name1
Dim sPwdNameRng(5) As String '\Range Name2
Dim sWriteResPwdNameRng(5) As String '\Range Name3


HTH--Lonnie M.

Lonnie, Kevin,

I tried to follow your indications with following code, but I've got the
same runtime error 9: subscript out of range.

Dim arr() As Variant
Dim i As Integer
ReDim Preserve arr(1 To 30)
arr() = ActiveSheet.Range("A1:C30").Value
For i = 1 To UBound(arr)
Debug.Print arr(i)
Next i

Moreover, if the range actually contains less or more than 30 cells (let's
say 2) then the array changes its dimensions (LBound and Ubound correctly
say respectively 1 and 2), but I cannot access any of the two items and got
the error.

Any other clue?

Paolo



--
Paolo
Milan, Italy


NOTE: remove QUESTONO from my email address for direct emailing
"Lonnie M." ha scritto nel messaggio
ups.com...
Keven, try one of the two examples, be sure to at least use parenthese
when declaring the array:

Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3

ReDim Preserve vaSpreadShtName(1 To 5) 'for dynamic (1 To
VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter



OR...

Dim sSpreadShtNameRng(5) As String '\Range Name1
Dim sPwdNameRng(5) As String '\Range Name2
Dim sWriteResPwdNameRng(5) As String '\Range Name3


HTH--Lonnie M.

Lonnie M. wrote...
Keven, try one of the two examples, be sure to at least use parenthese
when declaring the array:

Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3


No, I don't think I should not be putting() because these are simply
strings. They are not an array It is a string, which is the name of the
range on Hidden spreadsheet which contains 5 values per range.

ReDim Preserve vaSpreadShtName(1 To 5) 'for dynamic (1 To
VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

I tried this but I got the same error message.

Run-time error 9, subscript out of range. I wished it worked. Then my
problem would be solved.


OR...

Dim sSpreadShtNameRng(5) As String '\Range Name1
Dim sPwdNameRng(5) As String '\Range Name2
Dim sWriteResPwdNameRng(5) As String '\Range Name3

There is only one sSpreadshtNameRng, which is xrnSpreadShtNameCC. There are
not five of them. So I don't think this is my trouble spot here in that I
need to create an array.

I must be doing something that is wrong, but I am not sure what it is.

Thank you for trying.

Regards,
Kevin

Hi, Tom Ogilvy posted a similar example to what you are trying to do:

Dim vArr as Variant
vArr = Range(Cells(1,1),Cells(1,1).end(xldown))
for i = lbound(varr,1) to ubound(varr,1)
debug.print i, varr(i,1)
Next


You must pick up a range as a variant variable, which will then contain
the
array.


The array is always two dimensional, even for a single column.


for a single row it would be


Sub Tester5()
Dim vArr As Variant
vArr = Range(Cells(1, 1), Cells(1, 256).End(xlToLeft))
For i = LBound(vArr, 2) To UBound(vArr, 2)
Debug.Print i, vArr(1, i)
Next
End Sub

HTH--Lonnie M.

I've got it!

I've written a sample sub here below, you can re-adapt it for every
situation.
The fact is that when you assign the range to the array, no matter the
declaration, VBA create a bi-dimensional array ..... very simple:
Arr(rownumber, colnumber)
It doesn't seem to create a third dimension for multi area ranges, although,
but that is easily solved with a loop.

Sub ProvaArray()

Dim arr() As Variant
Dim i As Long
Dim k As Long
Dim a As Long

For a = 1 To Selection.Areas.Count
arr() = Selection.Areas(a).Value

For k = 1 To UBound(arr, 2) 'columns count
For i = 1 To UBound(arr, 1) 'rows count
Debug.Print arr(i, k)
Next i
Next k
Next a
End Sub

Let me know if it works also in your case.

Paolo

--
Paolo
Milan, Italy
NOTE: remove QUESTONO from my email address for direct emailing
"Kevin H. Stecyk" ha scritto nel messaggio
...
Lonnie M. wrote...
Keven, try one of the two examples, be sure to at least use parenthese
when declaring the array:

Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3


No, I don't think I should not be putting() because these are simply
strings. They are not an array It is a string, which is the name of the
range on Hidden spreadsheet which contains 5 values per range.

ReDim Preserve vaSpreadShtName(1 To 5) 'for dynamic (1 To
VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

I tried this but I got the same error message.

Run-time error 9, subscript out of range. I wished it worked. Then my
problem would be solved.


OR...

Dim sSpreadShtNameRng(5) As String '\Range Name1
Dim sPwdNameRng(5) As String '\Range Name2
Dim sWriteResPwdNameRng(5) As String '\Range Name3

There is only one sSpreadshtNameRng, which is xrnSpreadShtNameCC. There
are not five of them. So I don't think this is my trouble spot here in
that I need to create an array.

I must be doing something that is wrong, but I am not sure what it is.

Thank you for trying.

Regards,
Kevin

Dim sSpreadShtNameRng() As String '\Range Name1
Dim sPwdNameRng() As String '\Range Name2
Dim sWriteResPwdNameRng() As String '\Range Name3

No, I don't think I should not be putting() because these are simply
strings. They are not an array It is a string, which is the name of
the
range on Hidden spreadsheet which contains 5 values per range.

My appologies, I grabbed the wrong variables, copy paste thing...

Dim vaSpreadShtName() As Variant
Dim vaPwd() As Variant
Dim vaWriteResPwd() As Variant

ReDim Preserve vaSpreadShtName(1 To 5)
'for dynamic (1 To VarriableHere)
ReDim Preserve vaPwd (1 To 5)
ReDim Preserve vaWriteResPwd(1 To 5)

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

OR...
'using 5 based on the loop to load the array
Dim vaSpreadShtName(5) As Variant
Dim vaPwd(5) As Variant
Dim vaWriteResPwd(5) As Variant

For iCounter = 1 To 5 Step 1
Debug.Print iCounter
Debug.Print vaSpreadShtName(iCounter)
Debug.Print vaPwd(iCounter)
Debug.Print vaWriteResPwd(iCounter)
Next iCounter

HTH--Lonnie M.