Header function in VBA

Question

I have a function in Excel VBA that searches for headers and uses them to define certain ranges. It looks like it would work perfectly fine but when I call it, it breaks on this line: Set rngHeaders = Intersect(Worksheets(sheetName).UsedRange, Worksheets(sheetName).Rows(ROW_HEADERS)) and I am not sure why. It gives me a subscript out of range error. The sheetName variable is a string and the sheet Sheet8 (that I am passing into it) does exist. I have posted the entire function below in addition to the line where I call it in the Sub. Any help would be greatly appreciated.

Function FindHeader(HEADER_NAME As String, sheetName As String) As Range

    Dim rngHeaders As Range
    Dim rngHdrFound As Range

    Const ROW_HEADERS As Integer = 1

    Set rngHeaders = Intersect(Worksheets(sheetName).UsedRange, Worksheets(sheetName).Rows(ROW_HEADERS))
    Set rngHdrFound = rngHeaders.Find(HEADER_NAME)

    If rngHdrFound Is Nothing Then
       MsgBox ("ERROR: Cannot find appropriate header.")
        Exit Function
    End If

  Set FindHeader = Range(rngHdrFound.Offset(1), rngHdrFound.End(xlDown))

End Function

Line that calls it:

Sheet8.Activate
sheetName = "Sheet8"
Set rng1 = FindHeader("Client Exclusion List", sheetName)

It doesn't even get to that line...It breaks on the line before rngHdrFound is defined. — Philip
– Philip, Commented Feb 20, 2015 at 16:04
Can you try the following in the line that calls your function: Change the second [sheetName = "Sheet8"] line to sheetName = Sheet8.Name — basodre
– basodre, Commented Feb 20, 2015 at 16:07
@user3561813 that worked! must have been something with the name! thanks! — Philip
– Philip, Commented Feb 20, 2015 at 16:13

basodre · Accepted Answer · 2015-02-20 16:18:34Z

1

VBA distinguishes between a Worksheets .Name property and the .CodeName property. .CodeName refers to the name visible in the VBA Project Tree View (usually Sheet1, Sheet2, etc). The .Name is found in parenthesis right after the .CodeName. Additionally, the .Name is the value in the Worksheet Tab on the Excel front end.

If you pass the .CodeName to the Worksheets Collection, it will generate an error (unless the .CodeName and .Name are identical).

Hopefully this helps.

answered Feb 20, 2015 at 16:18

basodre

5,7901 gold badge18 silver badges25 bronze badges

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

Collectives™ on Stack Overflow

Header function in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related