Add worksheet change event to add a pre specified number looking up the corresponding row value

CapG · Jul 28th 2022

I have attached a sample excel file where when a user enters a text say from B3 to B5 Expert, Intermediate or beginner, it should lookup colum M and enter a number in the 'No' column using worksheet change function.

Justin Doward · Jul 29th 2022

Hello CapG

Not sure why you would not just do this by seperating the text into two columns and using the vlookup function native to excel, but this code should work for the text as provided:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B, D:D, E:E")) Is Nothing Then
    Dim rng As Range, c As Range
    Set rng = Range("M3:M6")
        For Each c In rng
            If InStr(c, Target) Then
                Target.Offset(0, 1) = Right(c, 2)
            GoTo Exit_Sub
            End If
        Next c
Exit_Sub:
    End If
End Sub

Display More

CapG · Jul 29th 2022

Thanks you. that worked!

Add worksheet change event to add a pre specified number looking up the corresponding row value

Participate now!

Share

Tags