Hi,

With the below code, I can print areas that match a criteria.

Problem : I have to copy/paste the same code several times if, fo
instance, there are 6 areas matching my criteria.


So, my question is : How to modify this code to work whatever how man
criteria match ? Maybe a loop ? How ?

Second question : How can a display a MsgBox and Exit Sub if non
criteria match ?

I hope that I've been understandeable but I'm not sure. Even in m
native language it's hard to explain so in English.... ;)

Anyway, thank you very much in advance for your help,


Greg

/////////////////////////////////////////

Sub Macro1()
'

' searcg criteria : It will find everything like "342 gregory", "54
georges", etc.

variablefund = "42 g"

' Starting point

Range("A1").Select

' Search


Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
False).Activate

' Define first result as a variable

StartCell = ActiveCell.Value

' Select areas until the word "Total"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
True)).Select

' Print

Selection.PrintOut Copies:=1, Collate:=True


'***

Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True

'***


Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True


'***



Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True


'**

--
Message posted from http://www.ExcelForum.com

I'm not very sure of the first problem, but the msgbox is
quite easy.

'Assuming you understand that I have assigned a fictious
variable here called "no_criteria_matches" you can add
the msgbox command just before exit sub command, and will
have the desired effect.

If no_criteria_matches = True Then

MsgBox "No criteria Matches"

Exit Sub

End If



-----Original Message-----
Hi,

With the below code, I can print areas that match a
criteria.

Problem : I have to copy/paste the same code several
times if, for
instance, there are 6 areas matching my criteria.


So, my question is : How to modify this code to work
whatever how many
criteria match ? Maybe a loop ? How ?

Second question : How can a display a MsgBox and Exit
Sub if none
criteria match ?

I hope that I've been understandeable but I'm not sure.
Even in my
native language it's hard to explain so in English.... ;)

Anyway, thank you very much in advance for your help,


Greg

/////////////////////////////////////////

Sub Macro1()
'

' searcg criteria : It will find everything like "342
gregory", "542
georges", etc.

variablefund = "42 g"

' Starting point

Range("A1").Select

' Search


Cells.Find(What:=variablefund, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
False).Activate

' Define first result as a variable

StartCell = ActiveCell.Value

' Select areas until the word "Total"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS",
After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
True)).Select

' Print

Selection.PrintOut Copies:=1, Collate:=True


'***

