Hi,
Thanks for your response. This is what I have so far, but it is not yielding
what I expect still:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
Stop

After the stop I go to the immediate window and type:
?ThisClose(1)
I am expecting the data stored in the Array to now be available to me, but I
get:
"Compile Error:"
"Expected Array"

The Option Base is set. In the Immediate Window, if I type ?CloseChanges(1),
wht I get is 12:00:00 AM. It does not matter which number I try and access,
it is alway 12:00:00 AM.

ThisDate
and
ThisClose
Are the elements I need to access.

Thanks,
David
"Douglas Klimesh" wrote:

To start with you you are not declaring CloseChanges() as Date. Use instead:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double

Also, note that by default arrays are dim'ed starting with 0. So if you
ReDim CloseChanges(10) the array is actually CloseChanges(0) ..to..
CloseChanges(9). Use the directive Option Base 1 at the very beginning
of your module code to have an array that goes from 1 to 10 instead of 0
to 9. Or you could: ReDim CloseChanges(1 To YearsTotal)

I always use Option Base 1 because if there is an problem with my index
variable its value will most often be zero, which will give me a VBA
error and more obviously alert me to my programming problem.

David wrote:
hi Group,

I am have a hard time with the syntax for setting up an Array.
This is what I have so far:
Dim CloseChanges(), ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
It did not like that I was using a variable to start with, so I went to the
ReDim syntax, but it still does not like it. The Array has only 2 data points
it is trying to pull in.

After it runs, I went to the Immediate Window and tried this:
?ThisClose(1)
But I get a message "Expect Array". Not sure wherre I am going wrong?

Thanks,
David

You still haven't used CloseChanges anywhere in your code. You need to put
something in those elements of the array somewhere.

Dim CloseChanges() As Date
Dim ThisDate As Date
Dim ThisClose As Double

ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select
CloseChanges(y)= ThisDate
Next y

....or something like that. We don't know what you are using CloseChanges for.

Charlie

"David" wrote:

Hi,
Thanks for your response. This is what I have so far, but it is not yielding
what I expect still:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
Stop

After the stop I go to the immediate window and type:
?ThisClose(1)
I am expecting the data stored in the Array to now be available to me, but I
get:
"Compile Error:"
"Expected Array"

The Option Base is set. In the Immediate Window, if I type ?CloseChanges(1),
wht I get is 12:00:00 AM. It does not matter which number I try and access,
it is alway 12:00:00 AM.

ThisDate
and
ThisClose
Are the elements I need to access.

Thanks,
David
"Douglas Klimesh" wrote:

To start with you you are not declaring CloseChanges() as Date. Use instead:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double

Also, note that by default arrays are dim'ed starting with 0. So if you
ReDim CloseChanges(10) the array is actually CloseChanges(0) ..to..
CloseChanges(9). Use the directive Option Base 1 at the very beginning
of your module code to have an array that goes from 1 to 10 instead of 0
to 9. Or you could: ReDim CloseChanges(1 To YearsTotal)

I always use Option Base 1 because if there is an problem with my index
variable its value will most often be zero, which will give me a VBA
error and more obviously alert me to my programming problem.

David wrote:
hi Group,

I am have a hard time with the syntax for setting up an Array.
This is what I have so far:
Dim CloseChanges(), ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
It did not like that I was using a variable to start with, so I went to the
ReDim syntax, but it still does not like it. The Array has only 2 data points
it is trying to pull in.

After it runs, I went to the Immediate Window and tried this:
?ThisClose(1)
But I get a message "Expect Array". Not sure wherre I am going wrong?

Thanks,
David

David,
Only CloseChanges is an array. The immediate window should print the
date with ?ThisClose . You still aren't setting CloseChanges() to
anything in your For loop. Don't you want something like
CloseChanges(y) = ThisClose ? 12:00:00 AM is like 0. It means you
haven't set the date variable to anything.

Charlie,
Can you give us your UBnd code. It sounds like a useful function.

David wrote:
Hi,
Thanks for your response. This is what I have so far, but it is not yielding
what I expect still:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
Stop

After the stop I go to the immediate window and type:
?ThisClose(1)
I am expecting the data stored in the Array to now be available to me, but I
get:
"Compile Error:"
"Expected Array"

The Option Base is set. In the Immediate Window, if I type ?CloseChanges(1),
wht I get is 12:00:00 AM. It does not matter which number I try and access,
it is alway 12:00:00 AM.

ThisDate
and
ThisClose
Are the elements I need to access.

Thanks,
David
"Douglas Klimesh" wrote:

To start with you you are not declaring CloseChanges() as Date. Use instead:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double

Also, note that by default arrays are dim'ed starting with 0. So if you
ReDim CloseChanges(10) the array is actually CloseChanges(0) ..to..
CloseChanges(9). Use the directive Option Base 1 at the very beginning
of your module code to have an array that goes from 1 to 10 instead of 0
to 9. Or you could: ReDim CloseChanges(1 To YearsTotal)

I always use Option Base 1 because if there is an problem with my index
variable its value will most often be zero, which will give me a VBA
error and more obviously alert me to my programming problem.

David wrote:
hi Group,

I am have a hard time with the syntax for setting up an Array.
This is what I have so far:
Dim CloseChanges(), ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
It did not like that I was using a variable to start with, so I went to the
ReDim syntax, but it still does not like it. The Array has only 2 data points
it is trying to pull in.

After it runs, I went to the Immediate Window and tried this:
?ThisClose(1)
But I get a message "Expect Array". Not sure wherre I am going wrong?

Thanks,
David

Hi,

