Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
accessing data from Access | Excel Discussion (Misc queries) | |||
Accessing List Data | Excel Discussion (Misc queries) | |||
Accessing Data from Closed Workbook | Excel Programming | |||
Accessing Data from Closed Workbook | Excel Programming | |||
Accessing properties of a series with no data | Excel Programming |