This VBA macro named `RecalculateScores` performs the following tasks: ### Purpose: The...

August 28, 2025 at 05:57 PM



Sub RecalculateScores()
    Dim wsMentor As Worksheet, wsMentee As Worksheet, wsEval As Worksheet
    Dim mentorRow As Long, menteeRow As Long, evalRow As Long
    Dim score As Integer
    Dim mentorIndustry As String, mentorComm As String, mentorAvail As String, mentorPersonality As String
    Dim menteeIndustry As String, menteeComm As String, menteeAvail As String, menteePersonality As String

    Set wsMentor = ThisWorkbook.Sheets("Mentor Input")
    Set wsMentee = ThisWorkbook.Sheets("Mentee Input")
    Set wsEval = ThisWorkbook.Sheets("Evaluation")

    ' Clear old evaluation data
    wsEval.Range("A2:C" & wsEval.Cells(wsEval.Rows.Count, "A").End(xlUp).Row).ClearContents
    evalRow = 2
    For mentorRow = 2 To wsMentor.Cells(wsMentor.Rows.Count, "A").End(xlUp).Row
        mentorIndustry = wsMentor.Cells(mentorRow, 2).Value
        mentorComm = wsMentor.Cells(mentorRow, 4).Value
        mentorAvail = wsMentor.Cells(mentorRow, 5).Value
        mentorPersonality = wsMentor.Cells(mentorRow, 6).Value

        For menteeRow = 2 To wsMentee.Cells(wsMentee.Rows.Count, "A").End(xlUp).Row
            menteeIndustry = wsMentee.Cells(menteeRow, 2).Value
            menteeComm = wsMentee.Cells(menteeRow, 4).Value
            menteeAvail = wsMentee.Cells(menteeRow, 5).Value
            menteePersonality = wsMentee.Cells(menteeRow, 6).Value

            score = 0
            If mentorIndustry = menteeIndustry Then score = score + 25
            If mentorComm = menteeComm Then score = score + 25
            If mentorAvail = menteeAvail Then score = score + 25
            If mentorPersonality = menteePersonality Then score = score + 25

            wsEval.Cells(evalRow, 1).Value = wsMentor.Cells(mentorRow, 1).Value
            wsEval.Cells(evalRow, 2).Value = wsMentee.Cells(menteeRow, 1).Value
            wsEval.Cells(evalRow, 3).Value = score
            evalRow = evalRow + 1
        Next menteeRow
    Next mentorRow
End Sub

This VBA macro named RecalculateScores performs the following tasks:

Purpose:

The code matches mentors and mentees based on certain criteria, calculates a compatibility score for each possible mentor-mentee pair, and stores the results in an "Evaluation" worksheet.

Explanation:

Declaring and Initializing Variables:
- The code declares variables for worksheets (wsMentor, wsMentee, wsEval) to store references to the sheets named "Mentor Input", "Mentee Input", and "Evaluation".
- Other variables are used to store the row indexes (mentorRow, menteeRow, evalRow), scoring criteria (strings like mentorIndustry, etc.), and the compatibility score (score).
Clearing Previous Results:
- The macro clears old evaluation data in the "Evaluation" sheet by removing content from columns A to C, starting from the second row to the last populated row.
Looping Through Mentors and Mentees:
- The outer loop iterates through all rows in the "Mentor Input" sheet (assuming data starts from row 2).
- For each mentor, an inner loop iterates through all rows in the "Mentee Input" sheet (also starting from row 2).
Matching Criteria and Scoring:
- The macro retrieves the relevant pieces of data for the mentor and mentee (e.g., Industry, Communication Style, Availability, Personality) from specified columns.
- A score variable starts at 0 and increases by 25 for each criterion (out of 4) where the mentor and mentee values match.
Storing Results in the "Evaluation" Worksheet:
- For each mentor-mentee pair, the macro stores:
  - Mentor's name/ID in column A,
  - Mentee's name/ID in column B,
  - Calculated compatibility score in column C.
- This data is placed starting at row 2 of the "Evaluation" sheet, and evalRow increments with each entry to keep track of the row index.
Output:
- After the macro completes, the "Evaluation" worksheet contains a list of all possible mentor-mentee pairs along with their scores.
- Each score reflects compatibility based on common attributes.

Summary:

This code compares all mentors and mentees from their respective sheets and assigns compatibility scores based on four specific characteristics (industry, communication style, availability, and personality). The results are neatly written to the "Evaluation" worksheet, allowing for an organized view of how well each mentor matches with each mentee. Each match is scored out of 100 (25 points per criterion).

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr