accessing data from an array
 

Assume Nums is a single column or single row

Dim varData() as double
Redim VarData(1 to Range("Nums").count, 1 to 1)

i = 0
For each c in Range("nums")
i = i + 1
varData (i+1) = c.Value
Next

Range("A1").Resize(i,1).Value = varData

--
regards,
Tom Ogilvy


"madge" wrote in message
m...
I have a variable that look like:

varData = "2.2, 3.2, 4.3" and I need to plot this on a spreadsheet
with the numbers in separate cells. i.e. The output needs to look
like:
2.2
3.2
4.3

I cannot figure out how to do this.

varData is a Variant but is not an Array. i.e It's declaration does
not have varData(). I do have an integer (i) that holds the number of
items that were put into varData. The code for this is something
like:

For each c in Range("nums")
varData = varData & "," & c.Value
i = i + 1
Next

But I am stuck as to how I retrieve the info from varData and plot it
into separate cells. I would appreciate any advice with how to handle
this.

Thank you
madge

accessing data from an array
 

varData is ReDimmed as a 2-d array, so varData(i+1) throws an error.

When that is changed to varData(i+1,1), the code throws an error at
varData(i+1,1) when i=the number of elements in Nums.

I think the following code does what Tom Ogilvy intended:

Dim varData() As Double
ReDim varData(1 To Range("Nums").Count, 1 To 1)

i = 1
For Each c In Range("Nums")
varData(i, 1) = c.Value
Debug.Print i, (varData(i, 1))
i = i + 1
Next

Range("D1").Resize(i - 1, 1).Value = varData '<--I used D1 'cause
I 'have something else in A1

But that raises the fundamental problem that all it does is create
another range that is identical to Nums; what's the point? I.e., if the
OP already has Nums, she already has the output she said she's seeking!??

Alan Beban

Tom Ogilvy wrote:
Assume Nums is a single column or single row

Dim varData() as double
Redim VarData(1 to Range("Nums").count, 1 to 1)

i = 0
For each c in Range("nums")
i = i + 1
varData (i+1) = c.Value
Next

Range("A1").Resize(i,1).Value = varData

accessing data from an array
 

Thank you so much Tom. This is just what I needed.

Can I trouble you further to ask if you would explain this line to me:
Redim VarData(1 to Range("Nums").count, 1 to 1)

I don't understand the ",1 to 1" bit. Is that creating a second "row"
in the array? If so, what does it hold?

Thanks
madge



"Tom Ogilvy" wrote in message ...
Assume Nums is a single column or single row

Dim varData() as double
Redim VarData(1 to Range("Nums").count, 1 to 1)

i = 0
For each c in Range("nums")
i = i + 1
varData (i+1) = c.Value
Next

Range("A1").Resize(i,1).Value = varData

--
regards,
Tom Ogilvy


"madge" wrote in message
m...
I have a variable that look like:

varData = "2.2, 3.2, 4.3" and I need to plot this on a spreadsheet
with the numbers in separate cells. i.e. The output needs to look
like:
2.2
3.2
4.3

I cannot figure out how to do this.

varData is a Variant but is not an Array. i.e It's declaration does
not have varData(). I do have an integer (i) that holds the number of
items that were put into varData. The code for this is something
like:

For each c in Range("nums")
varData = varData & "," & c.Value
i = i + 1
Next

But I am stuck as to how I retrieve the info from varData and plot it
into separate cells. I would appreciate any advice with how to handle
this.

Thank you
madge

accessing data from an array
 

Thanks for the explanation Alan. I saw your reply after I had posted my
response to Tom asking him to elaborate but you have already done so
here.

To answer your question, I need to do this because it is part of a
larger fairly complex (for me!) macro where data is picked up on the
fly, manipulated by having various calculations performed upon it
depending on certain factors and then plotted in a chart.

Once the calculations have been performed on Range("Nums"), which is not
a fixed range, I get varData and this needs to be plotted on a
spreadsheet so that the chart can link to the range. I tried plotting
varData directly and this works nicely until I get a situation where the
varData string reaches 1000 characters at which point the macro fails.
I was told that Excel charts cannot handle data strings of more than
1000 chars unless they are presented in a range. So I attempting to
give the charts a range instead of feeding the data directly to their
data series in the form of a string.

Thanks very much for your help.
madge


In article , says...
varData is ReDimmed as a 2-d array, so varData(i+1) throws an error.

