Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using Arrays to Fill a Range
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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?

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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,624
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default 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



Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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.

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default 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.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using arrays or range in IF function - HELP PLS MickJJ Excel Worksheet Functions 1 February 10th 06 05:43 PM
Dynamic Range and arrays PR Excel Discussion (Misc queries) 2 November 14th 05 03:51 AM
Dynamic Range and arrays PR Excel Worksheet Functions 2 November 14th 05 03:51 AM
How To Fill 2D Arrays Easily [email protected] Excel Programming 13 May 21st 05 11:06 AM
Arrays: Subscript out of range nortor Excel Programming 4 February 17th 05 09:13 AM


All times are GMT +1. The time now is 08:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement