Assign different named ranges based on variable number of entries

Hi All

I’m still fairly new to VB code, so I’ve been trying to practice by creating spreadsheets to make my life at work easier.

This spreadsheet will be used as a template each year. What I’d like to do is use VB to create named ranges based on each State grouping (i.e. Range(“$A16:$A21”)=“Arkansas”, Range(“$A22:$A24”)=”Connecticut”, etc.).

I’ve tried to code using VB to name each State’s range. I'm sure I've completely fubar'd this, so I need some expert help. The code below is where I appear to be running into problems….

Sub CalculateReimb()

Dim cell As Range, AgencyRng As Range, y As Integer

Set AgencyRng = Range("$A16:$A48")

For Each cell In AgencyRng

y = Application.WorksheetFunction.CountIf(AgencyRng, cell) - 1

ActiveWorkbook.Names.Add Name:=cell, refersto:=Worksheets("Calc to begin Rd 3"). _
    Range(cell & ":" & cell.Offset(, y))

Next cell

End Sub

Obviously, each time a user enters data for a new year, each State will be assigned different ranges than the year before. I have tried to create the VB sub, but I can’t seem to get the hang of it yet.

I’ve attached a sample spreadsheet for reference.
Any ideas?