Using Arrays to Fill a Range
 

Here is a type of code that I am using now. myX, myY and myZ are
filled 'by force' down to rows and is extremely slow. How to make a
code to insert these values into an array and then transfer the
contents of the array to the range?

Using Arrays to Fill a Range
 

One way:

Public Sub Test()
Dim vArr As Variant
Dim i As Long
Dim j As Long
Dim myX As Long
Dim myY As Long
Dim nIndex As Long

i = 10
j = 10
ReDim vArr(1 To (2 * i + 1) * (2 * j + 1), 1 To 3)
For myX = -i To i
For myY = -j To j
nIndex = nIndex + 1
vArr(nIndex, 1) = myX
vArr(nIndex, 2) = myY
vArr(nIndex, 3) = myX + myY
Next myY
Next myX
ActiveCell.Resize(UBound(vArr, 1), 3).Value = vArr
End Sub



In article om,
"nenad" wrote:

Here is a type of code that I am using now. myX, myY and myZ are
filled 'by force' down to rows and is extremely slow. How to make a
code to insert these values into an array and then transfer the
contents of the array to the range?

Code:

---

Sub Test()

    i = 10
    j = 10
    For myX = -i To i
        For myY = -j To j
            myZ = i + j
            ActiveCell.Value = myX
            ActiveCell.Offset(0, 1).Value = myY
            ActiveCell.Offset(0, 2).Value = myZ
            Cells(ActiveCell.RoW + 1, 1).Select
        Next myY
    Next myX
End Sub


---

Using Arrays to Fill a Range
 

Sub TestALt()
Dim i, j, myX, myY, myZ
Dim idx As Long
Dim aryData(1 To 441, 1 To 3)
i = 10
j = 10
idx = 1
For myX = -i To i
For myY = -j To j
aryData(idx, 1) = myX
aryData(idx, 2) = myY
aryData(idx, 3) = i + j
idx = idx + 1
Next myY
Next myX
ActiveCell.Resize(441, 3) = aryData
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"nenad" wrote in message
ps.com...
Here is a type of code that I am using now. myX, myY and myZ are
filled 'by force' down to rows and is extremely slow. How to make a
code to insert these values into an array and then transfer the
contents of the array to the range?

Code:

---

Sub Test()

    i = 10
    j = 10
    For myX = -i To i
        For myY = -j To j
            myZ = i + j
            ActiveCell.Value = myX
            ActiveCell.Offset(0, 1).Value = myY
            ActiveCell.Offset(0, 2).Value = myZ
            Cells(ActiveCell.RoW + 1, 1).Select
        Next myY
    Next myX
End Sub


---

Using Arrays to Fill a Range
 

Actually you can do yours without the Selects

Sub Test()
Dim i As Long, j As Long, myX, myY, myZ
Dim idx As Long
i = 10
j = 10
With ActiveCell
For myX = -i To i
For myY = -j To j
.Offset(idx, 0).Value = myX
.Offset(idx, 1).Value = myY
.Offset(idx, 2).Value = i + j
idx = idx + 1
Next myY
Next myX
End With
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"nenad" wrote in message
ps.com...
Here is a type of code that I am using now. myX, myY and myZ are
filled 'by force' down to rows and is extremely slow. How to make a
code to insert these values into an array and then transfer the
contents of the array to the range?

Code:

---

Sub Test()

    i = 10
    j = 10
    For myX = -i To i
        For myY = -j To j
            myZ = i + j
            ActiveCell.Value = myX
            ActiveCell.Offset(0, 1).Value = myY
            ActiveCell.Offset(0, 2).Value = myZ
            Cells(ActiveCell.RoW + 1, 1).Select
        Next myY
    Next myX
End Sub


---

Using Arrays to Fill a Range
 

ANother way

