Shouldn’t do, exiting the sub should reset the error handfler.
Really, you shouldn’t use a generic OnError Resume Next like that.
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: 17 June 2014 12:29
To: ExcelVBA@yahoogroups.com
Subject: Re: [ExcelVBA] Dynamic Named Range VBA (naming of range) not working
Hi,
Sorry about the blank post... thick finger syndrome!
Coming to this late.
One problem I have with this is that the On Error will stick if the code drops out of the proc on the line...
If n.Name = sRangeName Then Exit Sub
Lisa
Sent: Sun, Jun 15, 2014 9:07 pm
Subject: RE: [ExcelVBA] Dynamic Named Range VBA (naming of range) not working
How about this?
Sub AddDynamicRangeHorizontal()
On Error Resume Next
Dim sRangeName As String
Dim n As Name
If ActiveWorkbook Is Nothing Then Exit Sub
sRangeName = InputBox("Enter a range name, then push OK. ", _
"Add Horizontal Dynamic Range")
If sRangeName = "" Then Exit Sub
sRangeName = Replace(sRangeName, " ", "_")
With ActiveSheet
ActiveWorkbook.Names.Add Name:=sRangeName, _
RefersTo:="=OFFSET('" & .Name & "'!" & _
ActiveCell.Address & ",,," & _
"COUNTA('" & .Name & "'!" & Columns(ActiveCell.Column).Address & ")," & _
"COUNTA('" & .Name & "'!" & Rows(ActiveCell.Row).Address & "))"
End With
For Each n In ActiveWorkbook.Names
If n.Name = sRangeName Then Exit Sub
Next n
MsgBox Err.Description, , "Invalid Name"
On Error GoTo 0
End Sub
Posted by: "Bob Phillips" <bob.phillips@dsl.pipex.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (5) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar