Excel VBA: How to push data to an exclusive range on another sheet?

Question

I use the sub below as part of another sub which error checks and saves a grade sheet. There will be at least 39 grade sheets generated throughout the course this is built for. Each event may be accomplished more than once - a weather cancel, or student failed.

The FindEmptyInsertData sub takes the three most important data from a grade sheet (Date, Grade, Status) and adds them to a sheet named Index. Index then keeps a running tally on the events accomplished...based solely on the output of the 'Error Check and Save' macro.

My question is more of a logic question, rather than for specific code (although it'll help). FindEmptyInsertData works wonderfully. However, the data gets pushed and added to Index however many times the user clicks the 'Error Check and Save' Form Control button. I would like it to only get pushed once per grade sheet...the problem/challenge is that a user might need to go back and change the grade sheet (wrong date, different status...etc).

Index looks like this:

    Event   ABC-1   ABC-2   DEF-1   DEF-2
    Date    dd-mmm  dd-mmm  dd-mmm  dd-mmm
    Grade     1        2      2       3
    Status    WX      EFF    EFF     EFF
            ----    ----    ----    ----
    Date    dd-mmm
    Grade     3
    Status   EFF

I'm thinking that my solution will lie in the fact that only one event will ever be attempted/accomplished per day. Therefore...if date of gradesheet matches the date in index, then don't push the data again...except if the grade or status changes. Ugh, my brain hurts!

Thanks in advance!

Sub FindEmptyInsertData()

Dim ws As Worksheet
Dim gsDate As Date
Dim gsWorking As String
Dim gsMsnNum As String
Dim colNum As Integer

gsWorking = ActiveWindow.ActiveSheet.Name
gsDate = ActiveSheet.Range("S3")
gsGrade = ActiveSheet.Range("D40")
gsStatus = ActiveSheet.Range("O7")
gsMsnNum = ActiveSheet.Range("D3")

Application.ScreenUpdating = False


    'Opens up the INDEX (Sheet4) and finds the first empty cell
        Sheet4.Activate
        Sheet4.Unprotect

    'Finds the sortie name column in INDEX
        For Each Cell In ActiveSheet.Rows(5).Cells
        If Cell = gsMsnNum Then Cell.Select: Exit For
        Next Cell

    'Takes the active column number and assigns it to the variable
        colNum = ActiveCell.Column

    'Finds the first open cell in that column
        For Each Cell In ActiveSheet.Columns(colNum).Cells
        If Len(Cell) = 0 Then Cell.Select: Exit For
        Next Cell

    ActiveCell.Value = gsDate 'Prints the Date from the GS into the first empty cell
    ActiveCell.NumberFormat = "dd-mmm"

        Selection.Offset(1, 0).Select 'Moves One Cell Down

    ActiveCell.Value = gsGrade 'Prints the Grade from the GS

        Selection.Offset(1, 0).Select 'Moves One Cell Down

    ActiveCell.Value = gsStatus 'Prints the Status from the GS
    ActiveCell.Borders(xlEdgeBottom).Weight = xlMedium 'Adds a bottom border

    'Protects the Index Page
    Sheet4.Protect



    'Returns to the Previously open GS
    Worksheets(gsWorking).Activate


End Sub

Answer 1

Without seeing your Index and other code involved, this is the best recommendation I could make.

Conceptually, as far as simple database design goes, you might benefit from adding a 5th row for Event Sequence. Add an integer variable to your code that looks for the sequence and when assigning data for an event. Increment it by 1 when adding event data to the index. If you hate the idea of consuming an entire row you can also tell your code to hide the row.

This way you can have as many entries containing same or different data as necessary. You can choose to accept the "Event" and highest number for "Sequence" as the final submitted grade by default.

Event   ABC-1   ABC-2   DEF-1   DEF-2
-------------------------------------
Seq       1        1      1       1    ' <--- Pull data by Event Name & Sequence
Date    dd-mmm  dd-mmm  dd-mmm  dd-mmm
Grade     1        2      2       3
Status    WX      EFF    EFF     EFF
         ----    ----    ----    ----
Seq       2                            ' <--- Pull data by Event Name & Sequence
Date    dd-mmm
Grade     3
Status   EFF

---

Additionally, you could add another row to the Index that would contain data for which event is the active one that you have pushed to the grade sheet.

Event   ABC-1   ABC-2   DEF-1   DEF-2
-------------------------------------
Seq       1        1      1       1   ' <--- Pull data by Event Name & Sequence
Date    dd-mmm  dd-mmm  dd-mmm  dd-mmm
Grade     1        2      2       3
Status    WX      EFF    EFF     EFF
Active    0        1      1       1
         ----    ----    ----    ----
Seq       2                            ' <--- Pull data by Event Name & Sequence
Date    dd-mmm
Grade     3
Status   EFF
Active    1