title: Copy Value From Column When Text String Found
body: <p>I wonder whether someone may be able to help me please.</p>
  
  <p>I'm using the code below to search a named range ("ProjectName") for any cell that contains the value "OVH". When this value is found, the script creates a list of unique values from this range and pastes them into the named range "EnhancementsList" </p>
  
  <pre><code>Sub UniqueEnhancements()
  
  Dim MyCell
  
   With CreateObject("scripting.dictionary")
          For Each MyCell In Range("ProjectName").Value
             If InStr(1, MyCell, "OVH") &gt; 0 Then
                 .Item(MyCell) = 1
              End If
          Next
          Range("EnhancementsList").Resize(.Count) = Application.Transpose(.keys)
      End With
  
      Worksheets("Enhancements").Columns("B:B").AutoFit
  
      Range("EnhancementsList").Sort Key1:=Range("EnhancementsList").Cells(2, 1), _
      Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
  
  End Sub
  </code></pre>
  
  <p>The code works fine but, I'd like if possible, to adapt this so that although it checks the "ProjectName" range for the "OVH" text string, it copies, and creates the list of unique values from the column one step to the left with the named range "Task", but I'm not sure how to do this, despite spending a couple of days trying to come up with a solution.</p>
  
  <p>I have tried, with the limited knowledge I have, to change this line:</p>
  
  <pre><code>.Item(MyCell) = 1
  </code></pre>
  
  <p>to</p>
  
  <pre><code>.Item(MyCell.offset -1) = 1
  </code></pre>
  
  <p>and </p>
  
  <pre><code>.Item.offset-1 (MyCell) = 1
  </code></pre>
  
  <p>But I receive mismatch errors.</p>
  
  <p>I just wondered whether someone could possibly take a look at this please and let me know where I'm going wrong.</p>
  
  <p>Many thanks and kind regards</p>