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

B

Bering

Board Regular
Joined
Aug 22, 2018
Messages
186
Office Version
  1. 2016
Platform
  1. Windows
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:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
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
ABCDEFGHIJK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]36[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
 
Upvote 0
Solution
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
 
Upvote 0
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:
Upvote 0
Rick Rothstein said:
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
[QUOTE]
We learn from you sir Rothstien
What you saw abut  this code
[/QUOTE]
[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
</a1,1,a1*(roundup((@-a1+1)>
Click to expand...
 
Upvote 0
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.

Rick Rothstein said:
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
Click to expand...

</a1,1,a1*(roundup((@-a1+1)>
 
Upvote 0
salim hasan said:
Rick Rothstein said:
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

[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
</a1,1,a1*(roundup((@-a1+1)>
Click to expand...

Thank you Salim, I will give it try
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Amend values populating an array?
Replies
2
Views
315
TheWennerWoman
T
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
115
Ludwig
L
N
  • Solved
For next loop with array and using a variable in range().select
Replies
5
Views
591
notmeiquit
N
S
  • Solved
RE-manipulation of an array from a UDF
Replies
9
Views
415
StephenCrump
S
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
112
Alex Blakenburg
A

Forum statistics

Threads
1,223,705
Messages
6,173,996
Members
452,542
Latest member
Bricklin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top