Finding a cell based on the header of a section of data, then selecting the last row of that section

Question

I am attempting to find the text of a header row based on the value of a cell relative to the cell that is clicked in. The way I have attempted to do this is follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim var1 As Variant
  Dim var2 As Variant
  Dim var3 As Variant
  Dim FormName As String
  FormName = "New Form"
  Static NewFormCell As Range
  Application.ScreenUpdating = False

  If Not Intersect(Target, Range("G16:X80")) Is Nothing Then
    If Target.Cells.Count = 1 Then
      var1 = Cells(Target.Row, 2).Value
      var2 = Cells(15, Target.Column).Value     
      If Not (IsEmpty(var1)) And Not (IsEmpty(var2)) And var2 <> "+" And Target.Interior.ColorIndex <> 2 And Target.Borders(xlEdgeLeft).LineStyle <> xlNone Then
        If IsEmpty(Target) Then
          Target.Value = "X"
          Target.HorizontalAlignment = xlCenter
          Target.VerticalAlignment = xlCenter
          Target.Font.Bold = True
          Dim Header As Range
          Set Header = Range("A54:E160").Find(var2, LookIn:=xlValues)
            Header.Offset(1, 1).End(xlDown).EntireRow.Select
          Dim CopyCell As Range

          'Header.End(xlDown).EntireRow.Insert
          'Set CopyCell = Header.End(xlDown). [offset?]
          'CopyCell.Value = var1
        Else
          Target.ClearContents
        End If
      Else
        Exit Sub
      End If
    End If
  End If
  Application.ScreenUpdating = True

End Sub

The issue is VBA is throwing Run-Time Error 91 ("Object variable or With block variable not set"). It then highlights the last row in that section of code. Since I set that variable in the previous line, I'm not sure why I'm receiving this error or if I'm even going about this the right way.

Any input would be greatly appreciated!

EDIT: I cleared the above issue by searching over a wider range. The cell I wanted to select was merged, but I still assumed the value was stored within column A. But this code still isn't quite doing what I'd like it to:

I want to select the last row in the section (not the last row of data in the sheet, but the last contiguous data in column B), but right now my code is jumping me all the way to the bottom of the sheet.

Is that all of the code? Can you please post the rest of the relevant code (i.e. any declarations, etc). Is this a worksheet_change event? Why are you trying to `.Select` a cell? (It's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/)) Perhaps there's no `var2` in your range? — BruceWayne, May 23 '17 at 16:50
There are a number of other If statements in the sub, but I've updated the above code block with the whole sub minus the If statements that aren't relevant to this issue. var2 in this case returns "Mobile" and my header row has the following formula (=CONCAT(H15," Form")) where H15 is the cell containing "Mobile". Hence while my .Find function is searching within formulas. — ipenguin67, May 23 '17 at 17:04
It's any line after I set the Header variable that calls that variable. I tried a simple MsgBox Header and that threw the error too. — ipenguin67, May 23 '17 at 17:06
Are you sure that `var2` is in the range you're specifying? After the `Set Header`line, add `Msgbox(h is Nothing)` and see if that shows `TRUE` or `FALSE`. — BruceWayne, May 23 '17 at 17:10
Good call, it's showing True in this case. I tried `LookIn:=xlValues` as well and still getting True. Does the Find function require an exact match? — ipenguin67, May 23 '17 at 18:07
I believe so, but there are workarounds. What value are you looking for and what does the table of info you're searching for look like? Are you looking for say `foo`, in a list with say `foo 123`, `foo123`, etc.? — BruceWayne, May 23 '17 at 18:24

score 0 · Accepted Answer · answered May 23 '17 at 18:22

The problem is that your .Find isn't finding the value. In this case, you can add some code to handle that.

...
Dim Header As Range
Set Header = Range("A59:A159").Find(var2, LookIn:=xlFormulas)

If Header Is Nothing Then
    ' There's no value found, so do something...
    msgbox(var2 & " was not found in the range, will exit sub now."
    Exit Sub
End If

MsgBox Header
...

...of course there are myriad ways/things you can do to handle this. If you still want to execute other code, then wrap everything in an If Header is Nothing Then // 'do something // Else // 'other code // End IF type thing.

It really just depends on what you want to do. Again, your error is being caused by the fact that the var2 isn't being found, so just find other things to do in that case.

Finding a cell based on the header of a section of data, then selecting the last row of that section

1 Answers1