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

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

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



wrote:

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

--

Dave Peterson

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

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

wrote:

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

--

Dave Peterson