title: How to search a string in a single column (A) in excel using VBA
body: <p>I want to change these lines in my excel VBA code to something much faster, instead of looping through all the rows, i did saw examples but could not understand them as i am not a VBA user.</p>
  
  <p>When I used the code in samples (google,this site) I don't see the proper need I want, I want to search column A and if values found return the values in column B next to the searched values, else return empty.</p>
  
  <p>Most of the code I used returned error when not found and some other mysterious behavior.</p>
  
  <p>My current code to search is:</p>
  
  <pre><code>Dim k As Integer
  For k = 2 To sheet2Counter - 1                      
      Dim tmp As String                      
  
      tmp = ActiveSheet.Range("A" &amp; k).Value                      
      If tmp = tmpstr Then                      
          tmp = ActiveSheet.Range("B" &amp; k).Value                      
          tmp = Replace(tmp, "Q", "A")                      
          mainstringtopaste = mainstringtopaste + tmp + ","
  
              Exit For                      
      End If                    
  Next k
  </code></pre>
  
  <p>Also let me know if this is a better way or any code that will replace it to be more fast.</p>
  
  <p>Columns in the sheet to be searched are  like:</p>
  
  <pre><code>ColumnA        ColumnB
  trees          leaves
  oranges        fruits
  pineapple      fruits
  leaves         trees
  </code></pre>
  
  <p>So as my above code, trees should be searched and leaves should be returned...</p>
  
  <p>Thank you</p>