Hi!

I'm stuck. I have a working macro but it needs a small tweek. The macro
executes a find statement and performs calculations from the find to the end
of the column. The problem is when nothing is found. I need an if statement
or suggestion on how to tell it to skip the calculations if there is nothing
found. This is what I have so far(with no if's):

Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
.FormulaR1C1 = "=RC[-1]*RC[6]/12"
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, -2).Select
ActiveSheet.Paste

Thanks in advance for all your help.
--
A Waller

Try

Set foundcell = Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas
_
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
If foundcell Is Nothing Then
MsgBox "No match found"
Exit Sub
End If


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1

What type of variable is foundcell?

Sorry, Dim it as Range


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1

Well, this is a step in the right direction but what i need it to do is to
perform the calculation if Rim is found then continue with the rest of the
macro. If it is not found, it needs to skip the calculations and perform the
rest of the macro. I used the recorder and alot of help from you guys to get
it this far. This is the entire code. The if statement is only the beginning.

'This section selects the BOMSized tab and sorts by product code.

Sheets("BOMSized").Select
Rows("2:3").Select
Selection.Delete Shift:=xlUp
Columns("D:D").Select
Range("A1:N94").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'This section will find the first instance of Rim and perform calculations
to the end of the column.

Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
.FormulaR1C1 = "=RC[-1]*RC[6]/12"
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, -2).Select
ActiveSheet.Paste

'This segment will move all used tabs to the beginning of the workbook and
add a new sheet.

Sheets("Structural").Select
Sheets("Structural").Move Befo=Sheets(1)
Sheets("PropertySets").Select
Sheets("PropertySets").Move Befo=Sheets(2)
Sheets("BOMSized").Select
Sheets("BOMSized").Move Befo=Sheets(3)
Sheets("Structural").Select
Sheets.Add
Range("A1").Select

'Copy sorted information from BOMSized to new sheet.

Sheets("BOMSized").Select
Range("D:D,E:E").Select
Range("E1").Activate
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A1").Select
ActiveSheet.Paste


'Format numbers for subtotal.

Sheets("Sheet1").Select
Range("C2").Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
.FormulaR1C1 = "=RC[-1]*1"
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("C:C").Select
Selection.ClearContents

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1").Select
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Replace What:="total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
Cells.Find(What:="JobInformation", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("G2").Select
ActiveSheet.Paste
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Comments"
Range("G2").Select
Columns("B:B").Select
Selection.NumberFormat = "0"

You guys already know you're the best right?
--
A Waller


"VoG via OfficeKB.com" wrote:

Sorry, Dim it as Range


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1

Maybe you could put the code that depends on you finding the cell inside an if
statement...


dim FoundCell as range
'rest of do always code...

'here comes the .find stuff
Set foundcell = Cells.Find(What:="RIM", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If foundcell Is Nothing Then
'do nothing
Else
foundcell.Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
.FormulaR1C1 = "=RC[-1]*RC[6]/12"
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, -2).Select
ActiveSheet.Paste
End If

'continue with rest of code to do everything else

A Waller wrote:

Well, this is a step in the right direction but what i need it to do is to
perform the calculation if Rim is found then continue with the rest of the
macro. If it is not found, it needs to skip the calculations and perform the
rest of the macro. I used the recorder and alot of help from you guys to get
it this far. This is the entire code. The if statement is only the beginning.

'This section selects the BOMSized tab and sorts by product code.

Sheets("BOMSized").Select
Rows("2:3").Select
Selection.Delete Shift:=xlUp
Columns("D:D").Select
Range("A1:N94").Sort Key1:=Range("D2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'This section will find the first instance of Rim and perform calculations
to the end of the column.

Cells.Find(What:="RIM", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, 1).Select
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
.FormulaR1C1 = "=RC[-1]*RC[6]/12"
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(0, -2).Select
ActiveSheet.Paste

'This segment will move all used tabs to the beginning of the workbook and
add a new sheet.

Sheets("Structural").Select
Sheets("Structural").Move Befo=Sheets(1)
Sheets("PropertySets").Select
Sheets("PropertySets").Move Befo=Sheets(2)
Sheets("BOMSized").Select
Sheets("BOMSized").Move Befo=Sheets(3)
Sheets("Structural").Select
Sheets.Add
Range("A1").Select

'Copy sorted information from BOMSized to new sheet.

Sheets("BOMSized").Select
Range("D:D,E:E").Select
Range("E1").Activate
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("A1").Select
ActiveSheet.Paste


'Format numbers for subtotal.

Sheets("Sheet1").Select
Range("C2").Select
Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)) _
.FormulaR1C1 = "=RC[-1]*1"
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Columns("C:C").Select
Selection.ClearContents

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=False
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A1").Select
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Replace What:="total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Columns("A:A").EntireColumn.AutoFit
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Range("A1").Select
Cells.Find(What:="JobInformation", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
Range("G2").Select
ActiveSheet.Paste
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Comments"
Range("G2").Select
Columns("B:B").Select
Selection.NumberFormat = "0"

You guys already know you're the best right?
--
A Waller

"VoG via OfficeKB.com" wrote:

Sorry, Dim it as Range


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200507/1


--

Dave Peterson