Help with "double" loop!

[erikhs]

Hi,

I would like to make a loop that fills a cell with a formula vertically(column index), and then when it reaches the max boundary for i(Last specified cell in i) it should 'jump' down a row and begin at the same column as before. This should be repeated until j(or row index) is max.
I was thinking something like:

For j = 1 to x
..........
For i = 1 to y
....................
Next i
...........
Next j

However i can't get this to work. Any help will be really appreciated.

[Graham Whitehead]

Cant really see anything wrong with your logic here. Try this example:

Sub loops()

Dim x As Integer
Dim y As Integer

For x = 1 To 10
For y = 1 To 10
Cells(x, y).Value = x & ", " & y
Next y
Next x

End Sub

"erikhs" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> I would like to make a loop that fills a cell with a formula
> vertically(column index), and then when it reaches the max boundary for
> i(Last specified cell in i) it should 'jump' down a row and begin at the
> same column as before. This should be repeated until j(or row index) is
> max.
> I was thinking something like:
>
> For j = 1 to x
> .........
> For i = 1 to y
> ...................
> Next i
> ..........
> Next j
>
> However i can't get this to work. Any help will be really appreciated.
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile:
> http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=565095
>

[ChasAA]

Hello,

This should do it. (I always prefer to name variables with meaningful names
rather than single letters, otherwise I would I have used i,j etc)

Sub addBlocksofData()

Dim blocks As Integer
Dim off As Integer
Dim blockCounter As Integer
Dim formulaCounter As Integer
Dim nbrFormula As Integer

blocks = 5
nbrFormula = 6
Range("A1").Select
For blockCounter = 1 To blocks
For formulaCounter = 1 To nbrFormula
Selection.Offset(off, 0).Value = "Formula " & Str(formulaCounter)
off = off + 1
Next formulaCounter
Selection.Offset(off, 0) = "" ' Blank
off = off + 1
Next blockCounter

End Sub

ChasAA

"erikhs" wrote:

>
> Hi,
>
> I would like to make a loop that fills a cell with a formula
> vertically(column index), and then when it reaches the max boundary for
> i(Last specified cell in i) it should 'jump' down a row and begin at the
> same column as before. This should be repeated until j(or row index) is
> max.
> I was thinking something like:
>
> For j = 1 to x
> ..........
> For i = 1 to y
> ....................
> Next i
> ...........
> Next j
>
> However i can't get this to work. Any help will be really appreciated.
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=565095
>
>

[Hemant_india]

try this
dim rng as range
set rng=activesheet.usedrange
for n= 1 to rng.rows.count
for j= 1 to rng.columns.count
cells(n,j)="=formula"'<-------change
next j
next n

--
hemu


"ChasAA" wrote:

> Hello,
>
> This should do it. (I always prefer to name variables with meaningful names
> rather than single letters, otherwise I would I have used i,j etc)
>
> Sub addBlocksofData()
>
> Dim blocks As Integer
> Dim off As Integer
> Dim blockCounter As Integer
> Dim formulaCounter As Integer
> Dim nbrFormula As Integer
>
> blocks = 5
> nbrFormula = 6
> Range("A1").Select
> For blockCounter = 1 To blocks
> For formulaCounter = 1 To nbrFormula
> Selection.Offset(off, 0).Value = "Formula " & Str(formulaCounter)
> off = off + 1
> Next formulaCounter
> Selection.Offset(off, 0) = "" ' Blank
> off = off + 1
> Next blockCounter
>
> End Sub
>
> ChasAA
>
> "erikhs" wrote:
>
> >
> > Hi,
> >
> > I would like to make a loop that fills a cell with a formula
> > vertically(column index), and then when it reaches the max boundary for
> > i(Last specified cell in i) it should 'jump' down a row and begin at the
> > same column as before. This should be repeated until j(or row index) is
> > max.
> > I was thinking something like:
> >
> > For j = 1 to x
> > ..........
> > For i = 1 to y
> > ....................
> > Next i
> > ...........
> > Next j
> >
> > However i can't get this to work. Any help will be really appreciated.
> >
> >
> > --
> > erikhs
> > ------------------------------------------------------------------------
> > erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
> > View this thread: http://www.excelforum.com/showthread...hreadid=565095
> >
> >

[ChasAA]

Erikhs,
I think I misunderstood what you were trying to do but you did say you would
like to fill vertically not horizontally

ChasAA

"erikhs" wrote:

>
> Hi,
>
> I would like to make a loop that fills a cell with a formula
> vertically(column index), and then when it reaches the max boundary for
> i(Last specified cell in i) it should 'jump' down a row and begin at the
> same column as before. This should be repeated until j(or row index) is
> max.
> I was thinking something like:
>
> For j = 1 to x
> ..........
> For i = 1 to y
> ....................
> Next i
> ...........
> Next j
>
> However i can't get this to work. Any help will be really appreciated.
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=565095
>
>

[ChasAA]

Erikhs,
I think I misunderstood what you were trying to do but you did say you would
like to fill vertically not horizontally

ChasAA

"erikhs" wrote:

>
> Hi,
>
> I would like to make a loop that fills a cell with a formula
> vertically(column index), and then when it reaches the max boundary for
> i(Last specified cell in i) it should 'jump' down a row and begin at the
> same column as before. This should be repeated until j(or row index) is
> max.
> I was thinking something like:
>
> For j = 1 to x
> ..........
> For i = 1 to y
> ....................
> Next i
> ...........
> Next j
>
> However i can't get this to work. Any help will be really appreciated.
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=565095
>
>

[ChasAA]

Erikhs,
I think I misunderstood what you were trying to do but you did say you would
like to fill vertically not horizontally

ChasAA

"erikhs" wrote:

>
> Hi,
>
> I would like to make a loop that fills a cell with a formula
> vertically(column index), and then when it reaches the max boundary for
> i(Last specified cell in i) it should 'jump' down a row and begin at the
> same column as before. This should be repeated until j(or row index) is
> max.
> I was thinking something like:
>
> For j = 1 to x
> ..........
> For i = 1 to y
> ....................
> Next i
> ...........
> Next j
>
> However i can't get this to work. Any help will be really appreciated.
>
>
> --
> erikhs
> ------------------------------------------------------------------------
> erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788
> View this thread: http://www.excelforum.com/showthread...hreadid=565095
>
>