How to use row number from user input in formula?

[bakera8]

To be clear the cell formula is correct I do not need that changed. I am trying to figure out how to insert the row number "P" in to my formula based on the number input by the user so that I do not have to go in and hardcode the number of rows each time I change the data. The code is pulling the data from sheet1 and autofilling the matrix. I am sure there is an easier way to do this but I am trying to learn VBA so its more about the process than the final result for me so please try not to completely change the code if possible.

[AliGW]

Just to be clear - you are talking about ROWS, but P is a COLUMN.

[bakera8]

P is just the variable I came up with. The coding I have done before on other applications required a variable to be assigned to a user input in this case if the user input 40, P=40. Then where ever P is within the formula would be replaced with 40. If that is not how it works on VBA then that must be where my problem lies

[kvsrinivasamurthy]

Not clear about your requirement.
Formula in B2 then copied across may help.

=IFERROR(INDEX(Sheet1!$C$2:$C$69,AGGREGATE(15,6,ROW(Sheet1!$C$2:$C$69)/((Sheet1!$A$2:$A$69=$A2)*(Sheet1!$B$2:$B$69=B$1)),1)-ROW($C$1)),"")

[Fluff13]

How about

Please Login or Register  to view this content.

[bakera8]

How about

Please Login or Register  to view this content.

This code works. Is there a way to allow the user input to define an autofill range as well? For example if user input is 40, fillRange = Worksheets("Matrix").Range("R2C2:R40C2")

[Fluff13]

How about

Please Login or Register  to view this content.