Automate data attribution processes, Excel VBA

Here’s an example of a VBA code to automate the process of assigning grades based on scores in an Excel spreadsheet. This example assumes you have a table with names in column A, scores in column B, and you want to automatically assign grades in column C based on the scores in column B.

Scenario:

Suppose you have a table with names in column A and scores in column B. You want to automatically assign grades in column C based on the following conditions:

• If the score is greater than or equal to 90, the grade should be « A ».
• If the score is between 75 and 89, the grade should be « B ».
• If the score is between 60 and 74, the grade should be « C ».
• If the score is below 60, the grade should be « D ».

VBA Code:

Sub AssignGrades()
    Dim ws As Worksheet
    Dim i As Long
    Dim score As Double
    Dim grade As String   
    ' Reference to the active sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")   
    ' Loop through the rows of the table (starting from row 2, assuming row 1 has headers)
    For i = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
        ' Get the score from column B
        score = ws.Cells(i, 2).Value     
        ' Assign grades based on the score
        If score >= 90 Then
            grade = "A"
        ElseIf score >= 75 Then
            grade = "B"
        ElseIf score >= 60 Then
            grade = "C"
        Else
            grade = "D"
        End If       
        ' Assign the grade in column C
        ws.Cells(i, 3).Value = grade
    Next i
End Sub

Explanation of the Code:

1. Variable Declarations:
   • ws: a variable to reference the worksheet where the data is stored.
   • i: a variable for looping through the rows in the table.
   • score: a variable to hold the score value for each row.
   • grade: a variable to store the grade to be assigned.
2. Referencing the Worksheet:
   • Set ws = ThisWorkbook.Sheets(« Sheet1 »): This line defines the worksheet we’re working on. You can change « Sheet1 » to the actual name of your sheet.
3. Looping through the Rows:
   • The loop starts at row 2 (assuming the first row contains headers) and goes through all the rows in column B, stopping at the last non-empty row (ws.Cells(ws.Rows.Count, « B »).End(xlUp).Row).
4. Condition to Assign Grades:
   • The If statement checks the score in column B and assigns a grade to the grade variable based on the value of the score.
   • For example, if the score is 90 or higher, the grade will be « A », and so on.
5. Assigning the Grade:
   • After determining the grade, the grade is assigned to column C of the same row (ws.Cells(i, 3).Value = grade).

How to Run the Code:

1. Open your Excel file.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, click Insert > Module to add a new module.
4. Copy and paste the code into the new module.
5. Close the VBA editor.
6. To run the script, press Alt + F8, select AssignGrades, and click Run.

Expected Result:

After running this script, the grades will be automatically filled in column C based on the scores in column B.

Possible Enhancements:

• You can add error handling to manage invalid values or empty cells.
• You can set the script to run automatically when the workbook is opened or when specific cells are modified (e.g., by using Workbook_Open or Worksheet_Change events).