Excel VBA: how to move data to an exclusive range in another sheet?
I am using sub below as part of another sub whose error checks and saves the score sheet. At least 39 classrooms will be created throughout the course. Each event can be completed more than once - cancellation of weather conditions or student failed.
The FindEmptyInsertData subgroup takes the three most important data from the score sheet (Date, Grade, Status) and adds it to the sheet named Index. The index then stores a current estimate of the events executed ... based solely on the output of the Error Check and Save macro.
My question is more logical than specific code (although it will help). FindEmptyInsertData works wonderfully. However, data is inserted and added to the index, but many times the user clicks the Error Control and Save button on the form. I would like it to only click once per grade sheet ... the problem / problem is that the user might need to go back and change the grade sheet (wrong date, different status ... etc).
The pointer 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 think my solution would be that only one event will ever be taken / executed per day. Therefore ... if the date of the column matches the date in the index, then do not press the data again ... unless the class or state has changed. Wow, 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
source to share
Without looking at your Index and other code, this is the best recommendation I could make.
Conceptually, for simple database design, you may need to add a 5th line for the sequence of events. Add an integer variable to your code that looks for a sequence and when assigning data to an event. Increase it by 1 when adding event data to the index. If you hate the idea of consuming a whole line, you can also tell your code to hide the line.
This way you can have as many records as possible containing the same or different data. You can accept "Event" and the highest number for "Sequence" as the final default score submitted.
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
Alternatively, you can add another row to the index, which will contain the data for which the event is active that you clicked on the score 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
source to share
Thank you so much for your answer! I had the same idea as you when I went to bed last night thinking about how to solve this problem ... give each event a unique ID that is pulled along with other data. I knew it would be more of a boolean answer, with several solutions (for example, something in life, eh?).
BUT ... what I ended up with (for now, at least) was sticking with using the Date bound to the event name as "unique" qualifiers. I understand that there are some gaps in this logic (what if the date changes after the data is pressed?), The solution will be resolved. But the date is automatically generated by the Score Sheet, so I hope there will be little or no occurrence.
For others reading my first (!) Post here, here's my solution:
'Finds the Event 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 a date match in that column
For Each Cell In ActiveSheet.Range(Cells(6, colNum), Cells(40, colNum))
If Cell.Value = gsDate Then Cell.Select: Exit For
Next Cell
'If the date is the same, it will overwrite the grade and status
If ActiveCell.Value = gsDate Then
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
Pick up my previous code where I match the name of the event on the INDEX page going through line 5. Then I assign the column number to colNum.
Now for the new code: Since I took the date from the Scorecard, I use the matched Event column to search down to find the appropriate date. If it finds a suitable date, it will overwrite the old one with the new class and status. Otherwise, it will continue with the code that was previously recorded and find the first blank cell in the Event column and print out the date, category and status.
I hope this helps someone solve the problems, or at least make you think of a constructive solution ... or you just learn something new like me!
I went from zero VBA knowledge to where I am now (still learning a lot, but dangerous enough to impress the bosses) in just a couple of weeks thanks to this site and everyone who contributed. THANKS YOU THANKS YOU !!!!
source to share