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
– Darrell H, Commented Jan 23, 2019 at 20:11
r = .Range("1:1").Find(heading, LookIn:=xlValue) is missing a Set keyword. I'm surprised that isn't throwing error 91. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 23, 2019 at 20:21
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
– Mathieu Guindon, Commented Jan 23, 2019 at 20:37
@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
– NigelH, Commented Jan 24, 2019 at 15:43

Mike · Accepted Answer · 2019-01-23 20:28:40Z

3

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

answered Jan 23, 2019 at 20:28

Mike

6444 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mathieu Guindon Over a year ago

Wow, nice catch! Confirmed repro (throws error 91 once the typo is fixed, without the Set added). This would have been prevented with Option Explicit being specified at the top of the module.

Mathieu Guindon Over a year ago

I would also suspect a possible error #9 at Sheets(shtName), if that code isn't written in the ThisWorkbook module and the ActiveWorkbook (to which an unqualified Sheets call is then an implicit/indirect reference to) doesn't have a worksheet by that name. I would also advocate to use the Worksheets collection instead, to rule out the possibility of a type mismatch when the given name pulls, say, some Chart sheet - however unlikely that is.

Mathieu Guindon Over a year ago

Also, congrats! You now have enough rep to comment everywhere! =)

NigelH Over a year ago

The missing Set and xlValues has cured the problem, big tick and thanks. Also appreciate the refined code. I had actually put the .Select statement in as a visible 'debug' flag, so yes, redundant. @Mathieu your helpful advice duly noted, VBA objects and variables scope is driving me nuts!!

Collectives™ on Stack Overflow

VBA Subscript out of range, name resolution problem?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related