• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Loop Issue

C

coolkiran

Member
My second post in a day;)

I have VBA spread sheet, all codes completed, but i am stuck in final loop,

When a button is pressed, macro will create sheets based on Stages, In Column J, in the attached sheet there is total 7 stages, so 7 new sheets, then it will copy some information to each sheets, and will apply some formula. Till this part completed.

My problem is , in Column O there is column called Time Step, means there user will enter 1-50, if user selected 3, then i need to copy first 3 tables (table means group of 5 rows located after the button ) to copy and paste to that particular stages, so till 50, i have created table (means here 5 rows), Currently its copying 5 steps,

In manual i am sure it will lot of time, Any one suggest how we can do it in Loop.

Here is the file.
 

Attachments

Hi Kiran ,

Can you clarify a little more ?

In column O , from O4 through O10 , you have the values 1 ; 2 ; 3 ; 2 ; 1 ; 2 ; 3.

In the range A23 through AH96 , you have some data organized in groups of 5 rows and 6 columns , for a total of 50 steps , though the numbering needs to be corrected. Secondly , the gaps between rows of steps are uneven ; preferably this should be corrected.

After doing the above , can you explain what should be done with the data in the range O4 through O10 ?

Narayan
 
Ok,

If in O4, entered as 2, then i need to copy from A23:F33
If entered as 3, then i need to copy from A23: F41
If entered as 4, then i need to copy from A23: f49, similarly till 50,

Based on the stage, In this example, macro will create 7 sheets, Stage 1, Stage 2 ... Stage 7,

In that sheet, for example, Stage 1, i need to check the Time Steps, here its 1, so i need to copy from A23: F27 and paste in Stage 1 sheet, similarly i need to other sheet as well.
 
Hi, coolkiran!

You have many ways to do it, but none of them is desirable or practical.

The main issue is your table data distribution, which I assume that is the best suitable for your model but a bit weird for Excel, since there's no relation between the rows or columns with the stages and time steps, i.e. from the row and column of the starting cell of each group of 5x5 (or 5x6 if we consider the merged cell -which shouldn't exist!-) there's no relation that let deduce the stage and time step. So the 1st approach is discarded.

The next one is to put all groups in a separate worksheet in a row basis, in 5x7 groups (1st col for stage, 2nd col for time step, 3rd-7th col for data), all adjacent and one below the previous. Then in worksheet Zone2 you'd build a map of how do you want to distribute/locate the groups, if required; if they're only for being copied to the created worksheets you can omit this step.

Maybe there're more possibilities but they'd all depend on your requirements. Any thoughts?

Regards!
 
Hi Kiran ,

Check the uploaded file.

I have made the gaps between the steps uniform , as I had requested you to do in my earlier post.

The uploaded file has a circular reference error ; please run the macro in this file , and when you are satisfied it is working the way you want it to , copy the code to your file , make the gaps between the steps uniform , and then use the macro with your data.

I have also defined 6 named ranges , which you will have to incorporate in your workbook.

Narayan
 

Attachments

Awesome, It works perfectly, I am surprised with 10-15 code line power. My code was near to 3000 lines, and that time it was 10-15% completed. You guys are too good. Thanks alot Narayan.
 
You must log in or register to reply here.
Back
Top