LinkBack URL
About LinkBacks
Hi,
I'm trying to find the column number of a named range (which has been defined previously in my code).
Am I missing something obvious here ?
Please Login or Register to view this content.
Thanks,
amphi
Hi,
I'm trying to find the column number of a named range (which has been defined previously in my code).
Am I missing something obvious here ?Please Login or Register to view this content.
Thanks,
amphi
Maybe:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks TMS. Not sure what your suggestion should do/check ?
Depending with what I call the function, the column returned could be 1 (first time calling the function) then could be any number, 5,9,15 etc.
I'm not really sure what you are trying to achieve.
The code examples below show you how to get the address details of a Named Range
Please Login or Register to view this content.
The question mark is short-hand, in the Immediate Window, for Debug.Print
Regards, TMS
Thanks I'll try work through it using your tips !
You're welcome. Thanks for the rep.
One of the reasons this isn't working on my workbook is that I have an issue with _xlfn.iferror = #NAME as a hidden name stored in my workbook. The reason is that I've imported a 2007 sheet and added an IFERROR formula. I've tried deleting the name
but, as it's a hidden name, the loop doesn't access it. However when I loop through the names and debug.print, it still appears.Please Login or Register to view this content.
Source of the issue : http://www.pcreview.co.uk/threads/_x...nager.3434850/
I'm intrigued. Can you post a sample workbook with the Named Range(s)?
It looks as though you've consulted the best ... but I'd still like to have a go
I put a formula into a cell to generate an IFERROR condition.Formula:
Please Login or Register to view this content.
I then used this code to list the Named Ranges. In this instance, there was only one ... the one generated by using IFERROR. Seems like you can't delete it but you only get the one, regardless of how many times you use IFERROR.
Please Login or Register to view this content.
Regards, TMS
And, I should have made the point ... it's not because it's a hidden name that you can't delete it, it's because it's system generated. Even if you remove the formula generating and trapping the error, it is still present.
Debug Output:
Please Login or Register to view this content.
Another little example:
Please Login or Register to view this content.
Regards, TMS
Appreciate the thoroughness ! Yeah I quickly realized that I wouldn't be able to delete the name because as you rightly mentioned, it was system generated. Luckily enough, I simply trapped the name with an IF statement and added the name as string which seemed to do the trick :
This is probably unrelated but when I call the function with this :Please Login or Register to view this content.
it seems that the function looses it's value.Please Login or Register to view this content.
You're welcome.
With regard to the last question, I haven't got anything to test the function, or its call, with. Sorry, I'm not going to try to second guess what data is there in order to test it.
Regards, TMS
Sure no worries. At least the _xlfn.IFERROR issue is solved
Is the function actually returning a value?
If posting code please use code tags, see here.
This is what I'm getting :
Call :
Function:Please Login or Register to view this content.
Immediate window:Please Login or Register to view this content.
Please Login or Register to view this content.
Can you upload a sample workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
Here you go.
Replace the 2 Exit Fors with Exit Function, the second Exit For is never executed and only the inner loop is exited.
By the way, why is reportCol looping down to 0? If 0 is ever reached you'll get an error with reportSheet.Cells(3, reportCol).
PS Even if the 2nd Exit For was executed I don't think it would apply to the outer loop.
Last edited by Norie; 06-02-2015 at 09:50 AM.
All those debug.prints did show me the loop wasn't exiting but i never knew about Exit Function..
Yes I started getting an error in the sample work book I realized it was a mistake.
Thanks Norie !
I agree with Norie's solution.
Can you clarify the purpose of this routine? Why do you pass it a counter? Are you just trying to limit it? If I add a second Named Range, in say, column 3, the routine will exit with a result of 3 with no mention or reference to the Named Range in column 1.
Is it possible that you might have a Named Range outside the bounds you set?
Regards, TMS
Yes the counter is to limit the routine and yes, I also set Named Ranges outside the function & the call. Bigger routine is this: I'm importing sheets, adding summaries from them on a "Report" sheet. On the "Report" sheet, Range("A3:A33") are labels. First sheet I import would occupy Range("B3:F33") then Range("G3:G33") would be a Named Range and would sum Range("B3:F33"). Range("H3:L33") would be occupied by the summary of the next sheet. Range("M3:M33") would be named and then sum Range("H3:L33").
Last edited by amphinomos; 06-03-2015 at 03:32 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks