Find solicits Object Required

[bkelly]

I recorded a macro and used the generated code, changing it to save the results so I can discover the row where found. But the error message does not suggest what object is needed or where it is needed. What do I need to change?

Sub pull_titles_back_to_their_column()
  Dim col_num As Integer
  Dim test_col As Variant 
  Dim found_row As Long
  Dim found_cell As Variant  ' Tried Range, same result

  For col_num = COL_LAST_NAME To COL_SUFFIX

     test_col = Columns(COL_LAST_NAME)
     found_cell = test_col.Find( _ ‘ These 9 lines are highlighted
      What:="mr", _
      After:=ActiveCell, _
      LookIn:=xlFormulas, _
      LookAt:=xlWhole, _
      SearchOrder:=xlByColumns, _
      SearchDirection:=xlNext, _
      MatchCase:=False, _
      SearchFormat:=False).Activate

found_row = found_cell.Rows
Next col_num
End Sub

 

[Crystalyzer]

where do you define the variables COL_LAST_NAME and COL_SUFFIX?

For col_num = COL_LAST_NAME To COL_SUFFIX

 

[bkelly]

Sorry about that. They are constants declared at the very top. Here are a few of them.
Public Const COL_LAST_NAME As Integer = 6
Public Const COL_FIRST_NAME As Integer = 7
Public Const COL_MIDDLE_NAMES As Integer = 8
Public Const COL_SUFFIX As Integer = 9

 

[MARK858]

Try (Untested)...

Sub pull_titles_back_to_their_column()
    Dim col_num As Integer
    Dim test_col As Long
    Dim found_row As Long
    Dim found_cell As Range                      ' Tried Range, same result

    For col_num = COL_LAST_NAME To COL_SUFFIX

        test_col = Columns(COL_LAST_NAME)
        Set found_cell = test_col.Find( _
                         What:="mr", _
                         After:=test_col.Cells(1, 1), _
                         LookIn:=xlFormulas, _
                         LookAt:=xlWhole, _
                         SearchOrder:=xlByColumns, _
                         SearchDirection:=xlNext, _
                         MatchCase:=False, _
                         SearchFormat:=False)

        found_row = found_cell.Row
    Next col_num
End Sub

 

[bkelly]

That changed the problem to syntax error, a more basic error. Also tried this
After:=.cells( col_num, 1), _
with a no go.
Option Expllicit is set so its pretty sure not a syntax error or miss-named variable.