loop through range and name each column a range of itself from column header

I have a process in VBA that creates a named ranges for all the column and rows that the data is located.  That is, say RATING is range A3:I486.  In row 3 are the column headers and I need to the, within the ranges, for each column, name that column a prefix plus the column header.  That is column A in this range would be RATClientCode, coumn B would be RATFund, etc.  I have the start column passed in as a parameter and I get the count of the number of colums in the range, but I need to get to being able to tell the VBA code to take the text in, say, Cell A3 and name the range A3:A486 RATClientcode, then loop to the next cell, B3 and do the same.  Eventually, I will have seven ranges that I need to do this in, but I can't get past this loop.  It names the overall range nicely, but stops when I try to name range the colums.  The Error I get is Error No 438, object doesn's support this property or method.
Public Sub CreateDatSourceRangeNames(strWbkDestination As String, strStartColumn As String, strEndColumn As String, strType As String)
'This process created the named ranges in the Client master workbooks that are used by the
'reporting worksheets to find data.  The names are a combination of the three-letter prefex for that
'type of data.  This is run after the data has been queried from the ActiveMasterTemplate, which holds the
'Green Package data and has been placed in the Client master workbook on the worksheet DataSource.
On Error GoTo ErrorHandler
Dim strPrefix   As String
Dim strLastCell As String
Dim lngLastRow  As Long
Dim strDataRangeName    As String
Dim strColName As String
Dim strColRangeName As String
Dim intColCount As Integer
Dim rngCell     As Range

Select Case strType
    Case "RATING"
        strPrefix = "RAT"
    Case "SECTOR"
        strPrefix = "SEC"
    Case "MES"
        strPrefix = "MES"
    Case "WAL"
        strPrefix = "WAL"
    Case "EDB"
        strPrefix = "EDB"
    Case "CBK"
        strPrefix = "CBK"
End Select

strDataRangeName = strPrefix & "Data"
With Workbooks(strWbkDestination) 'Current month's Green Package destination workbook
        With .Worksheets("DataSource")
                lngLastRow = .Range(strStartColumn & "65536").End(xlUp).Row
            .Range(strStartColumn & "3:" & strEndColumn & lngLastRow).Name = strDataRangeName
          intColCount = .Range(strDataRangeName).Columns.Count
        End With
'Need to name the each column from row 3 to lngLastRow in the range the name of the column
'Name the column range 
'Error hits about here
For Each rngCell In .Range(strStartColumn & "3:" & strEndColumn & "3")
''strColName = .Cells(strStartColumn, 3).Value
    If rngCell.Value = "" Then
        'Do nothing
        strColName = rngCell.Value
        strColName = Replace(strColName, "/", "")
        strColName = Replace(strColName, "%", "")
        strColName = Replace(strColName, "-", "")
        strColName = Replace(strColName, "_", "")
        strColRangeName = strPrefix & strColName
        .Range(Cells(3, i), Cells(lngLastRow, i)).Name = strColRangeName
    End If
Next rngCell

End With
    Exit Sub
        MsgBox " Error Number: " & Err.Number & vbCrLf & vbCrLf & "Description: " & Err.Description _
                & vbCrLf & vbCrLf & " Procedure: CreateRangeNames", vbOKOnly
        Resume Exit_ErrorHandler
End Sub

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
You basically need something like:
dim rngcol as range
For Each rngcol In .Range(strdatarangename).columns
Rngcol.name = strprefix & rngcol.cells(1).value
Next rngcol

Open in new window

You should probably add a function to replace any spaces in the name with underscores.
I'm not sure you need this. If you name both row headers and column headers, you can call the intersection by its name, e.g.
=Rat ClientCode
=Rat Fund

Here is a screencast of that process.


Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Sandra SmithRetiredAuthor Commented:
That was so cool!  Not exactly what I need though I think.  The Column headers when they come in from the recordset are ClientCode, Fund, NAV, Price, Units, Bench.  I need to call the column ranges RATClientcode, RATFund, RATNAV, etc.  I have another dataset that brings in columns with the same headers, but their range names woulcdbe SECClientCode, SECFund, SECNav, SECPrice, etc.  So, I canot simply use the column headers, I understand what you were showing me, but I don't this that would apply in this case.
Use that to name the columns, then use a macro to rename all the named ranges?
Sandra SmithRetiredAuthor Commented:
thank you, does exactly what I want.  Below is final version and I did add a replace function.

For Each rngCell In .Range(strDataRangeName).Columns
    rngCell.Name = strPrefix & fntGypsyRoseLee(rngCell.Cells(1).Value)
Next rngCell
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.