transfer of array variables into a range
transfer of array variables into a range
(OP)
How could I transfer values of an array, say x(1),x(2),...x(k) into a range,say range_of_x?
m777182
m777182
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
transfer of array variables into a range
|
RE: transfer of array variables into a range
THis may not be the best code on the world, but it seems to work. I created it in Excel and it will read in a list of 20 numbers and then copy them to a different row, so manipulation of the code will be required.
Hope this is what you require.
Dim x(10000) As Long
Dim RANGE_OF_X(10000) As Variant
Sub Macro1()
Range("a1").Select
For NOS = 1 To 20
x(NOS) = ActiveCell
Selection.Offset(1, 0).Select
Next NOS
Range("c1").Select
For NEW_X = 1 To NOS
RANGE_OF_X(NEW_X) = x(NEW_X)
ActiveCell = RANGE_OF_X(NEW_X)
Selection.Offset(1, 0).Select
Next NEW_X
End Sub
Hope this helps.
----------------------------------
maybe only a drafter
but the best user at this company!
RE: transfer of array variables into a range
I 'll keep the idea if nothing better comes.
M777182
RE: transfer of array variables into a range
1. If range_of_x is declared as Range, it must refer to an object (i.e. a range on a worksheet) before you can do anything with it. The Range object has no meaning in Visual Basic itself, but is a part of Excel. However, as soon as you set, for example, Set range_of_x = Range("A1:A10"), the active sheet will be updated.
2. If you just want a single variable range_of_x, you can declare Dim range_of_x As Variant, and then simply put range_of_x = x in your code. You can then pass range_of_x to another sub or function, for example. It is then a Variant containing an array (look for arrays in the Help). If range_of_x is the result of a function, you can retrieve the values in the worksheet by pressing CTRL+ENTER after entering the formula (like an array formula)
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: transfer of array variables into a range
here I've got a piece of code that works:
rem initiate a range by some odd instruction:
Set range_of_x = Worksheets("sheet1").Cells(1, 1)
rem now get to work:
For j = 1 To 5
rangex.Item(j) = x(j)
Next j
Comment : could I escape the uggly initiation?
That's all.
m777182
RE: transfer of array variables into a range
I got a moment of inspiration and done it.
M777182
RE: transfer of array variables into a range
dim r as range, arr()
arr=selection
r.value=arr() 'In this one, I am not sure, and you may have to do a For-Next loop to refill the data into the range