Dave, thanks for trying to help, but I'm getting invalid use of Resize error
when trying to do below; in case it was the way I was referring to my ranges
as variables, or cells, or some other incorrect way of doing things, I tried
doing exactly what's below, and still got invalid use of resize (even though
the vba editor was putting up the autocomplete help as I was putting
"resize" in.


"Dave Peterson" wrote in message
...
I'd break it down into smaller pieces...

Option Explicit
Sub testme()

Dim myArray As Variant
Dim DestCell As Range

myArray = Array("a", "b", "c", "d")

With Worksheets("mySheet")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

DestCell.Resize(UBound(myArray) - LBound(myArray) + 1, 1).Value _
= Application.Transpose(myArray)

End Sub



On 10/15/2010 02:20, CompleteNewb wrote:
I have been experimenting with the whole Cells([whatever, whatever]),
Range(Whatever, whatever), using variables that are ranges, etc., and I
swear
every time I think I have figured out how to refer to things, I get the
error
about type mismatch. I mean really.

Can anyone tell me how to get this to work:

Worksheets("MySheet").Range("A65536").End(xlUp).Of fset(1, 0).Address ,
("A" &
UBound(MyArray) + 1) = Application.WorksheetFunction.Transpose(MyArray)

This is supposed to find the first blank cell in A, set the range from
there to
the number of elements in my array down, then put in my array values. I
can
debug.print the 1st address, printing ("A" & UBound(MyArray) + 1) IS
giving me
"A9" (there are 8 elements in my array), so that's working, and I can
print the
elements in the array. I set a range and filled it EXACTLY like this
before,
except for that I was using Range("A1:A" & UBound(MyArray) + 1), and that
worked
perfectly. This seems so slightly different I can't figure out whay I'd
get type
mismatch.

As long as I'm talking about it, does anyone know how to paste an array's
values
by just selecting the first cell you want? In Excel, if you have
ranges/values
in the clipboard, you can click just one cell and paste; is there a way
in vba
to just click one cell and transpose your array successfully, besides
looping
through every element and offsetting every time?

Thanks again. I know I'm posting a lot, but believe me, I'm spending
hours on
everything I can't quite get. And I found out how to do some great stuff
I never
knew about before, but unfortunately none of it applies to what I'm
doing.


--
Dave Peterson