Array via VBA to cells

[somkiat]

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

 

[brettvba]

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

 

[somkiat]

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

 

[Aladin Akyurek]

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

 

[somkiat]

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)),AllRecordNum),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?

 

[Aladin Akyurek]

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)),AllRecordNum),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

 

[Ivan F Moala]

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)),AllRecordNum),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 ??

 

[Aladin Akyurek]

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

 

[nisht]

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