Нужно проверить объединены ли ячейки?

Set mb = Range("a15")
Set ma = Range("a1").MergeArea
If ma.Address = "$A$1" Then
    mb.Value = "months"
Else
    If ma.Address = "$A$1:$C$1" Then
    mb.Value = "quartals"
    Else
        If ma.Address = "$A$1:$L$1" Then
        mb.Value = "years"
        Else
        mb.Value = "months"
        End If
    End If
End If

Dim wcnt1 As Integer
Dim wcnt2 As Integer
wcnt1 = 1
wcnt2 = 5

Private Sub CommandButton1_Click()
    SetPeriods 1, 5, 1, 15, 3, 12, 1
End Sub
Private Sub SetPeriods(ByVal plngRowOne As Long, _
                       ByVal plngRowTwo As Long, _
                       ByVal pintColumnOne As Integer, _
                       ByVal pintColumnTwo As Integer, _
                       ByVal pintMergedCountOne As Integer, _
                       ByVal pintMergedCountTwo As Integer, _
                       ByVal pintWSheet As Integer)
    Dim objCellOne As Range
    Dim objCellTwo As Range
    Dim objMergedRange As Range
    Dim objWSheet As Worksheet
        
    Set objWSheet = ThisWorkbook.Worksheets(pintWSheet)
    Set objCellOne = objWSheet.Cells(plngRowOne, pintColumnOne)
    Set objCellTwo = objWSheet.Cells(plngRowTwo, pintColumnTwo)
    
    If Not objCellOne.MergeCells Then
        objCellTwo.Value = "Months"
    Else
        Set objMergedRange = objCellOne.MergeArea
        Select Case True
            Case objMergedRange.Column = pintColumnOne And objMergedRange.Row = plngRowOne And objMergedRange.Rows.Count = pintMergedCountOne
                objCellTwo.Value = "Quarters"
            Case objMergedRange.Column = pintColumnOne And objMergedRange.Row = plngRowOne And objMergedRange.Rows.Count = pintMergedCountTwo
                objCellTwo.Value = "Year"
        End Select
    End If
        
    Set objWSheet = Nothing
    Set objCellOne = Nothing
    Set objCellTwo = Nothing
    Set objMergedRange = Nothing
    
End Sub

Dim oMyCell As Range
Set oMyCell = Cells(5, 15)
Select Case Cells(1, 1).MergeArea.Count
    Case 1: oMyCell = "Month"
    Case 3: oMyCell = "Quarter"
    Case 12: oMyCell = "Year"
End Select