Find Method - Run Time Error

[kaplin]

Given the name of the column (ie. the text in the header row), I just want to return the column number.

Using this code I get an error when nothing is found.


dim mycol as variant

mycol = ActiveSheet.Range("1:1&quot.find(sort_name, SearchOrder:=xlByRows).Column


Is there a better way?

 

[Zathras]

I'm guessing that your variable sort_name is blank or has a value that is not in row 1.

Here is a routine that does what I think you are trying to do:

Option Explicit

Sub FindSortName()
Dim sort_name As String
Dim rFound As Range
Dim mycol As Integer
  sort_name = "STATE"
  Set rFound = ActiveSheet.Range("1:1").Find(sort_name)
  If rFound Is Nothing Then
    MsgBox sort_name & " not found"
  Else
    mycol = rFound.Column
    MsgBox sort_name & " is in column " & mycol
  End If
  Set rFound = Nothing
End Sub

Hope it helps.

 

[kaplin]

Thanks! Looking at your code - I realized, the only thing I was missing was the word "Set" in front of the find call.

It works now!

 

[Zathras]

Actually, you didn't need the word "Set" the way you were using the find call since you were simultaneously finding and retrieving the column number from the object. This code works just as well:

Option Explicit

Sub FindSortName()
Dim sort_name As String
Dim mycol As Integer
  sort_name = "STATE"
  mycol = -1
  On Error Resume Next
  mycol = ActiveSheet.Range("1:1").Find(sort_name).Column
  If mycol = -1 Then
    MsgBox sort_name & " not found"
  Else
    MsgBox sort_name & " is in column " & mycol
  End If
End Sub

It's really just a matter of taste.