CODEWITHSUNDEEP
excelvba
PatK
PatK

Reputation: 11

Searching for Specific Column Headers in Excel File - Runtime Error 91

I am attempting to write some excel vba code that will process the content of certain columns of data. Given the worksheet has some level of dynamic change (columns added and removed from time to time), I want my code to "find" the specific columns by their header names, and ultimately return the column number. My File has roughly 50 columns.

The problem is this: My code works just fine to find many of the columns (headers) I am interested in returning the column index, but some of the columns "while clearly existing", will return Nothing and thus, throws the runtime 91 error.

I can say, without a doubt that when I execute the .find, that truly, the columns DO exist (like the Comments column). I can randomly change the failing hdr search column to a different header name, passing it to the function in the code and some columns are found just fine, and other, cause the runtime error. I have checked the "failing" headers for special characters, blanks, LF's etc. No luck. Even tried re-ordering the 4 rows using FindColHdrNum function. Again, no luck.

Was hoping fresh eyes may provide answer. Simplified code is below which is triggered by a button on main excel worksheet. I have not worked with functions much in VBA, and even where the function does not generate the Runtime Error, it is not returning the column value, but this is a secondary problem I can work on once I get the find code not blowing up (returning 0).

Sub Button119_Click()

Dim L4RankCol As Integer
Dim DecomDriverCol As Integer
Dim SupTermImpactYrCol As Integer
Dim Comments As Integer
Dim L3RankCol As Integer

    L4RankCol = FindColHdrNum("L4 Rank")   '<-- This works
    DecomDriverCol = FindColHdrNum("Decom Driver") '<-- This works
    SupTermImpactYrCol = FindColHdrNum("Support Termination Impact Yr")  '<-- This works
    Comments = FindColHdrNum("Comments")  '<-- This does not work

End Sub

Function FindColHdrNum(strHdr As String) As Integer

     Dim rngAddress As Range
     Set rngAddress = Range("Headers").Find(strHdr)
     FindColumnHdrNum = rngAddress.Column  '<--runtime error is caused by Nothing returned

End Function

Upvotes: 0

Views: 159

Answers (2)

PatK
PatK

Reputation: 11

Issue turns out to be a spurious line feed that was embedded in the header. It was strange as I kept re-typing it, but of course, I would always start at the "first letter" of the "comment" header, when in fact, the character preceded that. Thanks to all, for the help!

Upvotes: 1

B&#225;lint Kolosi
B&#225;lint Kolosi

Reputation: 83

The name of your function is FindColHdrNum but you wrote this into the function: 

FindColumnHdrNum = rngAddress.Column

Instead of:

FindColHdrNum = rngAddress.Column

Upvotes: 0

Related Questions