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
    .Activate
        With .Worksheets("DataSource")
            .Activate
                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
    Else
        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_ErrorHandler:
    Exit Sub
ErrorHandler:
        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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nutschCommented:
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

Thomas
0
nutschCommented:
Here is a screencast of that process.

Thomas


nutsch-440547.flv
0
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.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

nutschCommented:
Use that to name the columns, then use a macro to rename all the named ranges?
0
Rory ArchibaldCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.