title: VBA .Find to use for Setting Ranges
body: <p>I am very new to Excel, but one thing that seems to be super powerful is using .Find to name ranges. Unfortunately, I'm only seen people use it with message boxes and things like that afterwards. What I'm trying to do is a bit more complicated.</p>
  
  <p>I am trying to find a cell named 'State' somewhere in the 'rec' worksheet. After it finds it I need to set a range from one cell above the 'State' cell up six cells.</p>
  
  <p>This is what I have so far:</p>
  
  <pre><code>Dim rec As Worksheet
  
  Set rec = ThisWorkbook.Sheets("Rec")
  
  Dim State As String
  
  Dim MoveHeadersUp As Range
  
  State = "State"
  
  
  Set MoveHeadersUp = rec.Rows.Find(What:=State, LookIn:=xlValues, _
  Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
  MatchCase:=False, SearchFormat:=False)
  
  If MoveHeadersUp Is Nothing Then
  
  MsgBox "Uh-Oh. Column A is missing a header titled 'State'. You better create one!"
  
  Else
  </code></pre>
  
  <p>???????</p>
  
  <pre><code> End Sub
  </code></pre>