Thread: Array: how to populate it starting from a cell's value up to zero Thanks:  3 Post #5342017 (1)Post #5342060 (1)Post #5341998 (1) Likes:  3 Post #5341998 (1)Post #5342017 (1)Post #5342060 (1)

1. 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.  Reply With Quote

2. 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  Reply With Quote

3. 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  Reply With Quote

4. 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  Reply With Quote

5. 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  Reply With Quote

6. 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. Originally Posted by Rick Rothstein 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(@  Reply With Quote

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

[QUOTE=salim hasan;5342060] Originally Posted by Rick Rothstein 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  Reply With Quote

User Tag List

Tags for this Thread

array, cell, happen, populate, values  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•