Find MAX in range and display value of the top cell in column

I have to admit that I am lost to how this could be done. I have a worksheet with names in column 1 that will go down at least by 5000 rows (within the next 5 years). I then have a range of 39 columns (from column K to AW) that gets checked every time the name is entered more than once. In that range, the top cell has a different value and is always the same (they are room numbers).

What I want is to find the MAX in a row within the range and then enter into a cell which column has the highest value. So, say there is Bob in row 5, I want to find the maximum value in range K5:AW5. Say column L (a value of 3 let's say) has the highest value with 8, I want 3 to be placed in cell(5,9). If column P has the highest value with 10, I want to place 7 in cell(5,9).

*Further info if needed
How the names get entered is through a userform. People enter the name and the room number. Every time a name gets entered, it searches for the name in column 1 and then adds 1 to the room number they are entering. The first time they get entered their name gets placed in column 1 and other statistics gets placed as well. If need be I could place a formula here to have MAX entered into column 9.
Variables in userform:
The variable for name is: GuestName
The variable for the room is: Roomnum

I thought I could do something like this in the userform:

Please Login or Register  to view this content.

Then in the worksheet change function, it can match Cell(Tot,10) and to the number in the range and place in column 9 the value for the top of the cell.

Atleast share a screenshot if possible

Replace this line

Please Login or Register  to view this content.

With

Please Login or Register  to view this content.

Originally Posted by Templemind

find out how to match the two cells to come out with the top of the column. Any ideas?

Little bit confused about the words "Two Cells"

"Two Cells" Refers to?

Sorry. the cell of the max value and cell 10 of the same row (the outcome of the previous formula). So for row 15, column 101 (at the top of the column) has 2, which is the largest in that row. Thus 101 would be put in cell(15, 9).
Untitled.jpg

I guess I should add that this doing any kind of calculation on change would not be taxing on the program as it would only be looking at one row per change, even if there were 20,000 names. A loop would not work as it would have to search through all of the rows for those 39 columns each, but the worksheet would only change when someone entered a name with the userform.

Try this...

Please Login or Register  to view this content.

Originally Posted by Templemind

It will take me a bit to fully understand the formula. I was totally lost.

I added comment for each line for your easy understanding

Please Login or Register  to view this content.