Thread: check a row of data for a value return a boolean result

I need help to determine if a value exists in a column header and then based on that determination give it a boolean value (TRUE = it exists, FALSE = it doesn't exist) and then, if it does exist, store that column location into an integer variable.

My problems occur when the column name doesn't exist. Since there is no value, it can't determine if the value is > 0 so it hangs.

Any help would be appreciated.

-js999

'Variable to store column number if column exists
Dim JoinMktClassCol_Number as Integer        

'Variable to recognize whether column named 'JoinMktClass' exists
Dim JoinMktClassCol_Exists as Boolean


ActiveWorkbook.Sheets("Proposed").Activate
    JoinMktClassCol_Number = Cells.Find(What:="JoinMktClass", After:=[A1], _
        Searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    MsgBox "JoinMktClass is in column: " & JoinMktClassCol_Number
    
If JoinMktClassCol_Number > 0 Then
        JoinMktClassCol_Exists = True
Else: JoinMktClassCol_Exists = False
End If

Hello Js999,

The Find method returns a Range. If the Find is unsuccessful then it returns a special value called Nothing.

' Variable to store column number if column exists
  Dim JoinMktClassCol_Number As Range        

' Variable to recognize whether column named 'JoinMktClass' exists
  Dim JoinMktClassCol_Exists as Boolean


ActiveWorkbook.Sheets("Proposed").Activate
    Set JoinMktClassCol_Number = Cells.Find(What:="JoinMktClass", After:=[A1], _
        Searchorder:=xlByColumns, searchdirection:=xlPrevious)

  If Not JoinMktClassCol_Number Is Nothing Then
     JoinMktClassCol_Exists = True
     MsgBox "JoinMktClass is in column: " & JoinMktClassCol_Number.Column
  Else 
     JoinMktClassCol_Exists = False
  End If

Sincerely,
Leith Ross

Leith,

Thank you! It works perfect. Hopefully, someday I will get to the level where I can return the favor. Emphasis on 'someday'.

-js999