Some help w/ simple loop, please?

  • Thread starter Thread starter terry b
  • Start date Start date
T

terry b

[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.
 
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 said:
[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.
Click to expand...
 
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.
 
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 said:
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.
Click to expand...
 
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
 
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
 
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.
 
Back
Top