CODEWITHSUNDEEP
excelvbaexcel-formulavlookup
Matt Cash
Matt Cash

Reputation: 1

VBA Vlookup Runtime error when pasting or deleting

Everything seems to work great until you try to delete more than one cell in the column OR past into more than one cell at the same time. Either of these actions cause the same runtime error and refer to the vlookup line in vba.

There are 2 different columns on this main page and if you try these actions on either column, you get the same error. I have referred to the other links about runtime 1004, and that is not the same issue as I have occurring here. 

selectedNa = Target.Value
If Target.Column = 8 Then
    selectedNum = Application.VLookup(selectedNa, Worksheets("OPP Codes").Range("opp_code"), 2, False)

    If Not IsError(selectedNum) Then
        Target.Value = selectedNum
    End If
End If

selectedNa = Target.Value

If Target.Column = 7 Then
    selectedNum = Application.VLookup(selectedNa, Worksheets("OPP Codes").Range("opp_service"), 2, False)

    If Not IsError(selectedNum) Then
        Target.Value = selectedNum
    End If
End If

I simply want to be able to past into multiple rows in the column, or delete multiple cells with out this error occurring.

Upvotes: 0

Views: 82

Answers (1)

Matt Cash
Matt Cash

Reputation: 1

If Target.Cells.Count > 1 Then Exit Sub before the code above. – Scott Craner

Upvotes: 0

Related Questions