Find Next?

[benpatz]

Hi,

I was able to figure out from an online video how to find a value in excel and report a value in the same row back into excel in a different workbook and cell.

Sub Findmg ()

Dim lastrow as Long
Dim Class as String
Dim rownumber as long
Dim LookInRange as Range

lastrow = ThisWorkbook.Sheets("Sheets1").Range("B" & Rows.Count).End(xlUp).Row

Class = "mg"

Set LookInRange = ThisWorkbook.Sheets("Sheet1").Range("B1:B" & lastrow).Find(What:=Class, LookIn:=xlValues)
rownumber = LookInRange.Row
ThisWorkbook.Sheets("Sheet2").Cells(3,3).Value = ThisWorkbook.Sheets("Sheet1").Cells(rownumber, 3).Value

End Sub

Is there anyway I can use the find next function. I have multiple "mg" values. I want to report each value back on sheet 2.

And, why do so many lines of code end in .Row? Does .Row pick the entire row or the row number the value was found in?

 

[Logit]

.
Here is another version of a macro :

Option Explicit


Sub SearchMGCpyPaste()
  Dim rFind         As Range
  Dim sAdr          As String
    
  With Sheets("Sheet1").Range("B1:B1000") 'change Col B range here
    .Select
    Set rFind = .Find(What:="*mg", _
                       After:=.Cells(.Cells.Count), _
                       LookIn:=xlValues, _
                       LookAt:=xlWhole, _
                       SearchOrder:=xlNext, _
                       MatchCase:=True)
    If Not rFind Is Nothing Then
      sAdr = rFind.Address
      Do
        Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2).Value = rFind.Value2
        Set rFind = .FindNext(rFind)
      Loop While rFind.Address <> sAdr
    End If
  End With
   Sheets("Sheet1").Range("A1").Select
End Sub

 

[benpatz]

Thanks, there is a lot here to look at. I was worried about that.

 

[My Aswer Is This]

Try this:

You will see I have entered the value One Two and Three as search values
Modify these search values as needed and add more if needed.

Sub Find_Me()
'Modified  9/11/2018  10:01:15 PM  EDT
Dim r As Range
Dim Lastrow As Long
Sheets("Sheet1").Activate
Lastrow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
Dim Del As Variant
Dim x As Long
x = 1
Del = Array("One", "Two", "Three")
ans = UBound(Del)
For i = 0 To ans

For Each r In Range("B1:B" & Lastrow)
If r.Value = Del(i) Then Sheets("Sheet2").Cells(x, 1).Value = r.Offset(, 1).Value: x = x + 1
Next
Next
End Sub