I think yoou have come closer to any in what I am looking for. The code so
far is:

Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
z = 0
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ReturnAddress = ActiveCell.Address
Range("AA1").Select
ActiveCell.Offset(YearsTotal + 1 - z, 0).Value = ThisDate
ActiveCell.Offset(YearsTotal + 1 - z, 1).Value = ThisClose
z = z + 1
Range(ReturnAddress).Select
ActiveCell.Offset(-52, 0).Select
Next y

But you are right, I have not "Set" anything. I have placed the data in
another part of the worksheet. This is ALL in one module. I was hoping to get
to the end of the "collection" of data, then put it where I wanted it. I was
also hoping I might be able to access "elements" of the data as needed, using
it in several places.

I have not "Set" anything or created any Objects.

Thank you for your help, definitely to closest to what I was trying to
achieve.

Thanks Again,

"Douglas Klimesh" wrote:

David,
Only CloseChanges is an array. The immediate window should print the
date with ?ThisClose . You still aren't setting CloseChanges() to
anything in your For loop. Don't you want something like
CloseChanges(y) = ThisClose ? 12:00:00 AM is like 0. It means you
haven't set the date variable to anything.

Charlie,
Can you give us your UBnd code. It sounds like a useful function.

David wrote:
Hi,
Thanks for your response. This is what I have so far, but it is not yielding
what I expect still:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
Stop

After the stop I go to the immediate window and type:
?ThisClose(1)
I am expecting the data stored in the Array to now be available to me, but I
get:
"Compile Error:"
"Expected Array"

The Option Base is set. In the Immediate Window, if I type ?CloseChanges(1),
wht I get is 12:00:00 AM. It does not matter which number I try and access,
it is alway 12:00:00 AM.

ThisDate
and
ThisClose
Are the elements I need to access.

Thanks,
David
"Douglas Klimesh" wrote:

To start with you you are not declaring CloseChanges() as Date. Use instead:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double

Also, note that by default arrays are dim'ed starting with 0. So if you
ReDim CloseChanges(10) the array is actually CloseChanges(0) ..to..
CloseChanges(9). Use the directive Option Base 1 at the very beginning
of your module code to have an array that goes from 1 to 10 instead of 0
to 9. Or you could: ReDim CloseChanges(1 To YearsTotal)

I always use Option Base 1 because if there is an problem with my index
variable its value will most often be zero, which will give me a VBA
error and more obviously alert me to my programming problem.

David wrote:
hi Group,

I am have a hard time with the syntax for setting up an Array.
This is what I have so far:
Dim CloseChanges(), ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
It did not like that I was using a variable to start with, so I went to the
ReDim syntax, but it still does not like it. The Array has only 2 data points
it is trying to pull in.

After it runs, I went to the Immediate Window and tried this:
?ThisClose(1)
But I get a message "Expect Array". Not sure wherre I am going wrong?

Thanks,
David

Sure, there's really not much to it. If there is an error, or Arg1 is not an
array, the function simply "falls through" the If statement thereby returning
zero. Keep in mind this function works only if you use Option Base 1.

Public Function UBnd(Arg1 As Variant, Optional iDim As Long = 1) As Long
'
' returns Ubound of an array or zero if the array is undefined or empty
'
On Error Resume Next
If IsArray(Arg1) Then UBnd = UBound(Arg1, iDim)
'
End Function


"Douglas Klimesh" wrote:

David,
Only CloseChanges is an array. The immediate window should print the
date with ?ThisClose . You still aren't setting CloseChanges() to
anything in your For loop. Don't you want something like
CloseChanges(y) = ThisClose ? 12:00:00 AM is like 0. It means you
haven't set the date variable to anything.

Charlie,
Can you give us your UBnd code. It sounds like a useful function.

David wrote:
Hi,
Thanks for your response. This is what I have so far, but it is not yielding
what I expect still:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
Stop

After the stop I go to the immediate window and type:
?ThisClose(1)
I am expecting the data stored in the Array to now be available to me, but I
get:
"Compile Error:"
"Expected Array"

The Option Base is set. In the Immediate Window, if I type ?CloseChanges(1),
wht I get is 12:00:00 AM. It does not matter which number I try and access,
it is alway 12:00:00 AM.

ThisDate
and
ThisClose
Are the elements I need to access.

Thanks,
David
"Douglas Klimesh" wrote:

To start with you you are not declaring CloseChanges() as Date. Use instead:
Dim CloseChanges() As Date, ThisDate As Date, ThisClose As Double

Also, note that by default arrays are dim'ed starting with 0. So if you
ReDim CloseChanges(10) the array is actually CloseChanges(0) ..to..
CloseChanges(9). Use the directive Option Base 1 at the very beginning
of your module code to have an array that goes from 1 to 10 instead of 0
to 9. Or you could: ReDim CloseChanges(1 To YearsTotal)

I always use Option Base 1 because if there is an problem with my index
variable its value will most often be zero, which will give me a VBA
error and more obviously alert me to my programming problem.

David wrote:
hi Group,

I am have a hard time with the syntax for setting up an Array.
This is what I have so far:
Dim CloseChanges(), ThisDate As Date, ThisClose As Double
ReDim CloseChanges(YearsTotal)
For y = 1 To YearsTotal
ThisDate = ActiveCell.Value
ThisClose = ActiveCell.Offset(0, 6).Value
ActiveCell.Offset(-52, 0).Select

Next y
It did not like that I was using a variable to start with, so I went to the
ReDim syntax, but it still does not like it. The Array has only 2 data points
it is trying to pull in.

After it runs, I went to the Immediate Window and tried this:
?ThisClose(1)
But I get a message "Expect Array". Not sure wherre I am going wrong?

Thanks,
David