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

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

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.

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

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.