VBA Subscript out of range, name resolution problem?

Question

Trying to write a VBA function that will return the column number given the header cell string and the worksheet name but I get the Subscript out of range error. Here is the function:

Public Function namedColumnNo(heading As String, shtName As String) As Long
' Return the column number with named header text'
' on given worksheet.
Dim r As Range
Dim wks As Worksheet

Debug.Print shtName
'Exit Function

Set wks = Sheets(shtName)
wks.Range("1:1").Select
With wks
    r = .Range("1:1").Find(heading, LookIn:=xlValue)
    If r Is Nothing Then
        namedColumnNo = -1
    Else: namedColumnNo = r.Column
    End If

End With

End Function

I am using this test sub to call the funtion:

Public Sub getCol()

Debug.Print "Find MidTemp on " & DataSht.RawDataSht
Debug.Print "Col " & namedColumnNo("MidTemp", DataSht.RawDataSht)

End Sub

I have a user defined type DataSht where I have variables to name worksheets e.g.

Public Type dataShtNames
    HeaderSht As String
    RawDataSht As String
    ResultDataSht As String
End Type
Public DataSht As dataShtNames

With the Exit Function statement uncommented the variables resolve OK with the debug.print statements I get

Find MidTemp on RawData
RawData:MidTemp
Col 0

Leaving the function to run through the error occurs at Set wks = Sheets(shtName) If I replace the argument shtName with the actual sheet name as a string "RawData", then the error moves down to the line using the second argument heading. If I substitute a the parameter with a string here the error persists. Is there something I am missing here? Some help will be much appreciated.

When you call the function it assumes you are passing the variables for heading and shtname. Because you are not, the values are nothing and you will get the error because no such sheet or heading exists. — Darrell H
r = .Range("1:1").Find(heading, LookIn:=xlValue) is missing a Set keyword. I'm surprised that isn't throwing error 91. — Mathieu Guindon
I'm curious about whether these sheets exist at compile-time in ThisWorkbook, and whether the "headings" actually belong to ListObject (table) objects. If that is the case, there's a much, much simpler way to go about this. — Mathieu Guindon
@MathieuGuindon yes, the sheet does exist as I am using this function after creating new worksheets and applying names from the DataSht. variables. However, I will take the advice and learn more about the ListObject as I'm all for simpler solutions. Thanks — NigelH

Mike Mike · Accepted Answer · 2019-01-23T20:28:40

Sadly can't comment, but you're actually getting the out of range error because it should be LookIn:=xlValues where you have LookIn:=xlValue

As @Mathieu indicates, you'll need to fix add Set r = Find(heading, LookIn:=xlValues) to set the range to the value returned.

As a side note-you should drop the selection. Its not doing anything for you.

With wks.Range("1:1")
   Set r = .Find(heading, LookIn:=xlValues)
    If r Is Nothing Then
        namedColumnNo = -1
    Else: namedColumnNo = r.Column
    End If

End With

VBA Subscript out of range, name resolution problem?

1 Answers