Loop through columns and named ranges

Hi,

I'm trying to find the column number of a named range (which has been defined previously in my code).

Function findReportNamedRange(reportColCount As Integer) As Integer                                    '6
    
    Dim reportSheet As Worksheet
    Dim reportCol As Integer
    Dim namedRange As Name

    Set reportSheet = Sheets("Report")
    
        For reportCol = reportColCount - 1 To 1 Step -1
            Debug.Print reportCol                                                                                                        '5
            For Each namedRange In ThisWorkbook.Names
                Debug.Print namedRange                                                                                               'prints _xlfn.IFERROR
                If Not Intersect(reportSheet.Cells(3, reportCol), namedRange.RefersToRange) Is Nothing Then
                    findReportNamedRange = reportCol
                    Debug.Print findReportNamedRange                                                                              'should be 1 but doesn't print
                    Exit For
                    Exit For
                End If
            Next
        Next reportCol

End Function

Am I missing something obvious here ?

Thanks,
amphi

Thanks TMS. Not sure what your suggestion should do/check ?
Depending with what I call the function, the column returned could be 1 (first time calling the function) then could be any number, 5,9,15 etc.

Thanks I'll try work through it using your tips !

One of the reasons this isn't working on my workbook is that I have an issue with _xlfn.iferror = #NAME as a hidden name stored in my workbook. The reason is that I've imported a 2007 sheet and added an IFERROR formula. I've tried deleting the name

    
For Each namedRange In ThisWorkbook.Names
        namedRange.Delete
Next

but, as it's a hidden name, the loop doesn't access it. However when I loop through the names and debug.print, it still appears.

Source of the issue : http://www.pcreview.co.uk/threads/_x...nager.3434850/

Appreciate the thoroughness ! Yeah I quickly realized that I wouldn't be able to delete the name because as you rightly mentioned, it was system generated. Luckily enough, I simply trapped the name with an IF statement and added the name as string which seemed to do the trick :

Function findReportNamedRange(reportColCount As Integer) As Integer
    
    Dim reportSheet As Worksheet
    Dim reportCol As Integer
    Dim namedRange As Name

    Set reportSheet = Sheets("Report")
    
        For reportCol = reportColCount - 1 To 0 Step -1 'in this case 5
            For Each namedRange In ThisWorkbook.Names
                If namedRange.Name <> "_xlfn.IFERROR" Then
                    If Not Intersect(reportSheet.Cells(3, reportCol), namedRange.RefersToRange) Is Nothing Then
                        findReportNamedRange = reportCol
                        Debug.Print findReportNamedRange    'outputs 1 - indeed Range("A3:A33") is a named range
                        Exit For
                        Exit For
                    End If
                End If
            Next
        Next reportCol
        
End Function

This is probably unrelated but when I call the function with this :

Dim configCol as integer

configCol = findReportNamedRange(reportColCount)
                Debug.Print configCol 'outputs 0

it seems that the function looses it's value.

Sure no worries. At least the _xlfn.IFERROR issue is solved

This is what I'm getting :

Call :

Debug.Print "Before calling function : " & reportColCount
configCol = findReportNamedRange(reportColCount)
Debug.Print "Number obtained by calling function : " & configCol

Function:

Function findReportNamedRange(reportColCount As Integer) As Integer
    
    Dim reportSheet As Worksheet
    Dim reportCol As Integer
    Dim namedRange As Name

    Set reportSheet = Sheets("Report")
    
        For reportCol = reportColCount - 1 To 0 Step -1
            Debug.Print "Looping through : " & reportCol
            For Each namedRange In ThisWorkbook.Names
                If namedRange.Name <> "_xlfn.IFERROR" Then
                    If Not Intersect(reportSheet.Cells(3, reportCol), namedRange.RefersToRange) Is Nothing Then
                        findReportNamedRange = reportCol
                        Debug.Print "The value obtained: " & findReportNamedRange
                        Exit For
                        Exit For
                    End If
                End If
            Next
        Next reportCol
        
End Function

Immediate window:

Before calling function : 6
Looping through : 5
Looping through : 4
Looping through : 3
Looping through : 2
Looping through : 1
The value obtained: 1
Looping through : 0
Number obtained by calling function : 0

Here you go.

All those debug.prints did show me the loop wasn't exiting but i never knew about Exit Function..
Yes I started getting an error in the sample work book I realized it was a mistake.
Thanks Norie !