If Then Help!!!!

G

Guest

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

VoG via OfficeKB.com

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
 
G

Guest

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 Before:=Sheets(1)
Sheets("PropertySets").Select
Sheets("PropertySets").Move Before:=Sheets(2)
Sheets("BOMSized").Select
Sheets("BOMSized").Move Before:=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?
 
D

Dave Peterson

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 said:
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 Before:=Sheets(1)
Sheets("PropertySets").Select
Sheets("PropertySets").Move Before:=Sheets(2)
Sheets("BOMSized").Select
Sheets("BOMSized").Move Before:=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 said:
Sorry, Dim it as Range
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with Macro 7
Can Vba workout if a selection is part of a group? 0
Run-time error 1004 paste method of worksheet class failed 3
macro help for changing data (cell location) in edit find box 1
Excel Macro to use cells as a autofilter 0
Macro to fill to next field with data 4
Why does this macro crash? 6
Macro Debug Error 1

Top