Counting, populating and formatting rows help

[0mgman]

I have a workbook that has sheet1 filled with a bunch of data and I need to write some vb code to do the following:
1. Starting row 11 count how many ROWS are used from 11 to end of list.
2. Take the amount of rows from sheet1 and populate sheet2 rows starting at cell A5.
3. Apply formulas related to different cells in each row, ex: A5+C5=J5, A11+D11=M11, etc...
4. Apply conditional formatting so that it colors every other row based on how many rows are used.(I can do conditional formatting, just dont know how to apply it to a certain range of rows based on a count.
5. Add a "totals" cell that sums the values based on how many rows are used puts the cell directly under the rows used without conditional formatting. ex: If rows 5-12 are used it will put the total in row 13 and add rows 5-12.

One thing to note is the reason the amount of rows needs to be counted is because the amount of rows will change every time I run this code. I could use 5 rows today, 27 rows tomorrow and not every cell in a row will be used. Right now I am copy/pasting everything and when I do that it breaks all my formulas, formatting so I have to do it all by hand over and over.

So is this even possible with Excel or should I be using something else?

[contaminated]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

Doing this will ensure you get the result you need!

[Marcol]

Hi 0mgman
Welcome to the Forum.

You would do best to post a sample workbook showing Before and After.
and in this case where your formulae might be in the row

It should clearly illustrate your problem and not contain any sensitive data.

Cheers

[EDIT]
Ooops!!!

Now you have it in duplicate!!!, that should tell you something!!!

[0mgman]

Thanks for the welcome and quick replies!

Ill put together a workbook to show what I want and how it looks.

[0mgman]

Ok, finished a quick mock up of what I want.

sheet1 is the output I get from my program. I put comments on that sheet to explain what is happening with the data in it.
sheet2 is crudely formatted just to show how it needs to work. The yellow cells are the data that is pulled from sheet1 and the blue cells contain formulas for each row.

[0mgman]

I just realized I dont have a "totals" cell at the bottom of sheet for cycle time....
Like my first post says I will need a total there below the rows.

[0mgman]

Here is the new mockup of what I'm after including the totals.

[Marcol]

Hi 0mgman

I am not entirely sure about all of your request.

However
Give this a try to start with.

You do not have to do anything with Sheet2, except check the data in D7:I7 and N7:O7.
I have assumed the data in these cells are constants, (I know, some of them at least, are not,) and have copied them with each entry.
You can accept them as that and manually alter anyone after you run the macro, or delete them before you run the macro then add all the data as required.

I have added a formula to B7 that will increment the Milling Operation Numbers, after you run the macro you can alter any of them as required, or if there is a rule to follow, I can alter the macro to accommodate it.

All other formulae have been altered to display either an empty string "" or 0 on error.

There are two macros involved you only need to use "TransferData" as it automatically calls "ClearSheet2". I have left "ClearSheet2" public just so you can easily check what it does.

I have left out the conditional formatting of the rows for now, let us get the sheet working properly first.

You might want to consider using a look up table for tooling data, I have added a sheet called Lookups, it is not used as yet.

"Sheet2 (RequiredResult)" is a backup and can be deleted.

Hope this helps.

If you need any further information, please feel free to ask further.

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[0mgman]

Ok this is looking great so far but there is a couple things.

1. The columns Operation Description, NOT, FPT, SFM, WOC, Length of Cut, DOC, Tool Change and Rapid Time on sheet2 will all be filled out by me after the tool list is populated so they are not constants. So the formula for B7 is not needed as that will change every time I run this workbook, along with copying the values that are in those cells, not necessary.
2.The formula in P14 isn't working as it does on the RequiredResult worksheet. It only took the value from P7, not added the whole column.

I now have some ideas on how this works, the lookups worksheet will be my best bet. I just don't know how to get the formulas to work. Can you show me the code on how that works? Or am I over thinking things?

[Marcol]

Okay,

I thought you might have had some commonly used vales, rather than constants as such.

I missed that formula in Column P it is now corrected, sorry about that.
I've also tidied the formula in M7 it was showing a #DIV/0! error.

Amended workbook attached

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.