Some help w/ simple loop, please?
 

[Excel 2002]
I'm a raw beginner w/ VBA, as evidenced by the simple problem I'm
stumped by...
All I'm trying to do is, starting w/ a clean worksheet, write a sub to
apply the value "55" to ranges beginning w/ "A1:D3", then skipping rows
4 thru 6, then entering value in range "A7:D9", etc., and setting a
stop point around row 36.
So basically, I'm trying to write a simple loop to apply formatting to
alternating bands/ranges, and am using the value "55" as a placeholder.
Here's what I'm trying to make work:

Sub PracticeBanding()
Dim MyRange As Object
Set MyRange = Range("A1:D3").Select
Do Until ActiveCell.Row 36 = True
MyRange.Value = 55
MyRange.Offset(6, 0).Select
Loop
End Sub

So what happens is, As I step through the procedure, it selects the
first range, applies the correct value, offsets down to the next range
just fine...and then fails to apply the values, and just loops back up
to original range and back down to empty range over and over....it
doesn't apply the values after the first range....
I know this is basic as gravity, but I'd sure appreciate someone
helping me with it! Thanks much for your time [:-)

terry b.

Some help w/ simple loop, please?
 

Maybe...

Option Explicit
Sub PracticeBanding()
Dim MyRange As Range
Set MyRange = Range("A1:D3")
Do Until MyRange.Row 36
MyRange.Value = 55
Set MyRange = MyRange.Offset(6, 0)
Loop
End Sub

You could also loop through the row numbers.

Sub PracticeBanding2()
Dim iRow As Long
For iRow = 1 To 36 Step 6
Cells(iRow, "A").Resize(3, 3).Value = 66
Next iRow
End Sub



terry b wrote:

[Excel 2002]
I'm a raw beginner w/ VBA, as evidenced by the simple problem I'm
stumped by...
All I'm trying to do is, starting w/ a clean worksheet, write a sub to
apply the value "55" to ranges beginning w/ "A1:D3", then skipping rows
4 thru 6, then entering value in range "A7:D9", etc., and setting a
stop point around row 36.
So basically, I'm trying to write a simple loop to apply formatting to
alternating bands/ranges, and am using the value "55" as a placeholder.
Here's what I'm trying to make work:

Sub PracticeBanding()
Dim MyRange As Object
Set MyRange = Range("A1:D3").Select
Do Until ActiveCell.Row 36 = True
MyRange.Value = 55
MyRange.Offset(6, 0).Select
Loop
End Sub

So what happens is, As I step through the procedure, it selects the
first range, applies the correct value, offsets down to the next range
just fine...and then fails to apply the values, and just loops back up
to original range and back down to empty range over and over....it
doesn't apply the values after the first range....
I know this is basic as gravity, but I'd sure appreciate someone
helping me with it! Thanks much for your time [:-)

terry b.

--

Dave Peterson

Some help w/ simple loop, please?
 

For Dave P.
Thanks very much for your time! Your code (both) works w/out a flaw.

Now all I need to do is study what you've done and see why it
works....
Could you possibly check that thread tomorrow, & see if I've added
to it?
Thanks again [:-)

terrry b.

Some help w/ simple loop, please?
 

One of the nice things about the newsgroups is that anyone who reads your posts
will be able to jump in and help.

It's always nice to get a few opinions/methods of doing things.

terry b wrote:

For Dave P.
Thanks very much for your time! Your code (both) works w/out a flaw.

Now all I need to do is study what you've done and see why it
works....
Could you possibly check that thread tomorrow, & see if I've added
to it?
Thanks again [:-)

terrry b.

--

Dave Peterson

Some help w/ simple loop, please?
 

Sub Macro1()
Dim PlaceValue As Boolean, icount As Integer
PlaceValue = True
icount = 0
For Each c In Range("A1:D36")
If PlaceValue Then c.Value = "55"
icount = icount + 1
If icount = 12 Then
If PlaceValue Then
PlaceValue = False
Else
PlaceValue = True
End If
icount = 0
End If
Next c

End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Some help w/ simple loop, please?
 

Dave was resetting the range to the offset before the loop starts again.
Otherwise the loop was using the same range over and over.

Mike F
"terry b" wrote in message
oups.com...
For Dave P.
Thanks very much for your time! Your code (both) works w/out a flaw.

Now all I need to do is study what you've done and see why it
works....
Could you possibly check that thread tomorrow, & see if I've added
to it?
Thanks again [:-)

terrry b.

Some help w/ simple loop, please?
 

Thanks to All for replies,
I stared at the three subs suggested until the logic was apparent.
And I've already 're-worked' the basic code into other formatting
modules. And they work great!
Thanks again for your time, people [:-)

terry b.