Sub TestAlt2()
With ActiveCell
.Offset(0, 0).Resize(441).Formula = "=-10+(INT((ROW()-1)/21))"
.Offset(0, 1).Resize(441).Formula =
"=-11+MOD(ROW(),21)+(MOD(ROW(),21)=0)"
.Offset(0, 2).Resize(441).Value = 20
.Offset(0, 0).Resize(441, 2).Value = .Offset(0, 0).Resize(441,
2).Value
End With
End Sub






--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"nenad" wrote in message
ps.com...
Here is a type of code that I am using now. myX, myY and myZ are
filled 'by force' down to rows and is extremely slow. How to make a
code to insert these values into an array and then transfer the
contents of the array to the range?

Code:

---

Sub Test()

    i = 10
    j = 10
    For myX = -i To i
        For myY = -j To j
            myZ = i + j
            ActiveCell.Value = myX
            ActiveCell.Offset(0, 1).Value = myY
            ActiveCell.Offset(0, 2).Value = myZ
            Cells(ActiveCell.RoW + 1, 1).Select
        Next myY
    Next myX
End Sub


---

Using Arrays to Fill a Range
 

On Feb 24, 9:36 pm, "Bob Phillips" wrote:
ANother way

Sub TestAlt2()
With ActiveCell
.Offset(0, 0).Resize(441).Formula = "=-10+(INT((ROW()-1)/21))"
.Offset(0, 1).Resize(441).Formula =
"=-11+MOD(ROW(),21)+(MOD(ROW(),21)=0)"
.Offset(0, 2).Resize(441).Value = 20
.Offset(0, 0).Resize(441, 2).Value = .Offset(0, 0).Resize(441,
2).Value
End With
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"nenad" wrote in message

ps.com...



Here is a type of code that I am using now. myX, myY and myZ are
filled 'by force' down to rows and is extremely slow. How to make a
code to insert these values into an array and then transfer the
contents of the array to the range?

Code:

---

Sub Test()

---

Code:

---


    i = 10
    j = 10
    For myX = -i To i
        For myY = -j To j
            myZ = i + j
            ActiveCell.Value = myX
            ActiveCell.Offset(0, 1).Value = myY
            ActiveCell.Offset(0, 2).Value = myZ
            Cells(ActiveCell.RoW + 1, 1).Select
        Next myY
    Next myX
End Sub


---

- Hide quoted text -

- Show quoted text -

Hey Bob,

Thanks a lot man. I can't explain how much I appreciate this. This
first option worked perfect.

Using Arrays to Fill a Range
 

On Feb 24, 8:59 pm, JE McGimpsey wrote:
One way:

Public Sub Test()
Dim vArr As Variant
Dim i As Long
Dim j As Long
Dim myX As Long
Dim myY As Long
Dim nIndex As Long

i = 10
j = 10
ReDim vArr(1 To (2 * i + 1) * (2 * j + 1), 1 To 3)
For myX = -i To i
For myY = -j To j
nIndex = nIndex + 1
vArr(nIndex, 1) = myX
vArr(nIndex, 2) = myY
vArr(nIndex, 3) = myX + myY
Next myY
Next myX
ActiveCell.Resize(UBound(vArr, 1), 3).Value = vArr
End Sub

In article om,



"nenad" wrote:
Here is a type of code that I am using now. myX, myY and myZ are
filled 'by force' down to rows and is extremely slow. How to make a
code to insert these values into an array and then transfer the
contents of the array to the range?

Code:

---

Sub Test()

---

Code:

---


    i = 10
    j = 10
    For myX = -i To i
        For myY = -j To j
            myZ = i + j
            ActiveCell.Value = myX
            ActiveCell.Offset(0, 1).Value = myY
            ActiveCell.Offset(0, 2).Value = myZ
            Cells(ActiveCell.RoW + 1, 1).Select
        Next myY
    Next myX
End Sub


---

- Hide quoted text -

- Show quoted text -

Sorry JE I have to thank you as well. It works perfectly as well.
Thanks guys.