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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
317
TheWennerWoman
T
L
  • Solved
Formatting cells filled by array formulae
Replies
5
Views
216
Ludwig
L
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
283
Pingaware
P
A
  • Question Question
Populate Monthly Expenses Based on Date Range
Replies
3
Views
99
myall_blues
myall_blues
R
  • Question Question
Freezing/Logging Data from DDE at 1 Minute Intervals
Replies
1
Views
110
Anthony47
A

Forum statistics

Threads
1,224,747
Messages
6,180,719
Members
452,995
Latest member
isldboy

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