Ok, I'm just discovering the wonderful things arrays can do. What I'd like to do is assign a certain range of cells to an array value, and then insert those values into another sheet somewhere else in my VBA code. Does anyone know the syntax or how I might go about doing this??
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
me and arrays
- Thread starter scotawful
- Start date
the thing is, I'd like the range assigned to the array to be dynamic. I've written other codes where I use the cut/paste methods, and cycle through different ranges repeatedly with 'for' statements and offset commands, but it gets ugly. My code would be much cleaner if I could set a value like:
myarray = array(range("d33:336").value)
or something simmilar. I know this code is nowhere near what I'm supposed to do, but I think you an get the idea. It would be great if there were some way to assign a range of cells to a single variable, and then later recall the values in that range. Is this even possible??
myarray = array(range("d33:336").value)
or something simmilar. I know this code is nowhere near what I'm supposed to do, but I think you an get the idea. It would be great if there were some way to assign a range of cells to a single variable, and then later recall the values in that range. Is this even possible??
Upvote
0
NateO
Legend
- Joined
- Feb 17, 2002
- Messages
- 9,700
Probably, the question remains, why (in terms of assigning the values to an array)? There probably isn't a real need for this, try asssigning the range in question to a range object. Try something like the following, it's dynamic based on z's input range:
In a certain sense, it is grabbing an array of Excel cells and passing them.
But yes, this will will work:
But it's only a 1-d. You'll need to loop otherwise.
Hope this helps.
Code:
Sub tryMe()
Dim z As Range
Set z = Range("d33:f336")
Sheets(2).[a51].Resize(z.Rows.Count, z.Columns.Count).Value = z.Value
End SubIn a certain sense, it is grabbing an array of Excel cells and passing them.
But yes, this will will work:
Code:
Dim f
f = Application.Transpose([d33:d336].Value)But it's only a 1-d. You'll need to loop otherwise.
Hope this helps.
Upvote
0
Oh yes, this works like a charm. You may not see the point, but I'm writing code where I have to reference the same size range of cells multiple times, but from difference reference points on the sheet each time. My previous cut/paste method within a loop got to be very bulky after a little while. This cuts down the code size and complexity quite a bit. Thanks a lot.
Upvote
0
tusharm
MrExcel MVP
- Joined
- May 28, 2002
- Messages
- 11,029
Along the lines of what Nate mentioned, it seems you are caught up in discovering the wonders of a new tool.
If all you want to do is copy an arbitrary chunk of cells and paste that elsewhere, just use (the untested):
If all you want to do is copy an arbitrary chunk of cells and paste that elsewhere, just use (the untested):
Code:
Range("b23:d35").copy destination:=range("x55")
Upvote
0
Similar threads
- Solved
