macro to find value in cell and copy that row to toher sheet

  • Thread starter Thread starter Jim A
  • Start date Start date
J

Jim A

Hi - I am working on getting a macro to find a name in column "B" and once
found copy its row to another sheet.

I have been working on an "IF" statement that checks for the given name and
if true copies the row. I can not get it to keep checking and then stop
using "loop"

Can anybody help?

Thanks - JimA
 
If the number of entries in column A, then looping doesn't seem like a real bad
idea. But if that list gets any size, it's better to use another
technique--like using Edit|Find.

Option Explicit
Sub testme()

Dim FoundCell As Range
Dim Wks As Worksheet
Dim DestCell As Range
Dim WhatToFind As String

Set Wks = Worksheets("Sheet1") 'whatever the name is???

WhatToFind = "Joe Smith"

With Wks
With .Range("A1").EntireColumn
Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
MsgBox WhatToFind & " wasn't found!"
Else
With Worksheets("Sheet2") '???
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
FoundCell.EntireRow.Copy _
Destination:=DestCell
End If
End Sub
 
Hey Thanks for the response!
What do I need to do if I only want on column to be searched? (In this case
"I")

If I am reading this code right, it looks like it is searching the entire
sheet, row by row. I need it row by row, but only looking in column "I"

Thanks again - JimA
 
OOPS - is it the
"With .Range("A1").EntireColumn" line that applies the search to one column?

JimA
 
Nope.

It's only searching column A:

With .Range("A1").EntireColumn
will become:
With .Range("I1").EntireColumn
 
OK - it is working. After it finds the first match, what gets it to keep
looking for more matches?

Thanks - Jim A
 
Nothing.

It finds the first match, does the copy|paste and stops.

If you need it to do more, look at VBA's help for FindNext.

There's an example there.

Jim said:
OK - it is working. After it finds the first match, what gets it to keep
looking for more matches?

Thanks - Jim A
Click to expand...
 
Thanks for all your help - this is what i got to work:
Sub FIND_TEST3()
Dim n1 As String
Dim Wks As Worksheet
Dim c As Range
Dim firstAddress As String
Dim DestCell As Range

Set Wks = Worksheets("IRS Commission")
n1 = Sheets("Employee Data Entry").Range("D2").Value

With Wks
With .Range("I1").EntireColumn
Set c = .Cells.find(What:=n1, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not c Is Nothing Then
firstAddress = c.Address
Do
With Worksheets(n1)
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
c.EntireRow.Copy _
Destination:=DestCell
Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End With
End Sub

Thanks again - Jim A
 
Back
Top