repetitive pattern number generation

[azizunreal]

Hello,


I'm trying to make a matrix of point cloud data. Each number in colums A,B,C will plot itself as a point/pixel in my program. I've done a manual numbering and wondering if I can automate it?


For example, to make a cube 10 x 10 x 10 pixels, I had to do the following:


A B C

0 0 1

0 0 2

0 0 3


and so on until I got to 0 0 10


Now column B starts to increment by +1:

0 1 1

0 1 2

0 1 3


and so on until it reaches

0 10 10


Now column A starts to increment by +1


1 0 1

1 0 2

1 0 3


and so on until this reaches 10 10 10.


In the end, I ended up with 1221 lines in excel. Can I automate it so that if I put 2000 in a cell, these numbers will regenerate on their own up to line 2000 in excel?


I can send the original excel file I made for reference if you require.

 

[NARAYANK991]

Hi Aziz ,


From your post it is not clear what the input will be ; first you say that for a 10 x 10 x 10 cube , the required points need to be generated ; later on , you say that if you put 2000 in a cell , you want 2000 points to be generated.


If you enter 2000 , the maximum cube that can be generated is 12 x 12 x 12 , since 13 x 13 x 13 is 2197.


Or is it that if you enter 2000 , you wish to go beyond 12 x 12 x 12 as far as it is possible ?


Narayan

 

[azizunreal]

Hi Narayan,


Thank you for responding. You are correct, I wish to go as far as possible. So if I enter any number 2000, 4000 or 5500, Excel should generate those many lines, creating a larger cube.


Right now 1221 lines gives me 10 x 10 x 10. I want to be make larger cubes by automating the process.

 

[DaveTurton]

The below macro will work. Put your desired end value (i.e. 20000) in cell D1

[pre]

Sub test()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim n As Integer
Dim End_value As Integer

Application.ScreenUpdating = False

i = 0 'value for A
j = 0 'value for B
k = 0 'value for C

l = 0 'Counter for A
m = 0 'Counter for B
n = 0 'Counter for C

End_value = Cells(1, 4).Value

Cells(1, 1).Select

Do

If k = 11 Then
j = j + 1
k = 0
End If

If j = 11 Then
i = i + 1
j = 0
End If

ActiveCell.Value = i
ActiveCell.Offset(0, 1).Value = j
ActiveCell.Offset(0, 2).Value = k

l = l + 1
m = m + 1
n = n + 1

k = k + 1

ActiveCell.Offset(1, 0).Select

Loop Until i * j * (k - 1) = End_value

Application.ScreenUpdating = True

End Sub

[/pre]

 

[azizunreal]

Hello Dave,


It worked! Thank you so much.

 

[DaveTurton]

You're welcome