G
Guest
I am writing a macro that takes data from a sheet showing amounts invoiced in
a given month, decides what type of work was invoiced, and calclulates totals
for the different types of work.
I have one worksheet that shows the values billed in column E, and the type
of work billed in column F. I want to find all cells in column F with the
same type/value, then select the corresponding cells in column E, and apply a
name to the range that I can then insert into formulas.
The code I am using to do this is shown below. The problem I have is that
if a particular term I search for is not in column F, all the rows in column
E will be selected (which doesn't help me much).
What I would like to do is change the code to say that if there are no
matching entries in column F, no range is created. And later when I create
my formulas, I want something to say that if a particular range doesn't
exist, the value of the cell should be zero.
Any help doing this would be appreciated.
Thanks
MMH
***
'
' Creating Name for Search range to use in later formula
'
Selection.AutoFilter Field:=6, Criteria1:="Search"
Range("A1").Select
Cells.Find(What:="Search", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Name = "Search"
'
' Get values of search column from this month's workbook
'
Sheets("YTD Totals").Select
Range("E8").Select
ActiveCell.FormulaR1C1 = "=SUM(Search)"
Range("E9").Select
a given month, decides what type of work was invoiced, and calclulates totals
for the different types of work.
I have one worksheet that shows the values billed in column E, and the type
of work billed in column F. I want to find all cells in column F with the
same type/value, then select the corresponding cells in column E, and apply a
name to the range that I can then insert into formulas.
The code I am using to do this is shown below. The problem I have is that
if a particular term I search for is not in column F, all the rows in column
E will be selected (which doesn't help me much).
What I would like to do is change the code to say that if there are no
matching entries in column F, no range is created. And later when I create
my formulas, I want something to say that if a particular range doesn't
exist, the value of the cell should be zero.
Any help doing this would be appreciated.
Thanks
MMH
***
'
' Creating Name for Search range to use in later formula
'
Selection.AutoFilter Field:=6, Criteria1:="Search"
Range("A1").Select
Cells.Find(What:="Search", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select
Range(Selection, Selection.End(xlDown)).Name = "Search"
'
' Get values of search column from this month's workbook
'
Sheets("YTD Totals").Select
Range("E8").Select
ActiveCell.FormulaR1C1 = "=SUM(Search)"
Range("E9").Select