Array Problem??

[Michael Vaughan]

Hello Everyone,

I am having trouble trying to get the information into the array and then
writing it into cells on a different sheet. It seems to be loading into the
array fine, but when it writes it to the sheet, it shows two of the same
thing and it should be two different values. Here is the code:

Up at top I have Dim myArray() As String

(Then a bunch of code here to determine 3 variables.) IPL ='s Indiana Power
and Light, HSC ='s Howard Sewer County, and BID ='s BidPro. Now, My code
has determined that IPL and HSC are values, and BID is "". Then the
following code is continued after determining the variables mentioned above.

Y = 0
If IPL <> "" Then Y = Y + 1
If HSC <> "" Then Y = Y + 1
If BID <> "" Then Y = Y + 1

ReDim myArray(Y)
J = 0
If IPL <> "" Then myArray(J) = IPL: J = J + 1
If HSC <> "" Then myArray(J) = HSC: J = J + 1
If BID <> "" Then myArray(J) = BID: J = J + 1

lLastarray = 10 + J - 1
Sheets("Sheet2").Range("E10:E" & lLastarray).Value = myArray

When I run the code, it shows Indiana Power and Light twice in E10 and E11.
It should show Indiana Power and Light on E10 and Howard Sewer County on
E11. Why isn't it doing that????

Thanks in advance.. mv

 

[keepITcool]

you're assigning a 1 dimension array to a range.

since it's 1dim excel /vba assumes it will go into 1 row/many columns.

BUT you're redimming it along the way (and you can only redim the LAST
dimension.. so you CANNOT use Redim (1 to y,1 to 1)

if you're array is smaller then 5461 elements you can safely use
Range = application.transpose(myarray)

if the array could be larger your users need xlXP or newer.
(xl2000 with certain service packs), or you have to write your own
transpose function to create a 2dim array.



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


Michael Vaughan wrote :

 

[Tom Ogilvy]

He only redim's it once
Dim MyArray() as Long

Y = 0
If IPL <> "" Then Y = Y + 1
If HSC <> "" Then Y = Y + 1
If BID <> "" Then Y = Y + 1

ReDim myArray(0 to Y-1, 0 to 0)
J = 0
If IPL <> "" Then myArray(J,0) = IPL: J = J + 1
If HSC <> "" Then myArray(J,0) = HSC: J = J + 1
If BID <> "" Then myArray(J,0) = BID: J = J + 1

lLastarray = 10 + J - 1
Sheets("Sheet2").Range("E10:E" & lLastarray).Value = myArray


should work.

 

[keepITcool]

yep!..

i didn't read his code properly
i had just read Alan Beban's post "Dim MyArray vs Dim myArray()"
and must'v been confused. <G>


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


Tom Ogilvy wrote :

 

[Michael Vaughan]

Hi Tom,

Well, this one has got me stumped. I put in your code, and it still errors
out as "Type Mismatch"??? It is erroring out when it hits this line: "If
IPL <> "" Then myArray(J, 0) = IPL: J = J + 1".

Y = 0
If IPL <> "" Then Y = Y + 1
If HSC <> "" Then Y = Y + 1
If BID <> "" Then Y = Y + 1

ReDim myArray(0 To Y - 1, 0 To 0)
J = 0
If IPL <> "" Then myArray(J, 0) = IPL: J = J + 1 (ERROR AS
TYPE MISMATCH on this line)
If HSC <> "" Then myArray(J, 0) = HSC: J = J + 1
If BID <> "" Then myArray(J, 0) = BID: J = J + 1

 

[keepITcool]

Tom
defined MyArray as (an arrya of) Long
(in your code it was a variant)

your IPL seems to be a string..


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


Michael Vaughan wrote :

 

[Tom Ogilvy]

I put in
Dim MyArray() as Long

just to illustrate that myArray needed to by initially dimensioned as a
dynamic array. I missed this statement in the original message

Up at top I have Dim myArray() As String

so long was chosen arbitrarily as I saw no indication in the original code
of what it should be. (having missed the above line)


So it appears the intention is string, but perhaps variant would be more
robust.