macro ...if command, I suppose...

[Mario]

Hi everybody

I recorded a macro wich finds the word "new date" and then copies the
corresponding rows in another sheet... (no problem as far as here).

I would like if it found no occurrence it ended e gave a message as "no
occurrence found" instead of indicating a run time error.

I think it need an if command, I tried but without success...

I send the mail

Cells.Find(What:="new date", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate.

ActiveCell.Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlToLeft)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Sheets("ScadenzeClienti").Select
Application.Goto Reference:="R1C1"
Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
End Sub

Thanks Mario

 

[Don Guillett]

Have a look at ON ERROR (easy way is to type on error in the vbe and hit f1
key)
on error goto mymsg

code
mymsg:
"blah blah"

 

[Sharad]

Hi Mario,

Though what you want to do can be done in a small code,
since you are new and learning I will answer exactly your question part.

Change the the code first few lines as under:

Err.Clear
On Error Resume Next
Cells.Find(What:="new date", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False).Activate
If Err <> 0 Then
MsgBox "No occurance found."
Exit Sub
End If
'continue your rest code
ActiveCell.Offset(0, -1).Range("A1").Select
' and so on

 

[JE McGimpsey]

One way:

Dim rFound As Range
Dim rDest As Range
Set rFound = ActiveSheet.Cells.Find( _
What:="new date", _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If rFound Is Nothing Then
MsgBox "No occurrence found."
Else
Set rDest = _
Sheets("ScandenzeCliente").Range("A1").End(xlDown).Offset(1, 0)
With rFound.Offset(0, -1)
With Range(.Cells, .End(xlToLeft).End(xlDown))
rDest.Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End With
End If


Note that you almost never have to select a range in order to work with
it. Using range objects makes your code faster, smaller, and, IMO,
easier to maintain.

 

[mario.milani]

it works perfecly thak you DON.
Ciao
Mario

Have a look at ON ERROR (easy way is to type on error in the vbe and hit f1
key)
on error goto mymsg

code
mymsg:
"blah blah"