Very long macro!!

[panyagak]

Hi Mr Excel gurus.

Am "surgically" preparing to come up with JUST ONE LONG MACRO, recording >20 sequence of events to "cut out" tedious manual repetitive tasks.

Wondering the problems I may encounter recording & running it.

Anyone who has successfully created & using a long Macro?

My intention is use THE SHORTEST TIME to achieve my analysis goals.

thanks
panyagak

 

[mole999]

bugger to debug

create 20 small macros and call them one after another, very little over head that way, just be optimal when you create your files

 

[Norie]

You should split things up into stages, record/create the code for each stage separately and then join everything up.

Also if you are using the macro recorder you'll end up with a bunch of unneeded code so you should regularly review the code that's been generated.

 

[panyagak]

Norie & Mole999

"create the code for each stage separately and then join everything up."

On "joining up", this is where I"ll end up coming back to you guys repeatedly....which i dont want... pasting here 20 codes to join up amid many quizzes!!! am learning vba

Regards

 

[mole999]

join like this example

SUB ALL
ONE
TWO
End Sub

SUB ONE
your code
End Sub

SUB TWO
your code
End Sub

 

[panyagak]

Mole999.

am expecting 84 smaller Macros!!!!!

Patrick

 

[Norie]

Patrick

If you want specific help with your code post details of what you are trying to do, otherwise all we can give is general advice.

 

[panyagak]

I have 4 columns of interest: original A B C D & a copy A B C D all with values within one sheet:

am doing sensitivity analysis on the copy:

Add 1 to B & C & hold there:then add 1 to A, add 1 to D: dont mind the formulas are preset for filtering whole numbers only.
Next less 1 from A, add 1 to D;filter only TRUE;create new sheet; copy paste filtered data

Next add 1 to A; less 1 from D

Next less 1 from A; less 1 from D

Repeat for 2 to 5.(both - & +)

NEXT
hold A & D constant: add 1, less 1 & vv then -+2 to +-5 & vv on B & C.

NEXT
hold A & B constant : add 1, less 1 & vv then -+1 to +-5 on C & D & vv.

For all these scenarios, you filter TRUE on 2 preset columns, create a new sheet & paste there & repeat next scenario.

Each scenario (+-1-5) will give me 21 Macros: add 3 others*21 equals 84 & more!!!!

Lord HELP!!

 

[Norie]

Couldn't you use a loop/loops?

 

[panyagak]

Norie

May be you can advise further on Loops. am not adept at VB - am @learning stage.

Can Excel's What-If help or Solver Add-in?

Regards
Patrick