Macro inputs values between two numbers and sort accordingly

You are The man!

I appreciate your effort!

You are welcome.

Thanks for feedback and rep

Alf

Ps If this solves your problem don't forget to mark thread "Solved" as per forum rules.

To mark your thread solved do the following:
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

One slight improvement needed. Let's say start is in 51 week, and end is in 6 week of new year I would need it to fill in 1,2,3,4,5,6 and 51,52. At the moment it doesn't react that way.

Possible?

Not sure I understands excatly what you wish to do. I've made a change to the macro so when value reach 52 then it start going 1, 2, 3 and so forth. But this is only the cell value that get changed. I'm still using the i value to "find" the right offset cell to place value in.

If this is not right could you upload a file showing what you wish.

Please Login or Register  to view this content.

Alf

As shown in yellow row.

Ok here is the modified code, have tested a bit and as far as I can see it works the way you wanted but you better do some testing yourself as well.

The macro after clearing old data finds the range of data from FK3 to FK??.

It then compaires value in FK3 with value in FL3. If FK3 greater than FL3 it assumes that cell value should start at 1 and increase it until it reaches the FL3 value.

Then it takes the FK3 value and put in in the appropriate cell and increases this value untill it reaches 52.

If FK3 value is less than FL3 it works the way it did before.

Please Login or Register  to view this content.

In the uploaded file you had pasted the macro in sheet1 but you can also go to the "Developer" tab, click the "Visual Basic" and in the new window click "Insert" and then "Module". Then paste the code in this window.

Alf

Wonderful Alf!

I appreciate your effort! Hopefully this will help someone else as well when dealing with weeks.

Best regards.

Again you are welcome.

One quick question. Looking at my calendar I see there are 53 weeks in the year 2015 but the max number in the macro is hard-coded to 52.

Please Login or Register  to view this content.

Perhaps you should change it?

Alf

Well spotted! Thanks!

When I attach macro in my original sheet I get an error (type mismatch, 13) although macro performs well. I want to ignore the error, I have tried with "On Error Resume Next" but it ruins the data.

Please Login or Register  to view this content.

Ideas?

Macro assumes all cells in range BM3:BN3 and down to be numbers, perhaps you should check if this is so.

Have you tried to step through the macro to see where it finds the problem. As the line

Please Login or Register  to view this content.

is marked in red I assume this is the row that causes the problem and that could indicate that one cell value or the corresponding cell offset value is seen as text.

Try to run this macro as a stand alone and see if any cells get red colored in the BM3:BN?? range

Please Login or Register  to view this content.

Alf

Cells in "BM3:BN" range contain formulas, but this doesn't seem to be a problem. It colours red only cells under last input in range "BM3:BN".

Any possibility to skip the error msg?

It colours red only cells under last input in range "BM3:BN".

Then there seems to something beneath the last visible value in the range "BM3:BN", can you delete the rows where you have the red cells or if that's not posible clear the range with red cells and do a rerun on the "ChkIfNum" macro and see if you have no more cells colored red.

The command

Please Login or Register  to view this content.

starts from the bottom of the column BM and works its way upwards till it "meets" a cell with value but it will also "react" on for instance ' and the ' is a symbol you don't normaly see looking at a cell.

When there is no more red cells in the BM:BN column you can run your "normal" macro and then there should be no error message.

Alf