This VBA macro, `RecalculateScores`, evaluates compatibility scores between mentors and...

August 28, 2025 at 06:13 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 Double
    Dim mentorIndustry As String, mentorComm As String, mentorAvail As String, mentorPersonality As String, mentorSkills As String
    Dim menteeIndustry As String, menteeComm As String, menteeAvail As String, menteePersonality As String, menteeGoals As String
    Dim skill As Variant, matchFound As Boolean

    ' Define weights
    Const WEIGHT_INDUSTRY As Double = 0.3
    Const WEIGHT_SKILLS As Double = 0.3
    Const WEIGHT_COMM As Double = 0.15
    Const WEIGHT_AVAIL As Double = 0.15
    Const WEIGHT_PERSONALITY As Double = 0.1

    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 = Trim(wsMentor.Cells(mentorRow, 2).Value)
        mentorSkills = wsMentor.Cells(mentorRow, 3).Value
        mentorComm = Trim(wsMentor.Cells(mentorRow, 4).Value)
        mentorAvail = Trim(wsMentor.Cells(mentorRow, 5).Value)
        mentorPersonality = Trim(wsMentor.Cells(mentorRow, 6).Value)

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

            score = 0

            ' Industry match
            If LCase(mentorIndustry) = LCase(menteeIndustry) Then
                score = score + WEIGHT_INDUSTRY
            End If

            ' Skills vs Goals match
            matchFound = False
            For Each skill In Split(mentorSkills, ",")
                If InStr(1, LCase(menteeGoals), LCase(Trim(skill))) > 0 Then
                    matchFound = True
                    Exit For
                End If
            Next skill
            If matchFound Then
                score = score + WEIGHT_SKILLS
            End If

            ' Communication match
            If LCase(mentorComm) = LCase(menteeComm) Then
                score = score + WEIGHT_COMM
            End If

            ' Availability match
            If LCase(mentorAvail) = LCase(menteeAvail) Then
                score = score + WEIGHT_AVAIL
            End If

            ' Personality match
            If LCase(mentorPersonality) = LCase(menteePersonality) Then
                score = score + WEIGHT_PERSONALITY
            End If
            ' Write to Evaluation sheet
            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 = Round(score * 100, 0)
            evalRow = evalRow + 1
        Next menteeRow
    Next mentorRow
End Sub

This VBA macro, RecalculateScores, evaluates compatibility scores between mentors and mentees based on criteria from two worksheets and writes the results to a third worksheet. Here's a detailed breakdown of what it does:

1. Sets Up Worksheets and Variables

The macro interacts with three worksheets:

"Mentor Input": Contains mentor information.
"Mentee Input": Contains mentee information.
"Evaluation": Serves as the output sheet where compatibility scores are recorded.

It also defines variables for mentor and mentee attributes such as:

Industry, skills, communication style, availability, personality, and goals.
A score and weights for evaluating compatibility.

2. Clears Old Evaluation Data

The macro clears existing entries in the "Evaluation" worksheet, starting from row 2 onward. This ensures no old calculations interfere with new evaluations.

3. Loops Through Mentors and Mentees

The macro iterates through all mentors (rows in "Mentor Input") and then, for each mentor, loops through all mentees (rows in "Mentee Input") to compute a compatibility score between the pair.

4. Evaluates Compatibility for Each Mentor-Mentee Pair

For each mentor-mentee pair, the macro computes a compatibility score based on five weighted criteria:

a. Industry Match (30%)

If the mentor's and mentee's industries match (case-insensitive), the score increases by 30% of the total score.

b. Skills vs. Goals Match (30%)

The mentor’s skills (comma-separated) are checked against the mentee's goals (search for skills as substrings in goals).
If at least one skill matches, the score increases by another 30%.

c. Communication Match (15%)

If the mentor's and mentee's communication styles match, the score increases by 15%.

d. Availability Match (15%)

If their availability matches, the score increases by 15%.

e. Personality Match (10%)

If their personalities match, the score increases by 10%.

5. Writes Results to the "Evaluation" Sheet

For each mentor-mentee pair, the following is written in the "Evaluation" sheet:

Column A: Mentor's identifier (assumed in column 1 of the "Mentor Input").
Column B: Mentee's identifier (assumed in column 1 of the "Mentee Input").
Column C: Rounded compatibility score (percentage).

After writing the data, the macro increments the row counter for logging the next pair.

Summary

In short:

The macro compares mentors and mentees from two input worksheets.
It calculates compatibility scores based on criteria like industry, skills, communication, availability, and personality.
Results are stored in the "Evaluation" sheet as mentor ID, mentee ID, and the compatibility score (%).

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr