Username label's caption from different sheet based on same Activecell value. Help!

[mrscottjr]

Hello!
I'm looking for some help with applying a caption to a userform label, based on a value found in Sheets(3). I have to use "Sheets(3)" because the sheet name will change frequently.

The userform is called by double clicking on a cell in Sheet(2) and first label caption is

 lblGradDate.Caption= Cells(ActiveCell.Row, "I").Value

The second label's caption is where I'm struggling. Based on the student's name in the active cell, I'm looking to have the caption find that student's graduation date on Sheets(3). It would also be located in column I, but the row number will be different. I'm not sure if there's a way to incorporate an index/match formula or a lookup or if I'm missing something far more simple. Below is what the report would look like for one specific student. The desired caption would take the place of the "-" under Previous Report.

Thoughts?!

 

[My Aswer Is This]

I'm confused:

You said and showed in your image you have the GradeDate caption resolved.

But then you say:

The second label's caption is where I'm struggling. Based on the student's name in the active cell, I'm looking to have the caption find that student's graduation date

And you did not say what the name was of the second label

 

[mrscottjr]

Thanks for the response and sorry for the confusion. Let's assume the student this Userform represents is "John Smith" (which is the value of the ActiveCell). On Sheets(2), John Smith's Graduation Date is 10/12/2020 and is placed in userform under label "lblCurrGradDate". I'm trying to fill the caption for "lblPrevGradDate" for John Smith previous Graduation Date, found on Sheets(3) as "10/12/2019". While John Smith's Graduation dates will be found in Column "I" of both Sheets(2) and Sheets(3), the row in which John Smith's information will likely be different. Does this help?

 

[My Aswer Is This]

Since no once else has tried to help you I will give this another try.
If the active cell value is "George" then where should I search for the value "George"

And if I find "George" in Range("J12") where will I find the date.

Will the date be in Range("K12")

 

[mrscottjr]

Thanks for helping me out!

If the activecell.value is "George" then you'd want to search in Column A on Sheets(3) for the value "George"

So, in finding "George" in Sheets(3) Column A, the graduation date would be in Column I. Therefore, if "George" were to be in Range("A12"), his graduation date would be found in Range("I12").

 

[Norie]

Perhaps.

Dim Res As Variant

    Res = Application.Match(ActiveCell.Value, Sheets(3).Column(1), 0)

    If Not IsError(Res) Then
        lblPrevGradDate.Caption = Sheets(3).Range("I" & Res).Value
    End If

 

[My Aswer Is This]

Try this:

Private Sub CommandButton2_Click()
On Error GoTo M
Dim Lastrow As Long
Lastrow = Sheets(3).Cells(Rows.Count, "A").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
SearchString = ActiveCell.Value
Set SearchRange = Sheets(3).Range("A1:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
lblCurrGradDate.Caption = SearchRange.Offset(0, 8).Value
lblGradDate.Caption = Cells(ActiveCell.Row, "I").Value
Exit Sub
M:
MsgBox "The value " & SearchString & " Could not be found" & vbNewLine & "Or we had some other problem"
End Sub