Results 1 to 7 of 7

Thread: Array: how to populate it starting from a cell's value up to zero

  1. #1
    Board Regular
    Join Date
    Aug 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Array: how to populate it starting from a cell's value up to zero

    Hello,

    I am new to arrays and am really struggling with the concept.

    Here is what I am trying to achieve:

    in range("B3:B9") I have some numeric values.

    Let's say that the value in B3 is 37: I would like to create an array that contains all values from 0 up to 37 based to an incremental factor set in cell A1 (1 by default).

    I would then like to loop through that array and populate cell B3 with its items one by one, i.e. value in B3 starts at zero and stops at 37. The same should happen for the entire range ("B3:B9").

    Thank you in advance for your help.
    Last edited by Bering; Sep 14th, 2019 at 12:55 PM.

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

    Default Spam

    Try this Macro
    Code:
    Option Explicit
    Sub test()
    Dim Main_array
    Dim My_val
    Dim k%, Inc%, x
    Dim ro%, col%
    ro = 3: col = 4
    Range("d4").CurrentRegion.ClearContents
    Main_array = Evaluate(Application.Transpose("B3:B9"))
    Main_array = Application.Transpose(Main_array)
    For k = LBound(Main_array) To UBound(Main_array)
       My_val = Main_array(k)
        For x = 1 To My_val Step [A1]
         Cells(ro, col) = x
         col = col + 1
        Next x
     ro = ro + 1: col = 4
    Next k
    End Sub
    A B C D E F G H I J K
    1 5
    2
    3 37 1 6 11 16 21 26 31 36
    4 7 1 6
    5 17 1 6 11 16
    6 10 1 6
    7 8 1 6
    8 14 1 6 11
    9 3 1
    Sheet1

  3. #3
    Board Regular
    Join Date
    Aug 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array: how to populate it starting from a cell's value up to zero

    Thank you sooo much!!!

    Since I want the changes to occur in the same initial range B3:B9, I have slightly changed your code:

    Code:
    Sub test()Dim Main_array
    Dim My_val
    Dim k%, Inc%, x
    Dim ro%, col%
    ro = 3: col = 2
    'Range("d4").CurrentRegion.ClearContents
    Main_array = Evaluate(Application.Transpose("B3:B9"))
    Main_array = Application.Transpose(Main_array)
    For k = LBound(Main_array) To UBound(Main_array)
       My_val = Main_array(k)
        For x = 1 To My_val Step [A1]
         Cells(ro, col) = x
         'col = col + 1
        Next x
     ro = ro + 1 ': col = 4
    Next k
    End Sub

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,249
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Array: how to populate it starting from a cell's value up to zero

    I am not sure if you asked for an array solution because you are taking a class and are currently studying arrays, but what you asked for can be done without using arrays in a lot less lines of code...
    Code:
    Sub Test()
      With Range("B3", Cells(Rows.Count, "B").End(xlUp))
        .Value = Evaluate(Replace("IF(@="""","""",IF(@<A1,1,A1*(ROUNDUP((@-A1+1)/A1,0)-(MOD(@,A1)=0))+1))", "@", .Address))
      End With
    End Sub
    Last edited by Rick Rothstein; Sep 14th, 2019 at 06:24 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

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

    Default Re: Array: how to populate it starting from a cell's value up to zero

    [QUOTE=Rick Rothstein;5342017]I am not sure if you asked for an array solution because you are taking a class and are currently studying arrays, but what you asked for can be done without using arrays in a lot less lines of code...
    [CODE]Sub Test()
    With Range("B3", Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",IF(@ End With
    End Sub
    We learn from you sir Rothstien
    What you saw abut this code
    Code:
    Sub Other_macro()
        If IsEmpty([a1]) Or Not IsNumeric([a1]) _
            Or [a1] = 0 Then
            [a1] = 1
         Else
             [a1] = Int([a1])
         End If
        With Range("B3", Cells(Rows.Count, "B").End(3)).Offset(, 2)
          .FormulaArray = _
            "=" & Replace("INT(" & "Any_Thing" & "/$A$1)*$A$1", "Any_Thing", .Offset(, -2).Address)
          .Value = .Value
        End With
    End Sub

  6. #6
    Board Regular
    Join Date
    Aug 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array: how to populate it starting from a cell's value up to zero

    Thank you Rick, indeed I am trying to rewrite some old projects I worked on using arrays.

    I tried the code you suggested but what I really need is the value in the cell to progressively increase from zero up to the original value through a loop. I am using this method to create animated charts and dashboards.

    I will try and modify your code to work like that. Many thanks.

    Quote Originally Posted by Rick Rothstein View Post
    I am not sure if you asked for an array solution because you are taking a class and are currently studying arrays, but what you asked for can be done without using arrays in a lot less lines of code...
    Code:
    Sub Test()
      With Range("B3", Cells(Rows.Count, "B").End(xlUp))
        .Value = Evaluate(Replace("IF(@="""","""",IF(@
    

  7. #7
    Board Regular
    Join Date
    Aug 2018
    Posts
    56
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Array: how to populate it starting from a cell's value up to zero

    [QUOTE=salim hasan;5342060]
    Quote Originally Posted by Rick Rothstein View Post
    I am not sure if you asked for an array solution because you are taking a class and are currently studying arrays, but what you asked for can be done without using arrays in a lot less lines of code...
    [CODE]Sub Test()
    With Range("B3", Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate(Replace("IF(@="""","""",IF(@ End With
    End Sub

    Code:
    Sub Other_macro()
        If IsEmpty([a1]) Or Not IsNumeric([a1]) _
            Or [a1] = 0 Then
            [a1] = 1
         Else
             [a1] = Int([a1])
         End If
        With Range("B3", Cells(Rows.Count, "B").End(3)).Offset(, 2)
          .FormulaArray = _
            "=" & Replace("INT(" & "Any_Thing" & "/$A$1)*$A$1", "Any_Thing", .Offset(, -2).Address)
          .Value = .Value
        End With
    End Sub
    Thank you Salim, I will give it try

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
  •