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
strPrefix = "RAT"
strPrefix = "SEC"
strPrefix = "MES"
strPrefix = "WAL"
strPrefix = "EDB"
strPrefix = "CBK"
strDataRangeName = strPrefix & "Data"
With Workbooks(strWbkDestination) 'Current month's Green Package destination workbook
lngLastRow = .Range(strStartColumn & "65536").End(xlUp).Row
.Range(strStartColumn & "3:" & strEndColumn & lngLastRow).Name = strDataRangeName
intColCount = .Range(strDataRangeName).Columns.Count
'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
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
MsgBox " Error Number: " & Err.Number & vbCrLf & vbCrLf & "Description: " & Err.Description _
& vbCrLf & vbCrLf & " Procedure: CreateRangeNames", vbOKOnly