Here is attachment
Here is attachment
I looked back at your discussionm on MAX and Min, and admit IU don't really understand your point. Can you explain it again?Originally Posted by rberke
On the VLOOKUP point you make, you could use dynamic indexes
= VLOOKUP(M1,A1:B200,COLUMN(B1)-COLUMN(A1)+1,FALSE)
then no need to use the (clumsier) MATCH...INDEX alternative.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I have an objection to make. There is no poll option for "I have no idea what a named range is and who its cousin VLookUp is" however I have put myself on the subscription for MrExcels free e-book so that I can teach myself a little more.
Nothing annoys a non-conformist more than other non-conformists who refuse to conform to the rules of non-conformity.
Confused is my normal state of mind
Originally Posted by sandam
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Amen brother, Amen.Originally Posted by Aaron Blood
Hello , good discussion,
i love name, but I'm looking to retreive the cells address of a chart with VBA. Imagine the data source in DA220C230 and the chart is in A1 how could we manage to find this address with VBA (like we do by hand with the toolbar chart).
Any idees??
help will be welcome. )
Is it true that calculation of formulas working on named ranges is faster?
No significant difference that I can measure.Originally Posted by ALe
Hi Zack,
Being more or less considered the RangeName wizz, I cannot refrain from posting to this thread!!!
Of course the example you give is confusing because it (IMO) violates good VBA programming practice: qualifying the object you're talking to.Originally Posted by firefytr
Using Range("test") is simply begging for trouble, whether you'd be using a range name or a cell address (so Range("A1") is equally bad), UNLESS you're deliberate about wanting to address the active worksheet (in which case you'll need a check to make sure a worksheet IS active!).
I have a tool that fixes workbooks like that automatically.Originally Posted by johnske
Hi Kevin,
Off topic:Originally Posted by Zorvek
I'd advise to stop using variable names that are identical to Objects, properties or methods. It'll get you into trouble.
Hi,
Sorry about the duplicate post, my Internet connection is on and off today.
My 2 cts now.
Range names are a great tool. If put to use with prudence. Aaron has phrased it well: too little people are aware of the pitfalls.
I use range names extensively when it comes to communicating with spreadsheets through VBA. On worksheets, I use them sparsely, mainly when I need dynamic ranges and when I refer to single cells that contain key parameters for an entire model.
I've written an article on my site about Range names: http://www.jkp-ads.com/Articles/ExcelNames.htm, which addresses the pitfalls too little I guess (Aaron did motivate me to add some about the problems).
I guess Charles Williams and my Name Manager is a must have utility for anyone using defined names more than once a year :-).
Not sure if you all are aware, but there is a beta of version 4 of the Name Manager available, which enables you to rename names (includes renaming in almost any object on your workbook and in your VBA code!). I have also added a feature which will show you all locations where a name is in use. I think if one makes extensive use of this tool (especially the last new option I mentioned can be most enlightening), a lot of the problems with defined names may be avoided or at least they become much more obvious.
Oh, I forgot to add a link to the beta:
www.jkp-ads.com/officemarketplacenm-en.asp
Jan,
I?ve been using variable names identical to object names for years and have yet to get into trouble. For the most part it's a myth that it does. Objects exposed in VB/VBA are almost always members of other objects and hence have to be qualified. If the name DOES conflict with an object name defined in the global name space then VB/VBA chooses the local name over the global one without error. Can it ever get confusing? Sure. But, at the same time, so can liberal use of objects defined in the global name space that imply ownership ? that?s where people get into the most trouble. Here are some rather obtuse examples illustrating just how predictable variable references really are. Both routines produce predictable results without fail:
[vba]
Public Sub Test1()
Dim ThisWorkbook As Variant
ThisWorkbook = 23
MsgBox ThisWorkbook
MsgBox Application.ThisWorkbook.Name
End Sub
Public Sub Test2()
Dim Application As Variant
Application = 23
MsgBox Application
MsgBox ThisWorkbook.Application.Name
End Sub
[/vba]
Kevin
Kevin,
Although I guess you're right, I still disagree. I find using object/method/property names as variable names very confusing.
But hey, if you're happy with it, ignore me! I know I would :-)
Just when this thread appeared to be dormant ......
Great to see you here Jan Karel, I've incoporated a copy of your Name Manager into a corporate addin that we use for auditing and debugging spreadsheets
While I am still firmly in the "no" camp I do want to pose a name range problem that has been bugging me. I posted this question some time ago at Experts Exchange but had no luck identifying a workaround
"I've built a workbook template with indentical input sheets and I'm using hyperlinks to local range names for major sections (Revenue in A100, Capex in A364 and so on)
Capex ='Alt 2. 10|U|T|M - Mid Mine Plan'!$A$364
etc
All is going well until I rename a sheet, and voila, the hyperlink is now invalid. Has anyone seen this before, and more importantly is there anyway around it? Global range names wth hyperlinks work fine with a sheet name change, Excel just remaps like it normally does
Its an internal work example to be published on our intranet so code is out of the question"
Cheers
Dave
Hi dave,
Could you perhaps post a set of steps to repro the problem?
In this question I discovered by accident that there is an "automatic" range name created using the column heading (Excel 2003). I can't replicate this in a new spreadsheet. Is there a setting to do this?
http://www.vbaexpress.com/forum/showthread.php?t=8417&goto=newpost
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Sorry for the reply lag
Attached is a small example, there is a local name 'Daves Sheet'!Test at A1, a hyperlink linked to this range name in A4.
If the sheet name is changed from 'Daves Sheet' the hyperlink does not update the local range name change
Cheers
Dave
Yup, indeed the rename isn't working. It does work as expected with a globally defined name, but that doesn't solve your problem I guess.
I guess your workaround will need to be to either not use local named ranges in hyperlinks, or to rebuild the hyperlinks. Sorry, not much help I guess.