transfer of array variables into a range 1

[m777182]

How could I transfer values of an array, say x(1),x(2),...x(k) into a range,say range_of_x?
m777182

 

[onlyadrafter]

Hello,

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&quot.Select
For NOS = 1 To 20
x(NOS) = ActiveCell
Selection.Offset(1, 0).Select
Next NOS
Range("c1&quot.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!

 

[m777182]

thanks, onlyadrafter. You proposed a way that works fine but I would like to exclude the transfer of values x(i) through a cells of a spread sheet. My range_of_x is declared As Range and its dimenson is not given in advance.
I 'll keep the idea if nothing better comes.
M777182

 

[joerd]

It is not quite clear to me what you want to do:

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.

 

[m777182]

Hi onlyadrafter,
here I've got a piece of code that works:

rem initiate a range by some odd instruction:
Set range_of_x = Worksheets("sheet1&quot.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

 

[m777182]

Thanks, Joerd!
I got a moment of inspiration and done it.
M777182

 

[folk754]

You can do as follows:

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