Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional formatting crashes macro
How can conditional formatting crash this macro?
It will fail at "Calculate" when some conditional formatting is used, unfortunately I have not found the exact formatting that causes the crash. I can circumvent the crash if I clear all conditionals prior to running the macro. However it has adverse affects as well, like failing to copy worksheets to the proper location of each other. I can supply a sample workbook with all conditional formatting and releavant macros to any who wish to help. The macro is a printing function. IPI = In-Process Inspection (report). '====== Sub PrintIPI() ' ' PrintIPI Macro ' Macro date 03/08/07 by L Roach 'This macro prints and sorts the correct number of copies of the IPI based on 'the number of parts on the job. Dim NumCopies As Long, WhereToPaste As Long, PasteCopy As Long, WhatOp As Long Dim GetName() As String, LastPage As String, OpName As String Dim objSht As Worksheet On Error GoTo SORRY Calculate 'If there are more than 50 parts add the required sheets If Sheets("Master Sheet").Range("B21").Value 50 Then NumCopies = Round((Sheets("Master Sheet").Range("B21").Value / 50) + 0.5) - 1 For Each objSht In ActiveWorkbook.Worksheets For PasteCopy = 1 To NumCopies 'Name the pages that have been added for extra parts If InStr(UCase(objSht.Name), "OP") < 0 Then GetName = Split(Trim(Replace((Replace(UCase(objSht.Name), "OP", "")), "-", " ")), " ") OpName = GetName(0) For WhatOp = objSht.Index To Sheets.Count If InStr(Sheets(WhatOp).Name, OpName) < 1 Then Exit For If InStr(Sheets(WhatOp).Name, LastPage & " (" & PasteCopy + 1 & ")") 0 Then WhatOp = WhatOp + 1 Exit For End If Next WhatOp 'Fix the wording on the in process sheets Sheets(objSht.Name).Copy after:=Sheets(WhatOp - 1) Cells.Replace What:="Oper F.A.", Replacement:="I.P.", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Insp F.A.", Replacement:="I.P.", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Cells.Replace What:="Co Op F.A.", Replacement:="I.P.", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False End If Next PasteCopy LastPage = objSht.Name Next objSht End If Application.CutCopyMode = False ActiveWorkbook.PrintOut ActiveWindow.Close SaveChanges:=False Exit Sub SORRY: MsgBox "Failed to print IPI", , "Sorry... Had an error." End Sub '====== -- Regards VBA.Newb.Confused XP Pro Office 2007 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Macro? | Excel Programming | |||
Conditional Formatting in a Macro | Excel Discussion (Misc queries) | |||
Formatting a cell crashes Excel | Setting up and Configuration of Excel | |||
Conditional Formatting Macro | Excel Programming | |||
macro for conditional formatting | Excel Programming |