Bob,
Thank you so much for looking into this.
I never actually tested the code that I posted. The code I posted was chopped down next to nothing, BUT it still should NOT have
been functioning properly. It looks like with the way your code is set up that it should not work on my computer, BUT IT IS WORKING
JUST FINE. YET THE CODE THIS IS BASED OFF OF IS STILL NOT WORKING CORRECTLY.
Let me explain what I'm trying to do before I post the code: I'm copying/pasting a lot of data from SQL Server into Excel. This
data can be as big as 40,000 rows X 30 columns with "NULL's" peppered throughout. I came up with some code that will look through
this data and replace all NULL's with "". That part works fine. But on huge amounts of data (anything over 10,000 rows), I can't
let it run straight through. I have to break into it several times before it can make it through the whole selection. What I want
to do is when I break into it, I want to display a custom message box that will display how far I have made it through the whole
selection up to this point (a Progress message, if you will--this is just a point-in-time Progress message--not a dynamic,
continually updating Progress bar). I only want this progress message to show up when I have to break into the code ([Ctrl] +
[Break]). I came up with a separate sub routine that works just fine when run by itself, but for the life of me, I can not get this
to work from the error handler when doing a [Ctrl] + [Break]. I was trying to test this by having the single statement in the error
handler: "MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf & Selection.Address". If I can get this single statement
to work, then there should be no problem getting the rest to work.
This single statement in the error handler will work just fine when it comes across a legitimate error (that is why I had that "If
mrngCurrRange = "Nothing" Then" statement in there--to trigger a legitimate error). BUT every single time I get to the error
handler by means of [Ctrl] + [Break], I get this error message:
http://home.att.net/~ctbarbarin/files/progress_error.jpg.
The code that you came up with should be acting the exact same way, but it is not, it is actually working correctly. I just don't
get it. I can not tell what the problem is.
Once again, thank you so much for all of your help. I will post all of non-edited code below and you can test it to see if it will
work for you. There are a lot of commented lines of code in there that are just different methods of trying to accomplish the same
thing. Hopefully it is not too confusing and you can make sense of it all. The code, as it stands now, will return the error
message linked to above when I break into the code ([Ctrl] + [Break]) on my computer. Please let me know if you have different
results. If we can get the single msgbox statement in the error handler working correctly, then there should be no problem getting
the rest of it working.
Please let me know if you have any questions.
Thanks again,
Conan
Sub PasteReplaceNulls()
Dim pmbrResponse As VbMsgBoxResult
Dim pmbrAnswer As VbMsgBoxResult
Dim pstrPasteCell As String
' Dim plngFirstRow As Long
' Dim plngFirstCol As Long
' Dim plngCurrRow As Long
' Dim plngCurrCol As Long
' Dim plngCurrCell As Long
' Dim plngTotalCells As Long
On Error GoTo PasteReplaceNulls_Err
Application.EnableCancelKey = xlErrorHandler
' If mrngCurrRange = Nothing Then
' Set mrngCurrRange = Application.ActiveWorkbook.ActiveSheet.Selection
' mlngTotalCells = mrngCurrRange.Cells.Count
' mlngFirstRow = mrngCurrRange.Cells(1).Row
' mlngFirstCol = mrngCurrRange.Cells(1).Column
' mlngNbrOfCols = mrngCurrRange.Columns
' mstrFileName = Application.ActiveWorkbook.Name
' mstrSheetName = Application.ActiveWorkbook.ActiveSheet.Name
' mstrCurrRange = Selection.Address
' End If
pstrPasteCell = "A2"
' If Application.Calculation = xlCalculationAutomatic Then ToggleAutoCalc '***'ToggleAutoCalc' is a custom sub
procedure used to turn off the Auto Calculation***
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
Set mrngCurrRange = Nothing
mlngTotalCells = 0
mlngFirstRow = 0
mlngFirstCol = 0
mlngNbrOfCols = 0
Exit Sub
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)
' DisplayProgress
' Set mrngCurrRange = Application.Workbooks(mstrFileName).Sheets(mstrSheetName).Range(mstrCurrRange)
' With mrngCurrRange.Find("NULL", ActiveCell, xlFormulas)
' mlngCurrRow = .Row
' mlngCurrCol = .Column
' End With
'
' mlngCurrCell = ((mlngCurrRow - mlngFirstRow) * mlngNbrOfCols) + (mlngCurrCol - mlngFirstCol) + 1
' MsgBox "Progress: " & FormatPercent(mlngCurrCell / mlngTotalCells, 2, vbTrue)
MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf & Selection.Address
' MsgBox "Hoochie Mamma"
Exit Sub
End Sub
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
With Selection.Find("NULL", ActiveCell, xlFormulas)
plngCurrRow = .Row
plngCurrCol = .Column
End With
' With mrngCurrRange.Find("NULL", ActiveCell, xlFormulas)
' mlngCurrRow = .Row
' mlngCurrCol = .Column
' End With
plngCurrCell = ((plngCurrRow - plngFirstRow) * Selection.Columns.Count) + (plngCurrCol - plngFirstCol) + 1
MsgBox "Progress: " & FormatPercent(plngCurrCell / plngTotalCells, 2, vbTrue)
' mlngCurrCell = ((mlngCurrRow - mlngFirstRow) * mlngNbrOfCols) + (mlngCurrCol - mlngFirstCol) + 1
' MsgBox "Progress: " & FormatPercent(mlngCurrCell / mlngTotalCells, 2, vbTrue)
End Sub
Bob Phillips said:
Conan,
I adapted the code slightly like this
Sub PasteReplaceNulls()
Dim i As Long
On Error GoTo PasteReplaceNulls_Err
Application.EnableCancelKey = xlErrorHandler
For i = 1 To 1000000000
Next i
PasteReplaceNulls_Err:
MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf &
Selection.Address
Exit Sub
End Sub
and it broke into the error hanlder as expected. Does this not work for you,
or am I still not quite getting the problem?
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
Conan Kelly said:
Bob,
I'm sorry about that. I should have mentioned that it is declared in the
General Declarations of the module as a Range Variable.
If you are testing this on your computer, please declare it as a Range
variable and it should throw an error then.
Thanks again for all of your help. Please excuse me if I seem a little
short and rude. This problem is really driving me crazy.
I've been working on this for 3 or 4 days now. And since this is just for
me personally, I've been spending way too much time on it
and haven't been getting alot of work done. It also seems like not very
many people here know how to resolve it; I haven't had any
replies to my previous posts. I really want to figure this out so I can concentrate on work.
Once again, thank you for all of your help. It is greatly appreciated.
Conan
That first example does not throw an error as the object is not declared, so
it is interpreted as a string and compared against "Nothing" and succeeds.
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Conan Kelly" <CTBarbarin at msn dot com> wrote in message
bob,
Thanks for the feed back, but please re-read the post.
I already knew that this syntax is incorrect (I stated that in the
comments). I intentionally had that in there to trigger a
legitimate error. That is not where the problem lies. When I have this
incorrect syntax in the code, the error handler works
correctly, BUT when I use [Ctrl] + [Break], the error handler does not
work correctly.
Any more thoughts on the matter will be greatly appreciated,
Conan
If mrngCurrRange Is Nothing Then
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"Conan Kelly" <CTBarbarin at msn dot com> wrote in message
Hello all,
Using Excel 2002 (10.6501.6626) SP3
AAAAAAAAHHHHHHHHHHHHHHHHHHH!!!!!!!!!!!!!!!!!!!! THIS IS DRIVING ME
NUTS!!!!!!!!!!!!!!!!!!!!!!!!
I've been working on this for several days now and I think that I have
finally narrowed it down to this:
How can I get this to work correctly when I interrupt execution with
[Ctrl] + [Break]?
With the following code, the error handler works correctly:
Sub PasteReplaceNulls()
On Error GoTo PasteReplaceNulls_Err
Application.EnableCancelKey = xlErrorHandler
If mrngCurrRange = "Nothing" Then
' I KNOW THIS SYNTAX IS INCORRECT. I'VE ALREADY FOUND OUT
THE
CORRECT SYNTAX. This is just here to trigger a
legitimate error.
End If
'Statements that will cause the macro to run for a long time,
hence the need for "Application.EnableCancelKey =
xlErrorHandler" above
PasteReplaceNulls_Err:
MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf
&
Selection.Address
Exit Sub
End Sub
BUT with the following code AND INTERRUPTING EXECUTION WITH [Ctrl] +
[Break], the error handler does not work correctly. Instead, I
get this error message:
http://home.att.net/~ctbarbarin/files/progress_error.jpg
Sub PasteReplaceNulls()
On Error GoTo PasteReplaceNulls_Err
Application.EnableCancelKey = xlErrorHandler
'Statements that will cause the macro to run for a long time,
hence the need for "Application.EnableCancelKey =
xlErrorHandler" above
PasteReplaceNulls_Err:
MsgBox ActiveWorkbook.Name & vbCrLf & ActiveSheet.Name & vbCrLf
&
Selection.Address
Exit Sub
End Sub
How can I get this to work correctly when I interrupt execution with
[Ctrl] + [Break]?
