Hello all,

Using Excel 2002 (10.6501.6626) SP 3 on Windows XP Pro (5.1.2600) SP2

I do a lot of copying from SQL Server Query Analyzer/pasting to Excel. After pasting, my data is full of "NULL's" that I need to
replace. I've already come up with some code that will replace all of the NULL's giving you the option to paste data first or
replace NULL's on existing data. This code works just fine. I let this code run for 10 seconds and then do a [Ctrl] + [Break] or
else problems start to arise (the data I run this on can be as large as 40,000 rows X 30 columns, and then some).

I'm trying to adapt this code so that when I do a [Ctrl] + [Break], I will get some feed back. Right now, if I do the [Ctrl] +
[Break], my error handler does not work correctly (code will be posted at the bottom). I get the following message:
http://home.att.net/~ctbarbarin/file...ress_error.jpg. If I put a line of code in the sub routine that will raise an error, the
error handler works just fine (I was using If mrngCurrRange = "Nothing" Then<< when mrngCurrRange is declared as a range
variable--this Error-causing code is the 2nd line of code in this macro after variable declarations and "On Error GoTo...").

How can I get this error handler to work correctly on a [Ctrl] + [Break]? It works correctly on a legitimate error!
--
Thanks for any help anyone can provide,

Conan Kelly







Sub PasteReplaceNulls()
Dim pmbrResponse As VbMsgBoxResult
Dim pmbrAnswer As VbMsgBoxResult
Dim pstrPasteCell As String
Dim ptimTimer As Date
Dim mrngCurrRange As Range

On Error GoTo PasteReplaceNulls_Err

Application.EnableCancelKey = xlErrorHandler


' If mrngCurrRange = "Nothing" Then
' do stuff...
' End If

pstrPasteCell = "A2"

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", "", xlPart, xlByRows, False, False, False

Exit Sub

PasteReplaceNulls_Err:
MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf & Selection.Address
Exit Sub

End Sub