Using a variable to set the end of a for loop

[gwithey]

Is it possible to use a variable to set the end of my for loop.

I only want the loop to continue while each row in worksheet 3 contains data

I have tried this as shown by creating a "TotalRows" Variable as shown in the code but i cant get it working.

Please Login or Register  to view this content.

Does anyone know how i can calculate the formulas so long as the current row the loop is on has data in it.


Thanx George

[royUK]

Is there any other data on the sheet because your TotalRows variable should work. You could try

Please Login or Register  to view this content.

[gwithey]

Thank you the code now works well and carries out the calculations.

It is considerably slower than my other macros. Although the others only copy this data rather than calculate it.

Is there any simple way to speed up operation other than the ways i have tried?

Please Login or Register  to view this content.

Thanx George

[royUK]

The calculations shouldn't affect speed, Calculation is switched off whilst the macro is running. I haven't tested this but try it on a backup workbook

Please Login or Register  to view this content.

[gwithey]

That is a good idea for cutting it down but it doesnt work.

Dont know if it is because there is no way of the formulas looping. It doesnt even cayy out one loop so im not sure

[royUK]

Can you attach an example workbook?

[gwithey]

I noticed another thing i cannot assign this macro to the red button to calculate it.

It only works by selecting run macro from tool bar or running straight from the editor

[royUK]

The amended code seems to work fine.

I have also set it to start the formulas in Row2

[gwithey]

That is better works well

Do you know why i cannot execute the macro by assigning it to the red button?

It will run but only execute 34 rows down not to the bottom of the data

Thanx George

[gwithey]

Dont worry figured out the problem the macro would only run on the sheet when i was on that sheet so i had to reference it at the start of the macro in order to asign it to a button

Thanx again for the help Roy

George

[royUK]

The button worked on the example that I uploaded, I had added the With Sheet.... statement.