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/file...ress_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]?

--
Thanks for any help anyone can provide,

Conan Kelly

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/file...ress_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]?

--
Thanks for any help anyone can provide,

Conan Kelly

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




"Bob Phillips" wrote in message ...
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/file...ress_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]?

--
Thanks for any help anyone can provide,

Conan Kelly

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




"Bob Phillips" wrote in message
...
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/file...ress_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]?

--
Thanks for any help anyone can provide,

Conan Kelly

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


"Bob Phillips" wrote in message ...
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




"Bob Phillips" wrote in message
...
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/file...ress_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]?

--
Thanks for any help anyone can provide,

Conan Kelly

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/file...ress_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(mstrShe etName).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" wrote in message ...
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" <CTBarbarin at msn dot com wrote in message
...
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


"Bob Phillips" wrote in message
...
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




"Bob Phillips" wrote in message
...
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/file...ress_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]?

--
Thanks for any help anyone can provide,

Conan Kelly

It's bed time here now, I'll take another look tomorrow.

--

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,

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/file...ress_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(mstrShe etName).Range(mstrCurrRang
e)
' 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" wrote in message
...
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" <CTBarbarin at msn dot com wrote in message
...
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


"Bob Phillips" wrote in message
...
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




"Bob Phillips" wrote in message
...
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/file...ress_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]?

--
Thanks for any help anyone can provide,

Conan Kelly