G
Grek
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
'**
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
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
'**