Excel – Find and FindNext for Excel VBA

excelvba

I've been stuck trying to figure out what to do with this, but basically I want a way to print out the value in column B given a specific value that matches column A. So for example:

Column A    Column B
1           ABC
2           DEF
3           GHI
1           JKL

I want to, after using find/findnext or whatever it is, to print out this string:

ABC JKL

I tried using

Set cellFound = ActiveWorkbook.Worksheets("sheet1").Range("F1:F1000000").Find("1")
string = cellFound.Offset(0, 1).value

And I have a loop to loop through as many time as it needs to get all the rows taken care of. But with find it keeps returning me the same first string ("ABC") and the string ends up being ABC ABC instead of ABC JKL

I tried using FindNext instead of find, but what I got is a 1004 Error. So I'm not really sure where I'm doing this wrong. Anyone has any idea?

Best Solution

You don't need FindNext if you start each Find after the previous one:

Sub qwerty()
   Dim rFirst As Range, r As Range
   Dim A As Range
   Set A = Range("A:A")
   Do
      If rFirst Is Nothing Then
         Set rFirst = A.Find(What:=1, After:=A(1))
         Set r = rFirst
      Else
         Set r = A.Find(What:=1, After:=r)
         If r.Address = rFirst.Address Then Exit Do
      End If
         MyString = MyString & " " & r.Offset(0, 1)
   Loop

   MsgBox MyString
End Sub

enter image description here