Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error, Message, Exit
Hi, In the following, I need to add:
MsgBox "No #N/A cells found. Nothing to copy. Finished." --and when clicking OK, close the workbook without saving. before exiting in this statement: If Not IsError(.Cells(lr, "r")) Then Exit Sub ------------------------------------------------------------------- ' copy value in columns CDE #N\A to Employees.xlsx Set swb = ActiveWorkbook 'IF open Set dwb = Workbooks("Employees.xlsx") 'If open dlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row + 1 'MsgBox dlr With swb.Sheets(1) lr = .Cells(Rows.Count, "a").End(xlUp).Row If Not IsError(.Cells(lr, "r")) Then Exit Sub ..Range("a1:r" & lr).AutoFilter field:=18, Criteria1:="#N/A" ..Range("e2:e" & lr).SpecialCells(xlCellTypeVisible).Copy dwb.Sheets(1).Range("a" & dlr) ..Range("c2:d" & lr).SpecialCells(xlCellTypeVisible).Copy dwb.Sheets(1).Range("b" & dlr) ..Range("a1:r" & lr).AutoFilter nlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row 'MsgBox nlr 'print range dwb.Activate Sheets(1).PageSetup.PrintArea = Range(Cells(dlr, "a"), Cells(nlr, "c")).Address ActiveWindow.SelectedSheets.PrintOut End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
On Error, Message, Exit
On Mar 20, 10:21*am, Helmut wrote:
Hi, In the following, I need to add: MsgBox "No #N/A cells found. Nothing to copy. Finished." --and when clicking OK, close the workbook without saving. before exiting in this statement: *If Not IsError(.Cells(lr, "r")) Then Exit Sub ------------------------------------------------------------------- ' copy value in columns CDE #N\A to Employees.xlsx Set swb = ActiveWorkbook 'IF open Set dwb = Workbooks("Employees.xlsx") 'If open dlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row + 1 'MsgBox dlr With swb.Sheets(1) lr = .Cells(Rows.Count, "a").End(xlUp).Row *If Not IsError(.Cells(lr, "r")) Then Exit Sub .Range("a1:r" & lr).AutoFilter field:=18, Criteria1:="#N/A" .Range("e2:e" & lr).SpecialCells(xlCellTypeVisible).Copy dwb.Sheets(1).Range("a" & dlr) .Range("c2:d" & lr).SpecialCells(xlCellTypeVisible).Copy dwb.Sheets(1).Range("b" & dlr) .Range("a1:r" & lr).AutoFilter nlr = dwb.Sheets(1).Cells(Rows.Count, "a").End(xlUp).Row 'MsgBox nlr 'print range dwb.Activate Sheets(1).PageSetup.PrintArea = Range(Cells(dlr, "a"), Cells(nlr, "c")).Address ActiveWindow.SelectedSheets.PrintOut End With This should be what you're looking for: If Not IsError(.Cells(lr, "r")) Then Call MsgBox("No #N/A cells found. Nothing to copy.", vbokonly, "Finished") Exit Sub End If Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace VBA run-time error message with custom message | Excel Programming | |||
on error exit sub | Excel Programming | |||
Exit a macro from an error condition | Excel Programming | |||
save on exit message box | Excel Programming | |||
save on exit message box | Excel Programming |