How to Cell Find with VBA

From image below, we want to find "Apple"

 

 With Find and Replace,

If we do it by Macro, can do like below code:

1 2 3 4 5 6 7 8 9 10 11

Sub Macro1() ' ' Macro1 Macro ' ' Cells.Find(What:="Apple", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate End Sub

Above code could be simplified, and we can also get value from next column.

1 2 3 4 5

Sub Sample1() Dim Target As Range Set Target = Cells.Find(What:="Apple") MsgBox "Value:" & Target.Offset(0, 1) End Sub

Above code maybe only search from group 1, if we want to find from all group we can do like this

1 2 3 4 5 6 7 8

Sub Sample2() Dim Target As Range, msg As String Set Target = Range("A3:A8").Find(What:="Apple") msg = "Apple from group1:" & Target.Offset(0, 1) & vbCrLf Set Target = Range("C3:C8").Find(What:="Apple") msg = msg & "Apple from group2:" & Target.Offset(0, 1) MsgBox msg End Sub

Download File