Cells.Find(What:=variablefund, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS",
After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True

'***


Cells.Find(What:=variablefund, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS",
After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True


'***



Cells.Find(What:=variablefund, After:=ActiveCell,
LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS",
After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True


'***


---
Message posted from http://www.ExcelForum.com/

.

Greg,

Try this.

Not been able to test it, but hope it is there or at least close

Sub Macro1()
Dim oCell
Dim oTotCell
Dim iColumn As Long

' searcg criteria : It will find everything like "342 gregory", "542 georges
", etc."

variablefund = "42 g"

' Search
With Cells
Set oCell = .Find(What:=variablefund, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate
If Not oCell Is Nothing Then
Do
startcell = oCell.Address
iColumn = oCell.End(xlToRight).Column
Set oTotCell = Cells.Find(What:="TOTALS", _
After:=oCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
Range(oCell.Address, Cells(oTotCell.Row, iColumn)).PrintOut
Copies:=1, Collate:=True
Set oCell = .FindNext(oCell)
Loop While Not oCell Is Nothing And oCell.Address < startcell
End If
End With

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grek " wrote in message
...
Hi,

With the below code, I can print areas that match a criteria.

Problem : I have to copy/paste the same code several times if, for
instance, there are 6 areas matching my criteria.


So, my question is : How to modify this code to work whatever how many
criteria match ? Maybe a loop ? How ?

Second question : How can a display a MsgBox and Exit Sub if none
criteria match ?

I hope that I've been understandeable but I'm not sure. Even in my
native language it's hard to explain so in English.... ;)

Anyway, thank you very much in advance for your help,


Greg

/////////////////////////////////////////

Sub Macro1()
'

' searcg criteria : It will find everything like "342 gregory", "542
georges", etc.

variablefund = "42 g"

' Starting point

Range("A1").Select

' Search


Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
False).Activate

' Define first result as a variable

StartCell = ActiveCell.Value

' Select areas until the word "Total"

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
True)).Select

' Print

Selection.PrintOut Copies:=1, Collate:=True


'***

Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True

'***


Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True


'***



Cells.Find(What:=variablefund, After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
False).Activate

If ActiveCell.Value = StartCell Then
Exit Sub
End If

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Cells.Find(What:="TOTALS", After:=ActiveCell,
LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=
_
True)).Select
Selection.PrintOut Copies:=1, Collate:=True


'***


---
Message posted from http://www.ExcelForum.com/

Hi,

Thank you for your reponse.

Bob, I tried you code, unfortunately it seems that there is an proble
with this part :

Set oCell = .Find(What:=variablefund, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate

In french the message that I get could be translate like "Typ
incompatibility" or something like that.

I've attached a test file. If you could have a look at this issue...

Many thanks for your help,

Gre

Attachment filename: testmacrogreg.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=66290
--
Message posted from http://www.ExcelForum.com

Greg,

I left a bit on I shouldn't have,. Also found a couple of problem, so
corrected those.

Dim oCell As Range
Dim oTotCell
Dim iColumn As Long

' searcg criteria

variablefund = "42 g"



' Search
With Cells

Set oCell = .Find(What:=variablefund, _
after:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

startcell = oCell.Address

If Not oCell Is Nothing Then
Do
iColumn = oCell.End(xlToRight).Column
Set oTotCell = Cells.Find(What:="TOTALS", _
after:=oCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=True)
Range(oCell.Address, Cells(oTotCell.Row, 8)).Select
'PrintOut Copies:=1, Collate:=True

Set oCell = .Find(What:=variablefund, _
after:=oTotCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Loop While Not oCell Is Nothing And oCell.Address < startcell
End If
End With

Note, the end of line doesn't work so I have hard-coded ciolumne 8 in
(Range(oCell.Address, Cells(oTotCell.Row, 8)).Select 'PrintOut Copies:=1,
Collate:=True). You might want to extend this.

BTW hate what ExcelForum does to the code
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grek " wrote in message
...
Hi,

Thank you for your reponse.

Bob, I tried you code, unfortunately it seems that there is an problem
with this part :

Set oCell = .Find(What:=variablefund, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False).Activate

In french the message that I get could be translate like "Type
incompatibility" or something like that.

I've attached a test file. If you could have a look at this issue...

Many thanks for your help,

Greg

Attachment filename: testmacrogreg.xls
Download attachment:
http://www.excelforum.com/attachment.php?postid=662901
---
Message posted from http://www.ExcelForum.com/

Hi Bob,

It works great now. Thanks a lot !

I've just analysed your code and I understand the main things, howeve
as I'm not really familiar with and end with and set things, it woul
be really cool from you if you could explain me this code, part b
part, why you used the .address, etc.

Anyway thank you very very much for your help !!!

Gre

--
Message posted from http://www.ExcelForum.com

Hi Greg,

' Search
With Cells

With is used to set a reference to an object, so as to repeatedly us
that object without repeatedly referencing it. This makes the code more
readable, as you don't clutter it with object statements,, and more
efficient as VBA does not have to keep resolving the reference

Set oCell = .Find(What:=variablefund, _
....

In your code, you activated the find, in other words you selected the
found cell. By using Set, I am Setting a range object (oCell) to the cell
that the value is found in. This has two advantages, we do not need to
select the cell (which is inefficient), and we have an object which we can
test (for nothing or not). This is similar to getting details about a
country by looking it up in a reference book, rather than visiting it, a lot
less effort (though maybe not as much fun<vbg).

startcell = oCell.Address

This is just saving the address of the first found cell, so that we can
test we are done

If Not oCell Is Nothing Then

Testing for Nothing is how to test objects to see whether they have been
set. If we did not find anything, the object oCell would be Nothing (similar
to a cell being empty).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Grek " wrote in message
...
Hi Bob,

It works great now. Thanks a lot !

I've just analysed your code and I understand the main things, however
as I'm not really familiar with and end with and set things, it would
be really cool from you if you could explain me this code, part by
part, why you used the .address, etc.

Anyway thank you very very much for your help !!!

Greg


---
Message posted from http://www.ExcelForum.com/

Thanks a lot

--
Message posted from http://www.ExcelForum.com