PDA

View Full Version : Solved: Assigning Values to Multiple Cells



Cyberdude
11-10-2005, 11:36 AM
I have a range of three cells in a column ["Range("A1:A3"] to which I want to assign three constant values [1, 2, 3].
My first inclination was to write the statement:
Range("A1:A3") = Array(1, 2, 3)
which of course is incorrect. To use the "Array" function, I have to assign the values to an array variable (probably a variant). My question is: Can I do an assignment without using an array variable ... a statement that looks something like the statement shown above. I'm wondering if I can do the job with one statement without using an array variable as the target. Hmmmm??

mdmackillop
11-10-2005, 11:46 AM
Hi Sid

Range("A1:A3") = Application.WorksheetFunction.Transpose(Array(1, 2, 3))

Cyberdude
11-10-2005, 12:15 PM
Hi, Malcolm! Thanx. You know, I've used that before and just forgot about it. http://vbaexpress.com/forum/images/smilies/friendship.gif

mvidas
11-10-2005, 12:30 PM
Just as an added tidbit, for a 1-dimensional array (like that generated from the Array() method), you only need to .Transpose it when putting into a single column. For a row, you don't need itRange("A1:A3").Value = Application.Transpose(Array(1, 2, 3))
Range("C1:E1").Value = Array(4, "five", 2 + 4)

Also, though I don't think you'll run into this here, the .Transpose method can only be done for up to 5461 cells! Anything beyond that you'll get a type mismatch error, requiring you to loop through the array and putting the values in 1 by 1.

Matt

Cyberdude
11-11-2005, 09:13 AM
Thanks, Matt. I didn't realize there is a limit on transpose, although (as you say) I doubt if I'd ever exceed it.

mvidas
11-11-2005, 09:31 AM
I've reached the limit many times, took a long time to figure out why i was getting a 'type mismatch' error (to me, doesnt fit for this limit). Most of the time nowadays I just loop through it anyways :)