Referring to ranges as Cell, Range, and variables

[CompleteNewb]

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).Offset(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]

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

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).Offset(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.

 

[norie]

A9 is a string not a range and you can't use Range like that anyway.

If you want to create a range using 2 range references you need
something like this.

Worksheets("MySheet").Range(Worksheets("MySheet").Range("A" &
Rows.Count).End(xlUp).Offset(1).Address,
Worksheets("MySheet").Range("A" & UBound(Myarray) + 1).Address)

Rather unwieldy.

Why not try using some other method, perhaps Resize or Offset?

Which to use and how to use them depends on what range you actually
want to refer to is.

It might also be worth splitting the code up a bit and creating a
range reference:

addr1 = Worksheets("MySheet").Range("A" &
Rows.Count).End(xlUp).Offset(1).Address

addr2 = Worksheets("MySheet").Range("A" & UBound(Myarray) + 1).Address

Set rng = Worksheets("MySheet").Range(addr1, addr2)

 

[CompleteNewb]

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.

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

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).Offset(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.

 

[Gord Dibben]

Dave's code fills a,b,c,d into the first 4 blank cells below last cell in column
A

Works fine for me.

Try copy/pasting the code rather than typing.


Gord Dibben MS Excel MVP

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.

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

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).Offset(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]

Or share your updated code and also share what was in that array. Maybe your
array didn't get created the way you wanted.

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.

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

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).Offset(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.

 

[CompleteNewb]

I'm sorry, the problem was I forgot the () on the dimming of the array:

Dim MyArray() as variant.

This is probably not needed when you fill it specifically with known values,
as in your example, but I was using my own arrayname at first. My
apologies.

Thanks for the hlp on this, it's panning out to be useful in several of my
other problems I was having.

Or share your updated code and also share what was in that array. Maybe
your array didn't get created the way you wanted.

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.

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).Offset(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.