EXCEL FEED: Re: [ExcelVBA] Dynamic Named Range VBA (naming of range) not working

Selasa, 17 Juni 2014

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: Green <1z@compuserve.com>

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

----------------------------------
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

----------------------------------

Visit Your Group