When that is changed to varData(i+1,1), the code throws an error at
varData(i+1,1) when i=the number of elements in Nums.

I think the following code does what Tom Ogilvy intended:

Dim varData() As Double
ReDim varData(1 To Range("Nums").Count, 1 To 1)

i = 1
For Each c In Range("Nums")
varData(i, 1) = c.Value
Debug.Print i, (varData(i, 1))
i = i + 1
Next

Range("D1").Resize(i - 1, 1).Value = varData '<--I used D1 'cause
I 'have something else in A1

But that raises the fundamental problem that all it does is create
another range that is identical to Nums; what's the point? I.e., if the
OP already has Nums, she already has the output she said she's seeking!??

Alan Beban

Tom Ogilvy wrote:
Assume Nums is a single column or single row

Dim varData() as double
Redim VarData(1 to Range("Nums").count, 1 to 1)

i = 0
For each c in Range("nums")
i = i + 1
varData (i+1) = c.Value
Next

Range("A1").Resize(i,1).Value = varData

accessing data from an array
 

I hold my hands up - I haven't actually tried it yet. Just that I read
through the code and it has given me an idea of where I was going wrong
and can tackle it afresh when I get to work tomorrow armed with both
yours and Tom's responses.

madge



In article , says...
madge wrote:
Thank you so much Tom. This is just what I needed.

You mean it works???!!

Where did I go wrong? Help us out, Tom.

Alan Beban


Can I trouble you further to ask if you would explain this line to me:
Redim VarData(1 to Range("Nums").count, 1 to 1)

I don't understand the ",1 to 1" bit. Is that creating a second "row"
in the array? If so, what does it hold?

Thanks
madge



"Tom Ogilvy" wrote in message ...

Assume Nums is a single column or single row

Dim varData() as double
Redim VarData(1 to Range("Nums").count, 1 to 1)

i = 0
For each c in Range("nums")
i = i + 1
varData (i+1) = c.Value
Next

Range("A1").Resize(i,1).Value = varData

--
regards,
Tom Ogilvy


"madge" wrote in message
. com...

I have a variable that look like:

varData = "2.2, 3.2, 4.3" and I need to plot this on a spreadsheet
with the numbers in separate cells. i.e. The output needs to look
like:
2.2
3.2
4.3

I cannot figure out how to do this.

varData is a Variant but is not an Array. i.e It's declaration does
not have varData(). I do have an integer (i) that holds the number of
items that were put into varData. The code for this is something
like:

For each c in Range("nums")
varData = varData & "," & c.Value
i = i + 1
Next

But I am stuck as to how I retrieve the info from varData and plot it
into separate cells. I would appreciate any advice with how to handle
this.

Thank you
madge

accessing data from an array
 

This is what I meant:

Sub Tester3()
Dim varData() As Double
ReDim varData(1 To Range("Nums").Count, 1 To 1)

i = 0
For Each c In Range("nums")
i = i + 1
varData(i, 1) = c.Value
Next

Range("A1").Resize(i, 1).Value = varData
End Sub

I had a single typo, a "+" where there was supposed to be a ","

My apologies.

You shouldn't have changed varData(i+1) to varData(i+1,1), all you had to do
was replace the + with a comma which was the typo.

--
Regards,
Tom Ogilvy



Alan Beban wrote in message
...
varData is ReDimmed as a 2-d array, so varData(i+1) throws an error.

When that is changed to varData(i+1,1), the code throws an error at
varData(i+1,1) when i=the number of elements in Nums.

I think the following code does what Tom Ogilvy intended:

Dim varData() As Double
ReDim varData(1 To Range("Nums").Count, 1 To 1)

i = 1
For Each c In Range("Nums")
varData(i, 1) = c.Value
Debug.Print i, (varData(i, 1))
i = i + 1
Next

Range("D1").Resize(i - 1, 1).Value = varData '<--I used D1 'cause
I 'have something else in A1

But that raises the fundamental problem that all it does is create
another range that is identical to Nums; what's the point? I.e., if the
OP already has Nums, she already has the output she said she's seeking!??

Alan Beban

Tom Ogilvy wrote:
Assume Nums is a single column or single row

Dim varData() as double
Redim VarData(1 to Range("Nums").count, 1 to 1)

i = 0
For each c in Range("nums")
i = i + 1
varData (i+1) = c.Value
Next

Range("A1").Resize(i,1).Value = varData