VBA excel; Calculate the % utilisation

[Lewisbwalsh]

Hi i am stuck on this question for my application programming class in excel,

1. Calculate the % utilisation for the whole timetable. Utilisation is the percentage of empties.
2. You can use Find with blanks (nothing) but make sure to select the timetable range first so that you do not find blanks outside of that

I have been given a workbook.
Can someone please help or to even get it started.
Thank you

 

[Caleeco]

Hello,

I'm not sure of the structure of your workbook. So i mocked up the timetable below.

There are a number of ways to achieve the same result i'm sure. The rudimentary VBA code structure i would use would be this:

Sub Utilisation()


Dim oData As Range
Dim oCells As Range
Dim eCount As Integer 'Counts how many blanks in the range
Dim tCount As Integer 'Counts total cells in range


'Set the range of the timetable (excluding headers)
Set oData = ActiveSheet.Range("B2", "H8")
'Initialise counters
eCount = 0
tCount = 0


For Each oCells In oData
    If oCells.Value = "" Then
        eCount = eCount + 1
        tCount = tCount + 1
    Else
        tCount = tCount + 1
    End If
Next oCells


MsgBox "Number of Empty Cells : " & eCount
MsgBox "Total Number of Cells : " & tCount


'Insert your calculation for utilisation here, using eCount and tCount


End Sub

Hope that Helps
Caleeco