Error handler Only Rings Once - Excel 2007 error 1004 1

[AArt]

I'm creating and renaming sheets based on the customer name in a cell (truncated). Some of the customer names have colons, which are illegal, causing error 1004.
The error code:
On Error GoTo fixname

Here's the rename code:
TryNow: Sheets(cursheet).Select
Sheets(cursheet).Name = Cname
Sheets(Mainsheet).Select

Here's the error handler:

fixname:
If Err.Number = 1004 Then
Cname = InputBox("Fix Customer Name", "Naming Error", Cname)
GoTo TryNow
Else
MsgBox Err.Number
End If

The first time it comes across "EB: MERR". I get the input box, fix it and it works great.
The second time it encounters "RH: BROA" and the error handler doesn't handle it. I get the fatal error DB instead, saying "Run Time Error 1004".

What do you think of that? ?

 

[SkipVought]

Why not routinely eliminate the COLON and avoid the error.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

If you are continuing your code after errors come up, remember to Err.Clear after assessing the Error.

If that doesn't work, you'll have to post your code so we can see what's going on.

 

[HughLerwill]

You are returning from the error handler using a Goto rather than a Resume; so the error remains in the Error Object. Check the value of Err as you step through the code.

 

[AArt]

I wrote a function to parse the string, so yeah, that's one way. The error handler just seemed like it should work.

Gruuu - I tried err.clear, certain you had solved the problem. But it didn't seem to help.

Here's the code. Don't laugh, I'm self taught.

Sub BigTest()
'
' BigTest Macro
'
Dim x, numrow, Mainsheet, cursheet, mainrow, curRow, Cnum1, Cnum2 As Long
Dim Cname As String

On Error GoTo fixname
numrow = ActiveSheet.UsedRange.Rows.Count
Mainsheet = ActiveSheet.Index
Cnum2 = 1


For x = 2 To numrow
Cells(x, 5).Activate 'get Cus #
Cnum1 = ActiveCell.Value
Cells(x, 10).Activate 'cust name
Cname = Left(ActiveCell.Value, 10)
'Cname = Ffixname(Cname) this is the function that
'removes colons

If Cnum1 <> Cnum2 Then 'cust different than last time
Cnum2 = Cnum1
Rows(1).Select
Selection.Copy ' make sheet and copy header

Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste

cursheet = ActiveSheet.Index
curRow = 2
TryNow: Sheets(cursheet).Select
Sheets(cursheet).name = Cname
Sheets(Mainsheet).Select

Rows(x).Select ' copy first row of data

Selection.Copy
Sheets(cursheet).Select
Cells(curRow, 1).Select
ActiveSheet.Paste

Else ' cust same as last time

Rows(x).Select ' copy next row of data
Selection.Copy


Sheets(cursheet).Select 'go to other sheet
curRow = curRow + 1 ' and paste
Cells(curRow, 1).Select
ActiveSheet.Paste
End If
Sheets(Mainsheet).Select
Next x
Exit Sub
fixname:
If Err.Number = 1004 Then
Cname = InputBox("Fix Customer Name", "Naming Error", Cname)
Err.Clear
GoTo TryNow
Else
MsgBox Err.Number
Err.Clear
End If


End Sub

 

[AArt]

Resume did the trick!

Thank you all.

 

[HughLerwill]

Good.I suggest you highlight Resume and press F1 for the full story from VBA help; if you have not already done so.

 

[HughLerwill]

Error 1004 can be fired for a number of reasons under VBA and currently your whole routine will go to the error handler for any one of them; that could be confusing for you if an Error 1004 happened anywhere else. I suggest you only do On Error Goto just before the code causing the error you want to trap and switch it off with an On Error Goto 0 right after.

 

[AArt]

I misread that the first time. Consider me duly educated. Thanks again.