Using input box data to create formula

Hello,

I'm creating a macro that ranks data. The formula I currently want to use is the following: =RANK(N2,N$2:N$14,1)+SUMPRODUCT((N2=N$2:N$14)*(M2>M$2:M$14))

Or in VBA: "=RANK(RC[-1],R2c14:R1000C14,1)+SUMPRODUCT((RC[-1]=R2C14:r1000c14)*(RC[-2]>R2C13:r1000c13))"

The formula works, but I want it to be dynamic as the last row in this table will be changing every month. The last row of data is not the same as the last row of the table so I can't use that.

What I was thinking of doing is creating an input box and using the input from there to write the dynamic formula. The code I have is listed below.

Please Login or Register  to view this content.

Say I input "14", is there any way to incorporate this number into the formula? Thanks!