Run-time error: '1004': The syntax of this name isn't correct

[Derick63]

Hello all.

I've been banging my head against the wall on this one and I finally threw my hands into the air. I definitely need more brain power than I obviously have going on now.

My VBA code Creates a new worksheet, names it via an Inputbox function and "supposed" to create three new named ranges. Everything goes well until naming the ranges. I have stepped through the macro (F8) and that's where I get the 1004 error code. I think the error is happening at the "RefersTo:=" section.

I know this is well above my level of VBA.

Any help is greatly appreciated.

I removed non-pertinent sheet formatting code for clarity.

Code:

Sub Macro9()


'Get effective date of new contribution rates
    Dim Message, Title, Default, RateDate, NewWsName As Variant

    Message = "Enter Effective Date of Contribution Rate Chart."
    Title = "Contribution Rates Chart Effective Date"
    Default = "YYYY-MM-DD"
    RateDate = InputBox(Message, Title, Default)
    
'Add new sheet and rename it to NIS-[RateDate]
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) 'Last tab
    ActiveSheet.Name = "NIS-" & RateDate
    
    NewWsName = ActiveSheet.Name 'used below to create named ranges
    
'New Named Ranges for NIS formula to be replaced
    Dim NISLow, NISHigh, NisContr As Range
    Dim RangeNameLow, RangeNameHigh, RangeNameContr As String
    
    Set NISLow = Sheets(NewWsName).Range("A4:A19")
    Set NISHigh = Sheets(NewWsName).Range("B4:B19")
    Set NisContr = Sheets(NewWsName).Range("C4:C19")
    
    RangeNameLow = "NISLow" & RateDate
    RangeNameHigh = "NISHigh" & RateDate
    RangeNameContr = "NISContr" & RateDate
    
    ThisWorkbook.Names.Add Name:=RangeNameLow, RefersTo:=NISLow
    ThisWorkbook.Names.Add Name:=RangeNameHigh, RefersTo:=NISHigh
    ThisWorkbook.Names.Add Name:=RangeNameContr, RefersTo:=NisContr
    
    Range("A3").Select

End Sub

 

[steve the fish]

Its simply that you cant use the character - within the name of a named range

 

[Michael M]

I think RateDate is your problem...
The name box won't allow dashes / slashes

remove ratedate from the "RangeNameLow = "NISLow" & RateDate" lines and it will work

 

[mrshl9898]

You might need to format ratedate, if it contains any slashes.

Sub Macro9()




'Get effective date of new contribution rates
    Dim Message, Title, Default, RateDate, NewWsName As Variant
    Dim ratedate2 As String


    Message = "Enter Effective Date of Contribution Rate Chart."
    Title = "Contribution Rates Chart Effective Date"
    Default = "YYYY-MM-DD"
    RateDate = InputBox(Message, Title, Default)
    ratedate2 = Format(RateDate, "ddmmyyyy")
    
'Add new sheet and rename it to NIS-[RateDate]
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count) 'Last tab
    ActiveSheet.Name = "NIS-" & RateDate
    
    NewWsName = ActiveSheet.Name 'used below to create named ranges
    
'New Named Ranges for NIS formula to be replaced
    Dim NISLow, NISHigh, NisContr As Range
    Dim RangeNameLow, RangeNameHigh, RangeNameContr As String
    
    Set NISLow = Sheets(NewWsName).Range("A4:A19")
    Set NISHigh = Sheets(NewWsName).Range("B4:B19")
    Set NisContr = Sheets(NewWsName).Range("C4:C19")
    
    RangeNameLow = "NISLow" & RateDate
    RangeNameHigh = "NISHigh" & RateDate
    RangeNameContr = "NISContr" & RateDate
    
    ThisWorkbook.Names.Add Name:=RangeNameLow, RefersTo:=NISLow
    ThisWorkbook.Names.Add Name:=RangeNameHigh, RefersTo:=NISHigh
    ThisWorkbook.Names.Add Name:=RangeNameContr, RefersTo:=NisContr
    
    Range("A3").Select


End Sub

 

[Derick63]

Steve, Thanks man. I can't believe I was racking my head for something so simple. I removed all dashes from everywhere and it worked like a charm.

Its simply that you cant use the character - within the name of a named range

 

[Derick63]

Michael, Thanks for the reply. It was a simple fix. I just removed all instances of a "-" (dash/hyphen) and it flew through the macro. Can't believe it was that simple..... Duhhh on me...

 

[Derick63]

mrshl9898, Thanks so much for the reply. I like what you did there. I just removed all instances of a "-" (dash/hyphen) and it flew through the macro.