Iteration

In same cell or can be in 4 columns (or any other when you type no of switches)

Hi,

zbor wants to know if you want the result all in one cell or in different columns.

have a look at the attached.

hth

You'll struggle to come up with something truly dynamic (and elegant) in one cell without use of VBA (UDF) and/or 3rd party add-in like morefunc.xll (MCONCAT function)

In UDF terms, the below stored in a Module

Please Login or Register  to view this content.

in a Macro Enabled file can be used along lines of:

Please Login or Register  to view this content.

where [X] denotes the placeholder position

If you wanted to use an alternative delimiter (to comma) you can explicitly pass the otherwise optional 3rd argument, eg to use :

Please Login or Register  to view this content.

i'd go for a table approach as its easy enough to construct one for any interface type see sheet 2(once done you can simply copy paste back special values only to eliminate the formulas)

then use a simple vlook up

@ DonkeyOte

That worked beautifully!! Thank you so much, really appreciated! I had another scenerio I wanted to run by you...

Suppose I want the first and last elements in the string to be different from what's in between such as the following:

interface range GigabitEthernet1/0/51 - 52,GigabitEthernet2/0/49 - 52,GigabitEthernet3/0/49 - 52,GigabitEthernet4/0/51 - 52

Thanks again,

Anne

Not quite sure I follow... you're altering the first element in sequence (ie 1 to n) but the 2nd appears perhaps random (?) - ie how do you determine logically as to whether or not the final element should be 49/51 ? (is it only first/last should be 51 ?)

Not very flexible going forward but perhaps:

Please Login or Register  to view this content.

Called

Please Login or Register  to view this content.

I'm glad it has worked for you.
FWIW I don't get out much, however, in Excel what I don't know still exceeds that which I do.

Please remember to mark thread as solved.

You will need to post an example wherein you have placed your code - and where #NAME? errors are being generated...

Note you can only have one function called SWITCHES else you will get an ambiguity error ie Switches and SWITCHES are one and the same as far as XL is concerned - this error will result in a #NAME? evaluation.

If however the functions are stored in Modules in a macro-enabled VBA file you should find calling SWITCHES1 / SWITCHES should work without issue.

I created a module as follows:

Please Login or Register  to view this content.

it worked initially...but after I save and close...it gives the #NAME? error now. Not sure what gives

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I get the #NAME? error on both B2 and B3 cells

The screenshot implies the file is being saved as .xlsm but are macros definitely being enabled when you open the file ?

As I've said previously without seeing the file it's very hard to pass comment... #NAME? errors are generally result of

a) macros not being enabled

b) functions being stored in objects rather than Modules

c) ambiguity (ie multiple functions of same name)

There is nothing wrong with the two functions you have posted up and based on your screenshot the file is a macro compliant file type and the functions are stored in Modules so for me that leaves options a & c.

To test macro enabled status... add the below to ThisWorkbook object (assumes you don't already have an open event in place!)

Please Login or Register  to view this content.

save & close the file, reopen file - do you get a MsgBox dialog ?

EDIT:

SCRAP THE ABOVE

I just looked again... you've named the Modules the same as the Functions themselves (ambiguity) ... change the name of the modules to something other than the name of the Function (ie prefix the Module name with "fn" or "mod") and you should find things will work.

file works for me without issue... did you input my test re: workbook_open event ?

For info. on managing VBA security in 2007 see: http://office.microsoft.com/en-gb/ex...969191033.aspx

Thanks again... I'd like to go on to create a form to take the information and spit out a text file with the config; but I'll start another thread for that....perhaps in the excel programming formum? How do I "mark this discussion as solved"?


Thank you!!!

Anne

How do I "mark this discussion as solved"?

Forum rule #10

10. Please acknowledge the response you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.

Perhapes that+s easier way: start new thread, explain problem, upload example...

...and as and when you do create the new thread please be sure to post any links should you choose to ask the question elsewhere -- and this would include LinkedIn

all seems a long winded way to go about creating a cisco config if you ask me!

fair comment,must admit i have use of tools that automate loads of this but then again that solution would cost $10000's