Microsoft Office Tutorials and References
In Depth Information
you have a predetermined size to use. You can also instruct Excel to calculate the proper size
by using the AutoFit method.
Joining Two Ranges Together
It might not always be desirable to create a range of non-contiguous cells when defining
ranges. You might need to keep the ranges separate for tracking purposes. In the
Y2001ByMonth.xls workbook, the values are stored by date, but some analysis might need to
be done based on the day of the week. Naturally, each individual weekday is separated from
its kin by the other days of the week. We can pull all the same weekday ranges together using
the Application object’s Union method.
For more information on the Application object, see Chapter 6, “The Application Object.”
The Union method takes two or more ranges and combines them into one range. The syntax is
expression .Union( Arg1 as Range, Arg2 as Range, … )
expression
is an optional Application object.
Arg1, Arg2, …
are range objects of which at least two must be specified.
The following procedure calculates the average sales for each day of the week by hour:
Sub CalcWeeklyAverages()
Dim intDayOfWeek As Integer, intWeeks As Integer, intHours As Integer
Dim rgeWeek As Range, rgeMonth As Range, rgeDay As Range
Dim sglTotal As Single
Dim strRow As String, intWeek As Integer
'Loop through each day of the week
For intDayOfWeek = 1 To 7
'Find first day of month
Set rgeDay = Range("D6", Range("D6").End(xlDown))
Set rgeMonth = Nothing
intWeeks = 0
'Loop through each week
For intWeek = intDayOfWeek To _
rgeDay.Cells(rgeDay.Cells.count, -1) Step 7
'Calculate row number
strRow = Format(intWeek + 5, "#0")
Set rgeWeek = Range("D" & strRow & ":O" & strRow)
If intWeek = intDayOfWeek Then 'Adjust for first week
Set rgeMonth = rgeWeek
Else
Set rgeMonth = Union(rgeMonth, rgeWeek)
End If
'Count number of times weekday occurs
intWeeks = intWeeks + 1
Next intWeek
Search JabSto ::

Custom Search