Using Loop to create Array
 

Can someone identify the problem in the following code which seeks to
create an array using the product of a loop.

Sub CreateArray()

For i = 1 to 100 Step 0.5
k = k & """" & "Level" & """" & i
Next
'create the array
v =Array(Left(k,Len(k)-1))

For j = LBound(v) to UBound(v)
Debug.print v(j)
Next

Problem is v(j) returns blank although if the ouput of debug.print
Left(k,Len(k)-1) is slotted into v =Array(Left(k,Len(k)-1)), the code
wprks fine. What am I missing?

TIA


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=379247

Using Loop to create Array
 

I don't catch it - can u just paste the "wrong code" into procedure ?


Can someone identify the problem in the following code which seeks to
create an array using the product of a loop.

Sub CreateArray()

For i = 1 to 100 Step 0.5
k = k & """" & "Level" & """" & i
Next
'create the array
v =Array(Left(k,Len(k)-1))

For j = LBound(v) to UBound(v)
Debug.print v(j)
Next

Problem is v(j) returns blank although if the ouput of debug.print
Left(k,Len(k)-1) is slotted into v =Array(Left(k,Len(k)-1)), the code
wprks fine. What am I missing?

TIA


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=379247

Using Loop to create Array
 

The problem is, it sees the contents of Left(k, len(k)-1) as one big
string. All those apostrophes are just elements of the string, not
separators.

An easier way to do it is to use the split() function:

For i = 1 To 100 Step 0.5
k = k & "Level " & i & ","
Next
'create the array
v = Split(Left(k, Len(k) - 1), ",")

For j = LBound(v) To UBound(v)
Debug.Print v(j)
Next

Also, I changed the <<k = k & """" & "Level" & """" & i line because
I couldn't quite understand what you were trying to do.

Using Loop to create Array
 

maybe you're new to vba and it looks to me your're trying to create an
array the same way you would in a worksheet formula.

although you could do it using split (if you have xl2000+)
(see post from Nick Hebb)

in VBA functions cant work with arrays like this:
Left(array,3)

VBA functions are meant for single values, not for arrays.

in some case you can use application.worksheetfunction
or worksheet.evaluate("=left(a1:a10,3)")
to process arrays and get an array as the result.
but those are the exceptions not the rule.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


davidm wrote :


Can someone identify the problem in the following code which seeks to
create an array using the product of a loop.

Sub CreateArray()

For i = 1 to 100 Step 0.5
k = k & """" & "Level" & """" & i
Next
'create the array
v =Array(Left(k,Len(k)-1))

For j = LBound(v) to UBound(v)
Debug.print v(j)
Next

Problem is v(j) returns blank although if the ouput of debug.print
Left(k,Len(k)-1) is slotted into v =Array(Left(k,Len(k)-1)), the code
wprks fine. What am I missing?

TIA

Using Loop to create Array
 

Another way using VBA similarly to what you are trying

ReDim v(1 To 1)
j = 1
For i = 1 To 100 Step 0.5
ReDim Preserve v(1 To j)
v(j) = "Level" & i
j = j + 1
Next
For j = LBound(v) To UBound(v)
Debug.Print v(j)
Next


--
HTH

Bob Phillips

"Nick Hebb" wrote in message
oups.com...
The problem is, it sees the contents of Left(k, len(k)-1) as one big
string. All those apostrophes are just elements of the string, not
separators.

An easier way to do it is to use the split() function:

For i = 1 To 100 Step 0.5
k = k & "Level " & i & ","
Next
'create the array
v = Split(Left(k, Len(k) - 1), ",")

For j = LBound(v) To UBound(v)
Debug.Print v(j)
Next

Also, I changed the <<k = k & """" & "Level" & """" & i line because
I couldn't quite understand what you were trying to do.