Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenge with conditional find
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenge with conditional find
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 "Vic" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenge with conditional find
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 "Vic" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Challenge with conditional find
On May 11, 4:05 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: 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 "Vic" wrote: 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- Hide quoted text - - Show quoted text - This worked great! thank you folks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Conditional formatting challenge question | Excel Discussion (Misc queries) | |||
conditional formatting challenge | Excel Discussion (Misc queries) | |||
conditional statement to find more than one value ?? | Excel Programming | |||
Conditional find | Excel Programming | |||
Conditional find | Excel Programming |