MULTIPLE worksheet.change

jeanbot · Jan 9th 2018

I would like to apply worksheet.change to more than one column in an excel worksheet.

The code I have:

Private Sub Worksheet_Change(ByVal Target As Range)

selectedNa = Target.Value
If Target.Column = 6 Then
selectedNum = Application.VLookup(selectedNa, Worksheets("Look Up").Range("F_Product_Type"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
selectedNa = Target.Value
If Target.Column = 7 Then
selectedNum = Application.VLookup(selectedNa, Worksheets("Look Up").Range("F_Material_Type"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End If
End If
End Sub

The first column is working i.e. column 6 - the code does not keep going and apply the validation to column 7.

Where have I gone wrong?

Thank you in advance.

nilem · Jan 9th 2018

Hi jeanbot,
maybe so

Code

Private Sub Worksheet_Change(ByVal Target As Range)
selectedNa = Target.Value
Select Case Target.Column
    Case 6
        selectedNum = Application.VLookup(selectedNa, Worksheets("Look Up").Range("F_Product_Type"), 2, False)
        If Not IsError(selectedNum) Then
            Application.EnableEvents = False
            Target.Value = selectedNum
            Application.EnableEvents = True
        End If
    Case 7
        selectedNum = Application.VLookup(selectedNa, Worksheets("Look Up").Range("F_Material_Type"), 2, False)
        If Not IsError(selectedNum) Then
            Application.EnableEvents = False
            Target.Value = selectedNum
            Application.EnableEvents = True
        End If
    Case Else
        Exit Sub
End Select
End Sub

Display More

MULTIPLE worksheet.change

Participate now!

Share