How to create a range from a cell value and repeat time in another cell ?
Results 1 to 7 of 7

Thread: How to create a range from a cell value and repeat time in another cell ?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    669
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 ?

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,567
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

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

    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 :
    Code:
    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]

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Posts
    669
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Last edited by mrchonginhk; Jul 21st, 2019 at 08:31 PM.

  4. #4
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,567
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

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

    I don't understand what you want to do.

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Posts
    669
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 ?

  6. #6
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,567
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

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

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

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,499
    Post Thanks / Like
    Mentioned
    87 Post(s)
    Tagged
    19 Thread(s)

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

    Quote Originally Posted by mrchonginhk View Post
    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.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

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
  •