[ExcelVBA] Array values

 

Hello Group,

I have built several 2 element arrays, based on web Queries. Column A has the link Symbol in it. The Arrays populate 55 times 2 elements, a date and a close.

I am not using all 55 elements and the close is the only element used directly the calculations. An example array is called ESDate and ESClose.

What I am trying to populate in a Row across from the Symbol is ESClose(2)-ESClose(3) into a column, then in the next column over ESClose(3)-ESClose(5), then in the next column over ESClose(8)-ESClose(5), up to 14 calculation in a row.

I can hard code this, but that is very time consuming and I am trying to think of a way to code this instead. I have put the Array element numbers ie. for ESClose(2)-ESClose(3), the numbers, 2 and 3, into Rows 1 and 2, in the associated column.

The Symbols start in Row 4 and the first Calculation, ESClose(2)-ESClose(3) starts in cell C4. So the hard coded value for C4 is ESClose(2)-ESClose(3).

So far what I have tried:
ESClose(2)-ESClose(3)

Equals this:
ESClose(Range("C1").Value) -ESClose(Range("C2").Value)

And this works, but if I try this:
range("A4").value & "Close" & (Range("C1").Value) -range("A4").value & "Close" & (Range("C2").Value)
It fails.

So I tried to break it down into individual elements:
range("A4").value & "Close" & (Range("C1").Value)
This gives me "ESClose", so I tried:
range("A4").value & "Close" & "(" & (Range("C1").Value) & ")"
This give me "ESClose(2)", but not the Element Value.

But if I try ? ESClose(2) in the immediate window I get a good value.
Seemed promising, so I assigned it to a variable:
Z=range("A4").value & "Close" & "(" & (Range("C1").Value) & ")"
And Tested Z - ?Z in the immediate window, but I simply get "ESClose(2)" and not the element value.

Any ideas would be appreciated.

Thanks,
David

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (1)

Recent Activity:

• New Members 10

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___