jolivanes
05-30-2008, 11:15 PM
I am trying to loop through 31 columns (days of the month).
For the first day of the month the macro goes down Column F with
the macro below
Sub TestMacro1()
Application.ScreenUpdating = False
Dim Rng As Range, MyCell As Range
Range("F61:F65").ClearContents
Set Rng = Range("F3:F53")
For Each MyCell In Rng
With MyCell
Select Case True
Case .Value = "H" And .Offset(0, -1).Text = "Small" And .Offset(0, -2).Text = "Medium" And .Offset(0, -3).Text = "Big"
Range("F61").Value = Range("F61").Value + 2
Range("F62").Value = Range("F62").Value + 2
Range("F63").Value = Range("F63").Value + 2
Range("F64").Value = Range("F64").Value + 3
Range("F65").Value = Range("F65").Value + 3
Case .Value = "H" And .Offset(0, -1).Text = "Small" And .Offset(0, -2).Text = "Medium" And .Offset(0, -3).Text = ""
More code
more code
End Select
End With
Next MyCell
'It goes to cell F53
Application.ScreenUpdating = True
End Sub
I could have 31 times the above by changing the column designation letters from F to G and next to H etc and
changing the Offsets by 1 in each subsequent macro. The macro for one day is 65 lines long so
that would be a very long macro and, I am sure a bit over the top. The last column is Column AJ.
How can I loop through the same macro by increasing the column letter by one, and the
offset by 1 so the next loop would be
Range("G61:G65").ClearContents
Set Rng = Range("G3:G53")
For Each MyCell In Rng
With MyCell
Select Case True
Case .Value = "H" And .Offset(0, -2).Text = "Small" And .Offset(0, -3).Text = "Medium" And .Offset(0, -4).Text = "Big"
Range("G61").Value = Range("G61").Value + 2
Range("G62").Value = Range("G62").Value + 2
Range("G63").Value = Range("G63").Value + 2
Range("G64").Value = Range("G64").Value + 3
Range("G65").Value = Range("G65").Value + 3
Case .Value = "H" And .Offset(0, -2).Text = "Small" And .Offset(0, -3).Text = "Medium" And .Offset(0, -4).Text = ""
BTW this a different approach I am trying from the help I received from gwkenney and matthewspatrick at
http://www.vbaexpress.com/forum/showthread.php?t=19804
Thanks and Regards
John
For the first day of the month the macro goes down Column F with
the macro below
Sub TestMacro1()
Application.ScreenUpdating = False
Dim Rng As Range, MyCell As Range
Range("F61:F65").ClearContents
Set Rng = Range("F3:F53")
For Each MyCell In Rng
With MyCell
Select Case True
Case .Value = "H" And .Offset(0, -1).Text = "Small" And .Offset(0, -2).Text = "Medium" And .Offset(0, -3).Text = "Big"
Range("F61").Value = Range("F61").Value + 2
Range("F62").Value = Range("F62").Value + 2
Range("F63").Value = Range("F63").Value + 2
Range("F64").Value = Range("F64").Value + 3
Range("F65").Value = Range("F65").Value + 3
Case .Value = "H" And .Offset(0, -1).Text = "Small" And .Offset(0, -2).Text = "Medium" And .Offset(0, -3).Text = ""
More code
more code
End Select
End With
Next MyCell
'It goes to cell F53
Application.ScreenUpdating = True
End Sub
I could have 31 times the above by changing the column designation letters from F to G and next to H etc and
changing the Offsets by 1 in each subsequent macro. The macro for one day is 65 lines long so
that would be a very long macro and, I am sure a bit over the top. The last column is Column AJ.
How can I loop through the same macro by increasing the column letter by one, and the
offset by 1 so the next loop would be
Range("G61:G65").ClearContents
Set Rng = Range("G3:G53")
For Each MyCell In Rng
With MyCell
Select Case True
Case .Value = "H" And .Offset(0, -2).Text = "Small" And .Offset(0, -3).Text = "Medium" And .Offset(0, -4).Text = "Big"
Range("G61").Value = Range("G61").Value + 2
Range("G62").Value = Range("G62").Value + 2
Range("G63").Value = Range("G63").Value + 2
Range("G64").Value = Range("G64").Value + 3
Range("G65").Value = Range("G65").Value + 3
Case .Value = "H" And .Offset(0, -2).Text = "Small" And .Offset(0, -3).Text = "Medium" And .Offset(0, -4).Text = ""
BTW this a different approach I am trying from the help I received from gwkenney and matthewspatrick at
http://www.vbaexpress.com/forum/showthread.php?t=19804
Thanks and Regards
John