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

Bering

Board Regular
Joined
Aug 22, 2018
Messages
176
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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
15
2
33716111621263136
4716
517161116
61016
7816
8141611
931

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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
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)>
 
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.

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

</a1,1,a1*(roundup((@-a1+1)>
 
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

[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)>

Thank you Salim, I will give it try
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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