How to create a range from a cell value and repeat time in another cell ?

[mrchonginhk]

Right now I have a data A1:A100 each cell is 500
So my range called RangeA is A1:A100
Can I do without need to have this list in this 100 rows



Now I key in, B1=500, C1=100
How can I create RangeA based on these two cell values ?

 

[footoo]

If you mean you want to copy the value in B1 and enter it from A1 down to the row number in C1, and then create a named range ("RangeA") which refers to the rows with data in column A :

Dim rng$: rng = "RangeA"
On Error Resume Next
ActiveWorkbook.Names(rng).Delete
On Error GoTo 0
ActiveWorkbook.Names.Add Name:=rng, RefersTo:="=Sheet1!$A$1:$A$" & [C1]
Range("A2:A" & Cells(Rows.Count, "A").End(3).Row).ClearContents
Range(rng).Value = [B1]

 

[mrchonginhk]

Thanks. I forget I cannot use VBA in the company.

How to do it without VBA ? Will array work ?
Those 500 are fixed and do not change.

 

[footoo]

I don't understand what you want to do.

 

[mrchonginhk]

Basically I mean I need to create a range of 100 cells each is the same "500"
How to do this using cell value 500 and 100 ?

 

[footoo]

Perhaps :
Enter in A1:A100 =$B$1

 

[Peter_SSs]

Now I key in, B1=500, C1=100
How can I create RangeA based on these two cell values ?

Try in A1, Copied down as far as you might ever need

=IF(ROWS(A$1:A1)>C$1,"",B$1)

Now try changing B1 and/or C1 and look at the new values in column A.