Simplifying Code Using For-Next Loop

[inwalkedbud]

Looking to simplify the following code possibly with For-Next Loop.

Please Login or Register  to view this content.

Where the My Subs are private sub procedures. Thanks.

[mikerickson]

This question was also asked at
http://www.mrexcel.com/forum/showthread.php?t=328091
There are a couple of options there, both use Application.Run

[rylo]

Hi

Try this....

Please Login or Register  to view this content.

rylo

[inwalkedbud]

thanks for the response. What if the sheets were named (ex. "A", "B", "C" etc...) instead of numbered?

[rylo]

Hi

Does the macro have the same suffix as the sheet (ie A,B...) or does it still have the number suffix?

rylo

[inwalkedbud]

sorry, but i tried to give a generic example in the first code section. code looks more like this. thanks.

Please Login or Register  to view this content.

[jasoncw]

You can convert numbers to alpha characters using the Chr VBA function. Using Rylo's code above, as well as your example for sheets A-D, you could use:

Please Login or Register  to view this content.

Chr(65) = "A"
Chr(66) = "B"
etc.

HTH

Jason

[shg]

How much simpler could it be?

Please Login or Register  to view this content.

... would be more like obscure than good practice.

[inwalkedbud]

What if subs are private?

Please Login or Register  to view this content.

doesnt work the same as

Please Login or Register  to view this content.

[shg]

The first example doesn't work at all, for the same reason you can't do this:

Please Login or Register  to view this content.

[inwalkedbud]

looking to call the sub (in arr2) which corresponds to the correct sheet(arr1), however error message says that a sub, function or property is expected after the call command. thanks.

Please Login or Register  to view this content.

[rylo]

Hi

Try changing

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

actually shouldn't it be

Please Login or Register  to view this content.

rylo

[inwalkedbud]

rylo the first part of your suggestion works as far as activating the sheets however the second array (arr2) is an array of private subs which for some reason arent called in the examples above.

[rylo]

Hi

Being lazy here - can you put up an example workbook with the subs, sheets etc.

rylo

[inwalkedbud]

hey rylo-- i have completely solved my problem for this post. i re-worked it more like your most recent post and got it to work. Thanks again.

[inwalkedbud]

how bout this one. this is a simplified version.

Please Login or Register  to view this content.

i tried to do the standard concatenation but the compiler didnt like the fact that the new line started with quotes.

Please Login or Register  to view this content.

didnt work for me.

[inwalkedbud]

i tried to add this code to the previous post to eliminate the compile error but, i get new compile error: syntax error. any thoughts. thanks.

Please Login or Register  to view this content.

[mikerickson]

VB does not treat strings resulting from concatenation as variables.
VB does not treat strings resulting from concatenation as properties, methods, functions, collections, or objects.

The approach you have been trying throughout this thread (refering to the argument a_1 as "a_" & "1") is fatally flawed.

If you describe what you are trying to do, a successful approach (probably involving arrays) might be found.

[inwalkedbud]

mike--

is there any way to reduce the redundancy then? thanks. bud.

[mikerickson]

You should look into arrays.
The code above could be done like this:

Please Login or Register  to view this content.

This could be further simplified, to one loop

Please Login or Register  to view this content.

Starting with arrays and using For..Next loops makes it easier to see this version.

Please Login or Register  to view this content.

Unless you need myRange or formulaString later in the routine, this would be the way to go.