Results 1 to 6 of 6

Thread: Macro to put last entry in row in a certain cell

  1. #1

    Macro to put last entry in row in a certain cell



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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...)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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.

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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:
    Code:
    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.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •