Problem with macro calculating on different data sets with different numbers of rows

I'm still pretty new to using macros and would appreciate any help with the below :

I have attached a data file and the macro. the macro calculates the lease term in years and gives a payout quote based on the lease term. This works fine for the attache data set (329 rows), however, when i try to use the macro on a larger data (1000 rows) it only calculates down to row 329.

hopefully its something really simple that needs to be changed

I would appreciate any help.

thanks

Hi,
You have 4 lines of code that need changing to whatever row you want to use

Please Login or Register  to view this content.

Please Login or Register  to view this content.

the number 329 is preventing any further data being filtered. Just change it to however many rows you want.

I think this does the same as yours but is shorter, quicker and not limited to 329 rows:

Please Login or Register  to view this content.

Regards, TMS

hi,

is it possible to change the range so it automatically calculates for any number of rows without having to change it everytime for a different number of rows?

is it possible to change the range so it automatically calculates for any number of rows

See post #3. That's what it does.

Regards, TMS

Change this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

The problem is that it's a different data set and there is no data in column C. You need to use a column with the maximum number of rows.


Regards, TMS

One way: =SUM(L:L)

Regards, TMS

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save