title: VBA: Store value to variable
body: <pre><code>Sub test() ' ' test Macro '
  
      Dim aRange As Range
      Dim i As Integer
      
      Set aRange = Range(&quot;A1:A255&quot;)
      
      Range(&quot;A1&quot;).Select
      For i = 1 To aRange.Count - 1
      
          If InStr(ActiveCell.Value, &quot;Last name&quot;) Then
              Call CopyContents
          End If
          ActiveCell.Offset(1, 0).Select 
          
      Next i
      
  End Sub
  
  
  Sub CopyContents()
      Dim currentRange As Range
      Dim genderAndDiscipline As String
  
      Set currentRange = Range(ActiveCell.Address)
      
      'get the gender and dicipline
      Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value
      'genderAndDiscipline = genderAndDiscipline.Split(&quot; &quot;)
      
  End Sub
  </code></pre>
  <p>I'm trying to store a cell value in a variable. But somehow it's keep giving me an compile error:</p>
  <blockquote>
  <p>Object required</p>
  </blockquote>
  <p>In my opinion, I'm telling the variable to aspect a string and the cell is containing a string, as the debugger says.</p>
  <p>The currentRange is 'A7' here and the cell above is containing a string with '200m men'</p>
  <p>The error occurs at<br />
  <code>Set genderAndDiscipline = ActiveCell.Offset(-1, 0).Value</code></p>