Dynamic Array Problems

[l8sk8r]

Hi all

I'm trying to create a dynamic array so that when a user enters the first sheet name via an input box, excel will know to select the other sheets with ending (2), (3), (4) etc.

Currently this code works only if there are 4 sheets present (as I set this way), anything lower/higher is out of range.

How do I make it so that it will select sheets if there are less than 4 sheets present?

I tried nesting, but I pretty sure this array needs redimming or something. Is ubound a better option?

Any input would be most helpful!

Cheers


Danny

Please Login or Register  to view this content.

[Andy Pope]

This will create an array containing all worksheets named with the prefix user enters.

Please Login or Register  to view this content.

[l8sk8r]

Thanks for you help on this!

The code works fine except this way causes to pick up everything after the string which is what i am trying to avoid

say if I enter 'AH' and it will pick up AH, AH (2), AH (3) and AH (4) along with AHM, AHM (2), etc..

Anyway to get round this?

Cheers

Danny

Please Login or Register  to view this content.

[Andy Pope]

You code add a couple more tests.
Must end with a ) and contain a (
You could even add another test to check text within ( ) is numeric.

Please Login or Register  to view this content.

[l8sk8r]

Yeah I tried to a couple of different variations using left, right, instr but ran into issues with the first sheet. I have managed to affix my original code into this. Luckily it gives me the result I wanted. It's pretty ugly but thought I'd post it up.

Thanks for your help andy. Much appreciated.

Cheers

Danny

Please Login or Register  to view this content.

[Andy Pope]

Thanks for posting your code.

To resolve the issue with the sheet that matches you could add a test to check length of name matches when right closing bracket is not found.