Still trying to return the progress

[Conan Kelly]

Hello all,

HOW DO I GET THIS CODE TO RUN FROM WITHIN THE ERROR HANDLER?

For background on what I'm trying to do and why, scroll all the way down and read my previous posts.

I came up with this code:

Sub DisplayProgress()
Dim plngFirstRow As Long
Dim plngFirstCol As Long
Dim plngCurrRow As Long
Dim plngCurrCol As Long
Dim plngCurrCell As Long
Dim plngTotalCells As Long

plngTotalCells = Selection.Cells.Count
plngFirstRow = Selection.Cells(1).Row
plngFirstCol = Selection.Cells(1).Column
plngCurrRow = Selection.Find("NULL", ActiveCell, xlFormulas).Row
plngCurrCol = Selection.Find("NULL", ActiveCell, xlFormulas).Column

plngCurrCell = ((plngCurrRow - plngFirstRow) * Selection.Columns.Count) + (plngCurrCol - plngFirstCol) + 1
MsgBox "Progress: " & FormatPercent(plngCurrCell / plngTotalCells, 2, vbTrue)

End Sub



Now after interrupting code execution ([Ctrl] + [Pause/Break]) on another sub routine (the PasteReplaceNulls sub in the orig. posts
below), if I run this one all by itself, it appears that it runs just fine and reports the information I'm looking for. BUT if I
try to call this sub routine from an error handler in the other sub like so:

PasteReplaceNulls_Err:
DisplayProgress
Exit Sub

End Sub


OR if I try to put these statements in the error handler like so:

PasteReplaceNulls_Err:
plngTotalCells = Selection.Cells.Count
plngFirstRow = Selection.Cells(1).Row
plngFirstCol = Selection.Cells(1).Column
plngCurrRow = Selection.Find("NULL", ActiveCell, xlFormulas).Row
plngCurrCol = Selection.Find("NULL", ActiveCell, xlFormulas).Column

plngCurrCell = ((plngCurrRow - plngFirstRow) * Selection.Columns.Count) + (plngCurrCol - plngFirstCol) + 1
MsgBox "Progress: " & FormatPercent(plngCurrCell / plngTotalCells, 2, vbTrue)
Exit Sub

End Sub

(Variable names were declared at the top of the sub)

I get the following error message:

http://home.att.net/~ctbarbarin/files/progress_error.jpg


HOW DO I GET THIS CODE TO RUN FROM WITHIN THE ERROR HANDLER?

Thanks for any help anyone can provide,

Conan Kelly




---- Original Posts Below ----

Hello all,

First, below is my first post closely related to this question. Jim Rech already answered this question for me. Thanks Jim.

I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm
working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] +
[Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes.

The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue?
End? Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my
progress through the selected range.

Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way?

What I would like to do is display a message box displaying my progress through the selection.

I can easily figure out the total number of rows/cell in the selection, but is there any way to figure out what row/cell the code
left off on when it was canceled?



Now for the code (I don't like using line continuation characters in my code--hopefully this is readable):

Sub PasteReplaceNulls()
On Error GoTo PasteReplaceNulls_Err

Application.EnableCancelKey = xlErrorHandler

Dim pmbrResponse As VbMsgBoxResult
Dim pmbrAnswer As VbMsgBoxResult
Dim pstrPasteCell As String

pstrPasteCell = "A2"

If Application.Calculation = xlCalculationAutomatic Then ToggleAutoCalc

pmbrResponse = MsgBox("Do you want to Paste?", vbYesNoCancel + vbDefaultButton2 + vbQuestion, "Paste?")

If pmbrResponse = vbCancel Then
Exit Sub
ElseIf pmbrResponse = vbYes Then
pmbrAnswer = MsgBox("Data will be pasted starting in cell A2" & vbCrLf & vbCrLf & "Is this correct?", vbYesNoCancel +
vbQuestion, "Paste in A2")
If pmbrAnswer = vbCancel Then
Exit Sub
ElseIf pmbrAnswer = vbNo Then
pstrPasteCell = InputBox("Please enter the cell you would like pasting to begin:", "Paste Cell", pstrPasteCell)
End If
Range(pstrPasteCell).Select
ActiveSheet.Paste
End If

' Selection.Replace "NULL", Null, xlPart, xlByRows, False, False, False
Selection.Replace "NULL", "", xlPart, xlByRows, False, False, False

If Application.Calculation = xlCalculationManual Then ToggleAutoCalc

PasteReplaceNulls_Exit:
Exit Sub

PasteReplaceNulls_Err:
MsgBox "Need to enter progress info here."
Exit Sub

End Sub



PS. If any one has a more efficient way of doing this, I'm all ears.

Thanks for any help anyone can provide,

Conan Kelly