Vfrdtyky

i have trouble getting this code to work on the last to columns (T and U)
what can i do to change tis?

code:

   Private Sub Worksheet_ChangeS(ByVal Target As Range) 'column s, structure

    If Intersect(Target, Range("S:S")) Is Nothing Then Exit Sub

    Dim foundVal As Range

    Set foundVal = Sheets("Dropdown").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)

    If Not foundVal Is Nothing Then

        Target = foundVal.Offset(0, 1)

    End If

End Sub



Private Sub Worksheet_ChangeT(ByVal Target As Range) 'column t, component

    If Intersect(Target, Range("T:T")) Is Nothing Then Exit Sub

    Dim foundVal As Range

    Set foundVal = Sheets("Dropdown").Range("D:D").Find(Target, LookIn:=xlValues, lookat:=xlWhole)

    If Not foundVal Is Nothing Then

        Target = foundVal.Offset(0, 1)

    End If

End Sub



Private Sub Worksheet_ChangeU(ByVal Target As Range) 'column U, parameter

    If Intersect(Target, Range("U:U")) Is Nothing Then Exit Sub

    Dim foundVal As Range

    Set foundVal = Sheets("Dropdown").Range("I:I").Find(Target, LookIn:=xlValues, lookat:=xlWhole)

    If Not foundVal Is Nothing Then

        Target = foundVal.Offset(0, 1)

    End If

End Sub

Thanks in advance

There's only one Worksheet_Change event that fires whenever a cell is changed.

Private Sub Worksheet_ChangeS(ByVal Target As Range) would have to be "manually" called from the Worksheet_Change event to work as it's a user defined procedure.

You should delete those three procedures and using something along the lines of:

Private Sub Worksheet_Change(ByVal Target As Range)



    Dim lCol As Long

    Dim foundVal As Range



    Application.EnableEvents = False



    If Not Intersect(Target, Range("S:U")) Is Nothing Then



        If Target.Cells.Count = 1 Then 'Check a single cell is changing.

            Select Case Target.Column

                Case 19 'col S

                    lCol = 1

                Case 20 'col T

                    lCol = 4

                Case 21 'col U

                    lCol = 9

            End Select



            Set foundVal = Worksheets("DropDown").Columns(lCol).Find( _

                Target.Value, LookIn:=xlValues, LookAt:=xlWhole)



            If Not foundVal Is Nothing Then

                Target = foundVal.Offset(, 1)

            End If

        End If

    End If



    Application.EnableEvents = True



End Sub

Although looking at the range you then try and use FIND on you should be able to offset from the Target column and do the find in a single statement rather than three.

Edit:

I've updated the code to include EnableEvents. At the end of your code you change the value of Target which would cause the Worksheet_Change event to fire again. EnableEvents stops that from happening.

Edit 2:

I've updated the code to use a single FIND. The Select Case gives the column to search in the FIND command.

Note:

If your code crashes for any reason you may need to open the immediate window (Ctrl+G) and type in Application.EnableEvents = True as this does not reset when the code stops - you'll just notice that all your event code stops firing.