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

M

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
 
D

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"
 
J

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.
 
M

mario.milani

it works perfecly thak you DON.
Ciao
Mario


Don said:
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"
Click to expand...
 
M

mario.milani

Thank you JE
I succeed with: on error goto mymsg etc. etc., but i saved your code
becouse I want to study it.
Escuse me but what exactely is a "ranfe object?
And what does it mean IMO?
Sorry I haven't many familiarity with VB...
Ciao
Mario
 
D

Dave Peterson

A range object is a group of one or more cells in a worksheet. He was saying
that instead of including a bunch of .selects in your code:

range("a1").select
selection.value = "hi there"

You could just work on the range itself:
range("a1").value = "hi there"

IMO = In My Opinion.

You can find the definition of more acronyms at:
http://www.hiddenlab.com/acronym

Take a look at JE's code. You'll see he tries to find it and then checks to see
if he found it or not.

I've found that when I use "on error resume next", I can hide an error that
shouldn't be hidden (that should be corrected).
 
M

mario.milani

Ok Dave I understand, I already use it. In italian it has another name.
In this case I am inside of a range.
When I register a macro with "relatives reference" (I hope it's the
right traslation from italian) and I go for example on the left or down
the code is like this: ActiveCell.Offset(1, 0).Range("A1").Select.
I don't know what does it mean Range("A1") becouse I don't select any
"absolute reference". May be I can delete it from code...
Thank you
Ciao
Mario
 
D

Dave Peterson

Activecell.offset(1,0)
comes down one cell.
activecell.offset(1,0).range("a1")
is the first cell in the range described by activecell.offset(1,0)

These two are equivalent.

Activecell.range("a2")
would also be equivalent.

Imagine if you overlaid all the cells A1:IV655536 over a difference refence cell
(say C9).

So Range("C9").range("a1") is the same as range("c9")

Range("c9").range("B3")
means go over 1 column and down two more (1 is the row you're on).

Alan Beban has some notes at Chip Pearson's site that you may want to
read/print that have some more variations for using ranges.
http://www.cpearson.com/excel/cells.htm
 
Top