Option Explicit
Sub computeMeasures()
    Dim myData As Range
    
    'start at upper left corner of worksheet... begin with cell B2 in case you froze panes....
    Application.Goto (Cells(2, 2))
    Application.Goto (Cells(1, 1))
    Application.Goto (Cells(1, 2))
    
    'ask user to drag thru data range
    Set myData = Application.InputBox(prompt:="Please drag through demand series," & _
        vbCrLf & "including row 1 for column titles as variable names" & vbCrLf & vbCrLf & _
        "Time periood labels should be at left, not selected" & vbCrLf & _
        "All other values should be numeric" & vbCrLf & _
        "You need 7 blank rows below data", _
        Title:="Drag through demand series", Type:=8)
    
    'count the number of data columns
    Dim howManyColumns As Integer
    howManyColumns = myData.Columns.Count
    
    'count the number of data rows
    Dim howManyObs As Integer
    howManyObs = myData.Rows.Count - 1
        
    'where is data range located within the spreadsheet
    Dim dataStartRow As Integer
    dataStartRow = myData.Row
    Dim dataStartCol As Integer
    dataStartCol = myData.Column
    
    'specify output rows
    '   declare them
    Dim row_numObsWithDemand As Integer
    Dim row_numObsNonMissing As Integer
    Dim row_ADI As Integer
    Dim row_stdDev As Integer
    Dim row_mean As Integer
    Dim row_CVsq As Integer
    Dim row_frequency As Integer
    Dim row_variability As Integer
    Dim row_category As Integer
    '   compute them
    row_numObsWithDemand = myData.Row + howManyObs + 2
    row_numObsNonMissing = myData.Row + howManyObs + 3
    row_ADI = myData.Row + howManyObs + 4
    row_stdDev = myData.Row + howManyObs + 5
    row_mean = myData.Row + howManyObs + 6
    row_CVsq = myData.Row + howManyObs + 7
    row_frequency = myData.Row + howManyObs + 8
    row_variability = myData.Row + howManyObs + 9
    row_category = myData.Row + howManyObs + 10
    
    'ask user if it is OK to erase the output range
    Dim outputRange As Range
    Set outputRange = Range(Cells(row_numObsWithDemand, dataStartCol - 1), Cells(row_category, dataStartCol - 1 + howManyColumns))
    outputRange.Select
    'stop if user says cannot erase
    Dim mayIClean As VbMsgBoxResult
    mayIClean = VBA.Interaction.MsgBox(prompt:="OK to clear the selected range?", Buttons:=vbOKCancel, Title:="Clear this range?")
    If (mayIClean = vbCancel) Then
        'just reuse "mayIClean" so you can have another message box
        mayIClean = _
         VBA.Interaction.MsgBox(prompt:="Before restarting, please assure 10 blank rows beneath your data", Buttons:=vbOKOnly)
        Application.Goto (outputRange.Cells(1, 1))
        Exit Sub
    End If
    
    'to continue, assure output range is visible...
    Application.Goto (outputRange.Cells(outputRange.Rows.Count, 1))
    Application.Goto (outputRange.Cells(1, 1))
    'erase the cells within the output range
    outputRange.Cells.Clear
    
    'fill in output row labels
    Cells(row_numObsWithDemand, dataStartCol - 1).Value = "# with Demand"
    Cells(row_numObsNonMissing, dataStartCol - 1).Value = "# not Missing"
    Cells(row_ADI, dataStartCol - 1).Value = "ADI"
    Cells(row_stdDev, dataStartCol - 1).Value = "stdDev"
    Cells(row_mean, dataStartCol - 1).Value = "mean"
    Cells(row_CVsq, dataStartCol - 1).Value = "CV_sq"
    Cells(row_frequency, dataStartCol - 1).Value = "Frequency"
    Cells(row_variability, dataStartCol - 1).Value = "Variability"
    Cells(row_category, dataStartCol - 1).Value = "Category"
    'make sure you can read the row labels
    Columns(1).AutoFit

    'count the number of non-zero obs for each variable
    Dim rangeToCount As Range
    Dim j As Integer
    For j = 1 To howManyColumns
        Set rangeToCount = myData.Range(Cells(2, j), Cells(howManyObs + 1, j))
        
        'num with demand
        Cells(row_numObsWithDemand, dataStartCol - 1 + j).Formula = "=COUNTIF(" & rangeToCount.Address & ","">0"")"
        'numObs_withDemand(j) = Application.WorksheetFunction.CountIf(rangeToCount, ">0")
        
        'num non-missing
        Cells(row_numObsNonMissing, dataStartCol - 1 + j).Formula = "=COUNT(" & rangeToCount.Address & ")"
        'numObs_withMissing(j) = Application.WorksheetFunction.CountIf(rangeToCount, "=''")
    
        'ADI
        Cells(row_ADI, dataStartCol - 1 + j).Formula = "=" & Cells(row_numObsNonMissing, dataStartCol - 1 + j).Address & "/" & _
                                                             Cells(row_numObsWithDemand, dataStartCol - 1 + j).Address
        'stdDev
        Cells(row_stdDev, dataStartCol - 1 + j).Formula = "=stDev(" & rangeToCount.Address & ")"
    
        'mean
        Cells(row_mean, dataStartCol - 1 + j).Formula = "=AVERAGE(" & rangeToCount.Address & ")"
    
        'CVsq
        Cells(row_CVsq, dataStartCol - 1 + j).Formula = "=(" & Cells(row_stdDev, dataStartCol - 1 + j).Address & "/" & _
                                                             Cells(row_mean, dataStartCol - 1 + j).Address & _
                                                             ")^2"
        'apply rules
        'Demand Frequency
        Dim form As String
        form = "=IF(" & Cells(row_ADI, dataStartCol - 1 + j).Address & "<1.32,""Frequent"",""Infrequent"")"
        Debug.Print (form)
        Cells(row_frequency, dataStartCol - 1 + j).Formula = "=IF(" & Cells(row_ADI, dataStartCol - 1 + j).Address & "<1.32,""Frequent"",""Infrequent"")"
        
        'Variability
        Cells(row_variability, dataStartCol - 1 + j).Formula = "=IF(" & Cells(row_CVsq, dataStartCol - 1 + j).Address & "<0.49,""Low"",""High"")"
        
        'Category
        form = "=IF(" & Cells(row_frequency, dataStartCol - 1 + j).Address & "=""Frequent"",IF(" & Cells(row_variability, dataStartCol - 1 + j).Address & "=""Low"",""SMOOTH"",""ERRATIC""),IF(" & Cells(row_frequency, dataStartCol - 1 + j).Address & "=""Infrequent"",IF(" & Cells(row_variability, dataStartCol - 1 + j).Address & "=""Low"",""INTERMITTENT"", ""LUMPY"")))"
        Debug.Print (form)
        Cells(row_category, dataStartCol - 1 + j).Formula = _
             "=IF(" & Cells(row_frequency, dataStartCol - 1 + j).Address & "=""Frequent"",IF(" & Cells(row_variability, dataStartCol - 1 + j).Address & "=""Low"",""SMOOTH"",""ERRATIC""),IF(" & Cells(row_frequency, dataStartCol - 1 + j).Address & "=""Infrequent"",IF(" & Cells(row_variability, dataStartCol - 1 + j).Address & "=""Low"",""INTERMITTENT"", ""LUMPY"")))"
    Next j
     
    'Pretty up
    outputRange.Rows(3).Interior.Color = RGB(255, 255, 153)
    outputRange.Rows(6).Interior.Color = RGB(255, 255, 153)
    outputRange.Rows(9).Interior.Color = RGB(198, 239, 206)
    'replace all the $ so formulas can copy
    outputRange.Replace What:="$", Replacement:=""
   
End Sub