title: Excel 2010 VBA - Search Active Column for "Delete" then "Add/Retain"
body: <p>I am very new to VBA and need some guidance here. I have found little snippets here and there but can't seem to figure out how to place them all together.</p>
  
  <p>The overall script looks at a specific cell to see what store number is being worked on, then goes to a different sheet to find that number in row 2. On this other sheet, Column C contains all possible items for the stores in the region. Once the store number is located in row 2, that column is now important as it lists "Delete" or "Add/Retain" for the previously mentioned items listed in Column C.</p>
  
  <p>At the point in my script where I am at, I have the header cell active, on sheet2, that is the store number. Below it lists whether the products going down column C should be "Delete" or "Add/Retain".</p>
  
  <p>I am looking for help on how to search the active column for "Delete" and "Add/Retain" then momentarily transfer the product number from column C to a variable that can be used externally. </p>
  
  <p>For example, let's say the store number corresponded to Group 3. This is located in Column F on Sheet2. Below it is a specific/unique set of "Delete" or "Add/Retain". The first item down says "Delete", so the script should go to column C in which it will find item number "51308". It sets a variable to this value then passes it to my external program where it searches the database for that number (51308) and removes it since it was a "Delete". It writes this action to a text file "Deleted item 51308 from Group 3". Now the script moves down another row and there is "Add/Retain" listed. Back to column C (product list) and it lists "74063". The temporary variable gets set to this new product number, gets passed to my program, the database is checked to see if the number is currently there, if it is, nothing happens, if it isn't, it gets added and the text file is written a new line of "Added 74063 to Group 3".</p>
  
  <p>That's it. Easy right!? </p>
  
  <p>This is a screenshot of the Sheet2 I was mentioning.</p>
  
  <p><img src="https://i.stack.imgur.com/I6mnF.jpg" alt="screenshot"></p>
  
  <p>So far I have this:</p>
  
  <pre><code>Dim rng1 As Range
  Dim strSearch As String
  strSearch = "Delete"
  Set rng1 = Range("D:D").Find(strSearch, , xlValues, xlWhole)
  If Not rng1 Is Nothing Then
      Set rng2 = rng1.Offset(0, -1)
      MsgBox rng2
      MsgBox "Find has matched " &amp; strSearch &amp; vbNewLine &amp; "Corresponding cell is " &amp; rng1.Offset(0, -1)
  Else
      MsgBox strSearch &amp; " not found"
  End If
  </code></pre>
  
  <p>The script finds the first "Delete", then goes back one column to column C and displays the item number. I could then pass this on to my program no problem. 
  This is ok except the store group could be anywhere on Row 2 (not just column D).</p>