×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

transfer of array variables into a range

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

RE: transfer of array variables into a range

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").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

(OP)
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

RE: transfer of array variables into a range

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.

RE: transfer of array variables into a range

(OP)
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").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

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

RE: transfer of array variables into a range

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources