Iteration through column to find a particular value

Question

I am trying to go through a column of empty cells in my excel spreadsheet in order to find the row in which the word "Yes" is found. Afterwards, upon finding the word in a particular row, for instance in cell D23, I want it to go over one column to cell E23 and paste the value in that cell into cell B100. Here is what I have so far, but it doesn't seem to be functioning correctly:

     Sub Test3()
      Dim x As String
      x = "Yes"
      ' Dim found As Boolean
      ' Select first line of data.
      Range("D4").Select
      ' Set search variable value.
      ' Set Boolean variable "found" to false.
      found = False
      ' Set Do loop to stop at empty cell.
      Do Until ActiveCell.Value = x
         ' Check active cell for search value.
         If ActiveCell.Value = x Then
            Range("B100").Value = ActiveCell.Offset(0, 1).Value
            found = True
            Exit Do
         End If
         ' Step down 1 row from present location.
         ActiveCell.Offset(1, 0).Select
      Loop
   ' Check for found.
           If found = True Then
         MsgBox "Value found in cell " & ActiveCell.Address
      Else
         MsgBox "Value not found"
      End If
   End Sub

Thanks!

Why not use the Range object's `Find` method? It will be *much* quicker than cell-by-cell VBA looping. — John Coleman, Aug 08 '16 at 17:48

score 2 · Accepted Answer · answered Aug 08 '16 at 17:55

As @tigeravatar mentioned in his comment, you'd probably be better off using Excel's native functions, but, if you want to do this via VBA, you can do it much more easily and efficiently using the Find function which returns a range if found or else 'Nothing' if not.

Using that, you can test to see what you got back. Try this:

Sub Test3()
Dim x As String
Dim rng As Range

    x = "Yes"

    Set rng = Range("D4:D10000").Find(x)

    If Not rng Is Nothing Then
        Range("B100").Value = rng.Offset(0, 1).Value
        MsgBox "Value found in cell " & rng.Address
    Else
        MsgBox "Value not found"
    End If
End Sub

Hope it does the trick.

@S.Z. - Just FYI - If you notice, there's no use of `.Select`, which is another thing the solution does for you - it [avoids the use of `.Select`/`.Activate`, which is a best practice](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). — BruceWayne, Aug 08 '16 at 18:15

Iteration through column to find a particular value

1 Answers1