title: Excel looping from column A and finding matches with corresponding values and moving them to a new worksheet
body: <p>Excel looping from column A and finding matches with corresponding values and moving them to a new worksheet.</p>
  
  <p>Fairly new to excel VBA.  Having trouble creating a loop for finding matching drug names with corresponding values in a worksheet when compared to drugs in column A. The matches to column A would be reported in order on a second worksheet.</p>
  
  <p><strong>Intention:</strong></p>
  
  <p>Raw data - Drugs (column A) with corresponding values (scores in column B) from one cell line are listed.  Then in column C and D, drugs and corresponding values, respectively, from another cell line are listed.  This can go on and on depending on how many cell lines are tested. The key is that not all cell lines are treated with all the same drugs.  We are trying to only sort the matching drugs with corresponding values. We will then be able to compare how drugs work across cell lines.</p>
  
  <p>See the included figure for an example of the data sets. The headers for the cell line (Row 1) and Drug and Score (Row 2) take up the first 2 rows.  </p>
  
  <p>Starting with the first drug in column A, Range (“A3”) in my example, you would look from matches in column C, E, G, etc, depending on how many cell lines are analyzed. If a match is found, the drug with its corresponding data is then placed on a “Sorted” worksheet.  If for example 3 cell lines with data were reported, the original drug name and data from Cell line A would be placed on Range(“A3, B3”), the matches from Cell line B would be placed next to the original in the order Range(“C3,D3”), and the matches from cell line 3 would be placed on Range(“E3,F3”).</p>
  
  <p>We have drug and response (scores) data from many cell lines, but not all the drugs were tested in every cell line.  We want to find the common drugs that were tested in all the cell lines.</p>
  
  <p>I was able to write a macro (see below) to find a match in the first compared column (C) and report the row value, but I was stuck after that when I tried to consider how to extract that name and corresponding value and move it to the sorted worksheet.  I know all the general language for copy, paste, offset, looping, but when I start working with Dim’s I get lost on how to extract those findings.</p>
  
  <p>Any help would be appreciated.
  Thanks in advance!</p>
  
  <p><a href="https://i.stack.imgur.com/8TY1C.jpg" rel="nofollow noreferrer">Drug and score table</a></p>
  
  <pre><code>Sub Find_matching_drug()
  
      'Declaring variables. will start with first _
      drug in column A3, then move to A4, A5, etc _
      matches with corresponding values will be sent _
      to a different worksheet
  
      Dim i As Integer, drug_to_find As String
  
      'drug_to_find (variable) is defined as A3
  
      drug_to_find = Range("A3").Value
  
      MsgBox drug_to_find
  
      For i = 1 To 500  ' searches column up to 500 rows for the match
          If Cells(i, 3).Value = drug_to_find Then
  
              MsgBox ("Found value on row " &amp; i)
              Exit Sub
          End If
      Next i
  
      ' This MsgBox will only show if the loop completes with no success
      MsgBox ("Value not found in the range!")
  End Sub
  </code></pre>