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.
Say I input "14", is there any way to incorporate this number into the formula? Thanks!Dim RowNum As String RowNum = InputBox("What is the number of the last row of data", "Row Number", "Enter last row number HERE") MsgBox "The row number being used is " & RowNum
Bookmarks