VBA Ranges - Setting and Referencing

[marteb]

Hello,

I am trying to adapt a macro with hard coded ranges to use flexible ranges based on user input.
The macro creates a monthly calendar that I would like to use for my daily task pages. There are
14 different areas on the document where the calendar resides.

My problem is referencing the ranges once they are set. This is a portion of the code.

' sCell = Starting Cell - Input 1A, 1B, etc (1A first page, first calendar)
    Dim sCell As String
    
' Define Ranges
    Dim mTitle As Range
    Dim mLabel As Range
    Dim dTitle As Range
    Dim dlabel As Range
    
    sCell = InputBox("Enter Starting Cell")

' Beginning Case Statement - Set Ranges
Select Case sCell
    Case Is = "1A"
        Set mTitle = Range("a1")
        Set mLabel = Range("a1:g1")
        Set dTitle = Range("a2:g2")
        Set dlabel = Range("a2")
End Select

[B]Range("mTitle").NumberFormat = "mmmm"[/B]

With Range("mLabel")
           .HorizontalAlignment = xlCenterAcrossSelection
           .VerticalAlignment = xlCenter
           .Font.Size = 11
           .Font.Bold = True
           .RowHeight = 14.25
       End With

The code hits the error trap (not shown) at Range("mTitle"). Am I referencing the ranges incorrectly
or is the setup wrong? The code works correctly with the hard coded ranges.

Thanks, Marteb

 

[Joe4]

Since you have already defined those ranges with names, you can refer to them like this (which should get rid if your error):

mTitle.NumberFormat = "mmmm"
 
With mLabel
    .HorizontalAlignment = xlCenterAcrossSelection
    .VerticalAlignment = xlCenter
    .Font.Size = 11
    .Font.Bold = True
    .RowHeight = 14.25
End With

 

[marteb]

Joe,

Thanks for your help and have a great day!

Marteb