Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Array via VBA to cells

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Bangkok, Thailand
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a constant name : MyNum
    Refers to an number array ={1;2;3;4;5}

    and a range name : MyRange
    Refers to =A1:A5

    With vba, how to send value 1-5 to cells A1:A5

    To get
    A1: 1
    A2: 2
    A3: 3
    A4: 4
    A5: 5

    Thank you so much

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sub ArrayMac()
    MyNum = Array(1, 2, 3, 4, 5)
    Range("A1").Select
    For Each Number In MyNum
    ActiveCell.Value = Number
    ActiveCell.Offset(1, 0).Select
    Next

    End Sub

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Bangkok, Thailand
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks but what I need is not making array in vba code.

    If we can set number array from constant name, we should be able to send any new numbers thru this name. It should be easier than changing vba code.

    My real question is far difficult. I have build an array formula and refered as a formula name. This array formula returns 20 numbers.

    I need to send results from this array formula to 20 cells. (send value to cells, not send formula to cells)

    Thanks,
    Somkiat
    http://www.xls.i.am

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-28 20:29, somkiat wrote:
    I have a constant name : MyNum
    Refers to an number array ={1;2;3;4;5}

    and a range name : MyRange
    Refers to =A1:A5

    With vba, how to send value 1-5 to cells A1:A5

    To get
    A1: 1
    A2: 2
    A3: 3
    A4: 4
    A5: 5

    Thank you so much
    Good to see you here.

    If MyNum is defined via Insert|Name|Define, the following non-VBA approach would work:

    In A1 enter and copy down:

    =INDEX(MyNum,ROW())

    or

    =IF(COUNT(MyNum)<=ROW(),INDEX(MyNum,ROW()),"")


    By the way, did you ever look at Longre's morefunc add-in? He has a UDF, called EVAL, that might interest you (as I recall our exchange at the old board on evaluating a dynamically constructed formula).

    It's downloadable at:

    http://perso.wanadoo.fr/longre/excel/downloads/

    Aladin

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Bangkok, Thailand
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nice to talk with you again, Aladin.

    I make an array formula to get an unique list of Order_ID

    {=INDEX(Order_ID,INDEX(SMALL(IF(ISBLANK(Order_ID),FALSE(),IF(MATCH(Order_ID,Order_ID,0)=AllRecordNum,AllRecordNum)),AllR ecordNum),RecordNum,1),1)}

    This formula returns an array of
    {10248;10249;10250;10251;.......11747}
    into cells correctly.

    Order_ID ia a range of Order ID such as 10248, 10248, 10248, 10249,10249, 10250. One transaction can have many orders with the same order_id. There are 1500 orders.

    AllRecordNum is a range of consecutive number from 1;2;3;4;5;....to 1500.

    RecordNum is a range of consecutive number from 1;2;3;4;5;...to 20. I use these numbers to show the first 20 order_ids from its unique list.

    Then I create a formula name as IdSource and refered to that formula.

    With the following vba, it returns falsely only the first unique id instead of all unique list into UniqueID range:

    Sub GetUniqueID()
    myvar = range("IdSource")
    range("UniqueID") = myvar
    End Sub

    I need this answer to UniqueID range:
    10248
    10249
    10250
    ......+1

    not
    10248
    10248
    10248
    10248
    10248
    10248

    How comes only the first item in array formula pass to vba?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-04-29 03:56, somkiat wrote:
    Nice to talk with you again, Aladin.

    I make an array formula to get an unique list of Order_ID

    {=INDEX(Order_ID,INDEX(SMALL(IF(ISBLANK(Order_ID),FALSE(),IF(MATCH(Order_ID,Order_ID,0)=AllRecordNum,AllRecordNum)),AllR ecordNum),RecordNum,1),1)}

    This formula returns an array of
    {10248;10249;10250;10251;.......11747}
    into cells correctly.

    Order_ID ia a range of Order ID such as 10248, 10248, 10248, 10249,10249, 10250. One transaction can have many orders with the same order_id. There are 1500 orders.

    AllRecordNum is a range of consecutive number from 1;2;3;4;5;....to 1500.

    RecordNum is a range of consecutive number from 1;2;3;4;5;...to 20. I use these numbers to show the first 20 order_ids from its unique list.

    Then I create a formula name as IdSource and refered to that formula.

    With the following vba, it returns falsely only the first unique id instead of all unique list into UniqueID range:

    Sub GetUniqueID()
    myvar = range("IdSource")
    range("UniqueID") = myvar
    End Sub

    I need this answer to UniqueID range:
    10248
    10249
    10250
    ......+1

    not
    10248
    10248
    10248
    10248
    10248
    10248

    How comes only the first item in array formula pass to vba?
    Somkiat,

    INDEX cannot return a constant array of values, just a scalar value. That's why you see just one value in the first place.

    May I suggest a different route.

    Apply Longre's UDF UNIQUEVALUES to the range of interest:

    =UNIQUEVALUES(Order_ID,1)

    The second arg means ascending order.

    You can make it a named formula (e.g., UnIds) if you wish.

    In A2 enter:

    =IF(ROW()-1<=20,INDEX(UnIds,Row()-1)

    will, as I noted earlier, reproduce 20 of unique id's.

    You could of course check whether UnIds gives a count of 20 by

    =COUNT(UnIds)

    and even incorporate this in the first formula.

    Regards,

    Aladin



  7. #7
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-29 03:56, somkiat wrote:
    Nice to talk with you again, Aladin.

    I make an array formula to get an unique list of Order_ID

    {=INDEX(Order_ID,INDEX(SMALL(IF(ISBLANK(Order_ID),FALSE(),IF(MATCH(Order_ID,Order_ID,0)=AllRecordNum,AllRecordNum)),AllR ecordNum),RecordNum,1),1)}

    This formula returns an array of
    {10248;10249;10250;10251;.......11747}
    into cells correctly.

    Order_ID ia a range of Order ID such as 10248, 10248, 10248, 10249,10249, 10250. One transaction can have many orders with the same order_id. There are 1500 orders.

    AllRecordNum is a range of consecutive number from 1;2;3;4;5;....to 1500.

    RecordNum is a range of consecutive number from 1;2;3;4;5;...to 20. I use these numbers to show the first 20 order_ids from its unique list.

    Then I create a formula name as IdSource and refered to that formula.

    With the following vba, it returns falsely only the first unique id instead of all unique list into UniqueID range:

    Sub GetUniqueID()
    myvar = range("IdSource")
    range("UniqueID") = myvar
    End Sub

    I need this answer to UniqueID range:
    10248
    10249
    10250
    ......+1

    not
    10248
    10248
    10248
    10248
    10248
    10248

    How comes only the first item in array formula pass to vba?
    Note 100% sure of what you require.
    Care to email workbook to me ??
    I sought of know what you are after ??
    Kind Regards,
    Ivan F Moala From the City of Sails

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,049
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-05-01 03:44, Ivan F Moala wrote:
    Note 100% sure of what you require.
    Care to email workbook to me ??
    I sought of know what you are after ??
    Ivan,

    The following is the case:

    Insert|Name|Define.
    Name is ConArr.
    Refers to is:

    ={45;56;60;70}

    The contents of this array must go to a set range, say, A1:A4 (a column range) such that after the code is run, we should see to appear:

    45 in A1,
    56 in A2,
    60 in A3,
    70 in A4.

    Hope this clarifies the intent & big thanks.

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-05-01 04:40 ]

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have got two files..

    one is invoicing.. which generates unique invoice numbers in different ways..

    and second is entering only unique values..

    file nos.14 and file nos 27..
    you can study the code for your need.
    http://www.pexcel.com/download.htm
    ni****h desai
    http://www.pexcel.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •