Challenge with conditional find

[Vic]

I would like to seach for a word in a range of cells and when found,
copy and paste that cell into another page. I built a little macro but
have a challenge if the word I am searching-for is not found
(something I did not plan-for and I am a newbie for-sure).


here is what it does now:

' find "component(s)" and copy
Cells.Find(What:="component(s):", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.Copy

' select different worksheet to paste-to
Sheets("Condensed ChangeLog").Select

' Paste "component(s)"
ActiveCell.Offset(2, -1).Select
ActiveSheet.Paste


So with the new issue where it may not be found, I would like to
instead paste in the destination cell the words "no components found".
So it seems I need some logic that if-not found, then...

Help appreciated.
~Vic

 

[Guest]

Use a little error trapping to deal with the no match condition.

Just before your Cells.Find instruction put:

On Error Resume Next

Just before the Selection.Copy statement insert this

If Err<>0 Then
'this is your no match found handler
Err.Clear ' clear the error
ActiveCell.Offset(2, -1) = "no components found"
Else
'this is your found a match area, just put the
'code you have now into it

End If
On Error GoTo 0 ' clear error trapping when leaving.
....
....
End Sub

 

[Guest]

Dim rngFound As Range

Set rngFound = Cells.Find(What:="component(s):", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
Sheets("Condensed ChangeLog").Select
If rngFound Is Nothing Then
ActiveCell.Offset(2, -1).Value = "Not found"
Else
ActiveCell.Offset(2, -1).Value = rngFound.Value
End If

 

[Vic]

Dim rngFound As Range

Set rngFound = Cells.Find(What:="component(s):", _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
MatchCase:=False)
Sheets("Condensed ChangeLog").Select
If rngFound Is Nothing Then
ActiveCell.Offset(2, -1).Value = "Not found"
Else
ActiveCell.Offset(2, -1).Value = rngFound.Value
End If

--
HTH...

Jim Thomlinson











- Show quoted text -

This worked great! thank you folks!