Passing user defined parameter onto next sub

[D_N_L]

Hi everyone,

I have this code below which works fine. As you can see, when it fires, it always ensures that there is a space 3 spaces from the right. So it would change this postcode/zipcode LE987G into LE9 87G. However what I need it to do is to work off a user defined range rather than a fixed range.

The code immediately below shows the static code for creating the space. The code beneath that shows the subs where the user defines their range.

I hope that makes sense. I'm trying to learn the convention for how a user defined range is passed from one sub to the next, i.e. how can I apply Sub Macro1() to a range that the user has defined. Hope you can help.

Sub that has fixed range (want this to be whatever range the user picks)

Please Login or Register  to view this content.

The below code shows subs where the user defines their own range. I need the code above appending to the end of this code (using the user defined range)

Please Login or Register  to view this content.

[romperstomper]

Change it to:

Please Login or Register  to view this content.

then add:

Please Login or Register  to view this content.

to the other code.

[D_N_L]

Hi RS, (nice pic by the way),

Thanks for your help man!! Where abouts does the 'macro 1 wb2rng' code go? I got an Ref error.

[romperstomper]

Depends when you want it to work. Possibly:

Please Login or Register  to view this content.

[D_N_L]

Thanks man. I got the below code to work (well not bug out anyway) but it doesn't actually perform the final sub (SubMacro1), i.e. it doesn't change to text from LE98LG to LE9 8LG. The range (3 cells) I selected after the macro had ran had the following formula in them:

=LEFT(SUBSTITUTE(IV5," ",""),LEN(SUBSTITUTE(SUBSTITUTE(IV5," ","")," ",""))-3) & " " & RIGHT(SUBSTITUTE(IV5," ",""),3)

Any ideas how I ensure that the range passes into the formula too, i.e. so that cell IV5 (and IV6 & IV7) isn't referenced but that the user defined range is?

Thanks for your help so far!!

Please Login or Register  to view this content.

[romperstomper]

Ahh, I think I see. You want the formula to refer to the selected range, not to be put in the selected range? If so, I think you actually want:

Please Login or Register  to view this content.

which will add your formulas to the cells to the right of the selected range.
If that's not what you want, can you clarify?

[D_N_L]

Hi RS,

Yes, that's exactly what I mean. Thank you for sticking with it!! I have tried your amendment and it works flawlessly. Is there no way to actually change the selected range rather than offsetting?

If not I'm happy to use your suggestion (and mark it solved and add rep).

[romperstomper]

Try this: change macro1 to:

Please Login or Register  to view this content.

and then use the original:

Please Login or Register  to view this content.

[D_N_L]

Thanks man! Really appreciate your help!