Array Problem??
 

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

Array Problem??
 

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 :

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

Array Problem??
 

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.

--
Regards,
Tom Ogilvy


"keepITcool" wrote in message
ft.com...


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 :

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

Array Problem??
 

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 :

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.

Array Problem??
 

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



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

Array Problem??
 

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 :

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



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

Array Problem??
 

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.

--
Regards,
Tom Ogilvy


"keepITcool" wrote in message
ft.com...


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 :

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



"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy