SOLVED Enter number and lookup matched subject

I have the following scenario and am stuck on how I need to complete my task at hand.

I have two sheets. The first sheet has three columns. A: is the name of a student B: is the subject C: is the grade
The Second sheet has two columns A: is a number B: is a subject that corresponds to each Column A number

So what I want is to be able to type in a number in the first sheet under Column B and have the input be the subject (math, english, ect)

Not quite sure where to start with this.

any help will be appreciated!

See if you can adapt this basic code (no error handling)

The code *MUST* go into the worksheet module - *NOT* a standard module.
The code assumes a look up table is kept on another sheet ( in this example sheet2) in the range of A1:Bnnn per your post.

Sheet references in the code are to sheet *CODE NAMES*, not worksheet (tab) names. Adjust if needed.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim lastrow As Long
    Dim rngList As Range
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    Set rngList = Sheet2.Range("A1").CurrentRegion
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, Range("B2:B" & lastrow)) Is Nothing Then ' user is in column-B
        If IsNumeric(Target) Then
            Target.Value = Application.WorksheetFunction.VLookup(Target.Value, rngList, 2, False)
        End If
    End If
    
    Set rngList = Nothing

End Sub

This is not the only approach that can be used, but it is simple and does what you asked for.

The code *DOES NOT* go into the the module named "ThisWorkbook" delete the code from that module.

Assuming you have the VB editor open (Alt + F11 keys)

Make sure the Project Explorer pane is in view. If not, press Ctrl + R or go to View in the menu and select it.
Afterwards, look for the sheet name in the project explorer and double click on it to make the code pane active for that worksheet.

Copy and paste the code the code into the code pane.
Adjust any sheet or range references as needed.

As I mentioned, sheet references in my code are to sheet code names

Close the VB Editor (Alt + Q keys)

Re-read my previous post in its entirety, especially regarding the look up table.

@svayl

You get better help if you post an Excel-example of your workbook, without confidential information.

Attached is a copy of your workbook with the code added and the sheet code name adjusted to reference the correct sheet containing the subject codes.

I think you would be much better served by using a *SINGLE* worksheet to contain all of the data and include an extra column for the date (month year). Assuming you will be entering grades or other data you may want to report on, a single sheet will make this task much easier. In addition, the code only needs to go into the one sheet module, otherwise you will have to add it to all sheet modules as you add new sheets.

I've add the code to the two worksheets in your sample workbook.

In Excel 2007+, when you have macros you *MUST* save the file as a .xlsm file type, otherwise Excel will delete the code from the workbook.

Excel Help_840513.xlsm