Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro ...if command, I suppose...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro ...if command, I suppose...
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro ...if command, I suppose...
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. In article , "Mario" wrote: 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 |
#5
Posted to microsoft.public.excel.misc, microsoft.public.excel.programming, microsoft.public.excel.worksheet.functions
|
|||
|
|||
macro ...if command, I suppose...
it works perfecly thak you DON.
Ciao Mario Don Guillett wrote: 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" -- Don Guillett SalesAid Software "Mario" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use Macro To Change Which Macro Assigned To Command Button | Excel Discussion (Misc queries) | |||
If command in macro | New Users to Excel | |||
Macro Command | Excel Discussion (Misc queries) | |||
macro ...if command, I suppose... | Excel Discussion (Misc queries) | |||
macro ...if command, I suppose... | Excel Worksheet Functions |