Macro to put last entry in row in a certain cell

[apollo753]

OK I have an excel spread sheet that grades answers I have a macro that runs and puts the person with the most correct first and so on.
The last answer given is a bouns answer. The excel sheet is setup so that the persons name are in C2 to AI2. Answers are from C3 to C17 for the person in C2 and so on.
When I run the the "Correct Number" button C22-C27 are fill out.....The problem is I need the last entry in cells C2 to C17 to be in C27, the last entry can be any cell C2 to C17.

Attached please find an example of the excel sheet.

Thanks for your help

Dave

[Ken Puls]

Hi there, and welcome to the forum.

I'm not quite clear on this... You're looking for row 27 to be populated with the "last entry", I get that. But what constitutes the last entry? Based on your example:
-Column C: row 6 or 15?
-Column D: row 6 or 14?
-Column J: row 2, 6 or 14?

[apollo753]

OK. Based on the example I gave, lets look at "Name31" (AG1) his last answer this time is AG14 so the contents of that cell should end up in C27. He has the most correct and his name ends up in C22. If his last answer was in AG6 then the contents of AG6 would end up in C22. The last answer can be any cell from xx2 to xx17. A empty (blank) cell is not the last answer. Also if you look at Name08 (J1) his last answer is J2, (NO*). Name10 L1 his last answer is L6 (NO*).

Thanks
Dave

[Ken Puls]

Hi Dave,

Sorry, I'm still not totally clear on a copule of things...

-"AG6 would end up in C22". C22 would be for his name though, from row 1, would it not?
-With the "last answer"... is that the last answer that the person physically typed into the worksheet, or is it the last in the column of data... do you follow me?

So basically, the table from A1:AI20 never moves or changes column orders. It's just the one in C22:C27 that moves around, correct? (That might make things easier...)

[apollo753]

AG6 would end up in C27. Yes C22 is for the persons name.

Yes the table A1:A20 never moves....correct.

Yes, it is the last answer in the column.

Yes C22:C27 moves around, but also xx22 to xx27 moves around it is sorted based on who has the most correct.

[Ken Puls]

Hi there,

Okay, so I've given this a go, but with an intention of making this a little more worksheet function intensive than VBA.

I did add a VBA routine in the Sheet3 module as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
'Copies most recent entry to row 21 for later reference
    If Not Intersect(Target, Target.Parent.Range("C2:AI17")) Is Nothing Then
        Target.Parent.Cells(21, Target.Column).Value = Target.Value
    End If
End Sub

But that's as much as I used. If you look in rows 34:42, I re-wrote your table using formulas only. This table is dynamic, and will automatically update as new people get higher/different scores. It uses some Excel rank functions to determine what order the tests belong in, and uses index statements to pull out the appropriate stats after that.

If you like this, you can knock off your entire colour sorting routine. This will probably be much easier for most users to maintain.

In addition, I have to question the need for your CountColors routine. With conditional formatting, you could highlight the answers in your table if they match column B's value. In addition, you could solve column A by using a COUNTIF worksheet function. Again, easier to maintain, and it would also be marginally faster.

If you would like to pursue that, please let me know and I'd be glad to help.