Disable Run-Time Errors "Application.DisplayAlerts = False" Not Working

Long time lurker here, hoping to finally get some help on a lot of issues I'm hoping I did my due dilligence and have exhausted ever search possible, but if not I'm sorry in advance.

The following code allows me to have a drop down list that fills in multiple fields once an option is selected. It works perfectly fine for fillig in one row at a time. However, if I try to fill down a row of information I get a run time error. This isn't an issue since everything gets filled down anyway, I just want to either prevent the error from poping up, or fix the code so there is no longer any error. Any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayAlerts = False
    Dim FindMe As String
    Dim Rng As Range
    If Not Target.Column = 30 Then
        Exit Sub
    Else
        FindMe = Target.Value
        Set Rng = Sheets("Data").Range("Item_Code")
        With Rng
            Set Rng = .Find(What:=FindMe, _
                    After:=.Cells(.Cells.Count), _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)
            If Not Rng Is Nothing Then
                Target.Offset(0, 1).Value = Rng.Offset(0, 1).Value
                Target.Offset(0, 3).Value = Rng.Offset(0, 2).Value
                Target.Offset(0, 5).Value = Rng.Offset(0, 3).Value
                Target.Offset(0, 6).Value = Rng.Offset(0, 4).Value
                Target.Offset(0, 7).Value = Rng.Offset(0, 5).Value
            End If
        End With
    End If
Application.DisplayAlerts = True
End Sub

What error are you getting and where in your code?

Application.Display alerts is working fine, it's just not intended to supress run-time error, so fixing the error would be the way to go.

Probably not the bit giving you an error, but I'd avoid doing this:

Set Rng = Sheets("Data").Range("Item_Code")
        With Rng
            Set Rng = .Find(What:=FindMe

It would be neater to just go with:

With Sheets("Data").Range("Item_Code")
            Set Rng = .Find(What:=FindMe

Another note,

When dealing with worksheet change events (or selection change events), it is best to also check the target.count. If a user selects more than one cell and does a paste or a clear or types a value and hits CTRL+ENTER, when you try to set FindMe to Target.Value, you'll get an error.

And using AndrewR's code will probably get rid of your error. Since you are using a With Rng, and then you change the Rng to the results of a search, I bet this would cause issues.

As far as error trapping is concerned, I would recommend reading up on proper methods. If there is even the slightest chance your code will produce an error, it is usually best to trap and handle the error, rather than just throw Run-Time errors at the user. Even a simple Catch-All of "On Error Goto ErrHandler" at the beginning of the code and then a MsgBox to display the error number and description can make things less alarming to the user.

http://www.cpearson.com/excel/errorhandling.htm

Originally Posted by Whizbang

And using AndrewR's code will probably get rid of your error. Since you are using a With Rng, and then you change the Rng to the results of a search, I bet this would cause issues.

Sorry, I know this one is solved, but I just wanted to add that I was fairly sure that although the bit of code I pointed out grates a bit I don't think it actually produces an error.

I was fairly sure, from something I'd tried in the past, that when you start a With block Excel remembers the object and you can't change it within the With. This little bit of code shows that Excel does track these things separately:

Sub test()

Dim r As Range

Set r = Range("A1")

With r
  Set r = Range("B2")
  MsgBox .Address & "\" & r.Address
End With

End Sub

After playing with it some more, the looping solution needed some work to account for the fact that the user could select multiple columns that might include column 30.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim FindMe As String
    Dim Rng As Range
    Dim Cll As Range

    If Not Intersect(ActiveSheet.Cells.Columns(30), Target.Cells) Is Nothing Then
        For Each Cll In Intersect(ActiveSheet.Cells.Columns(30), Target.Cells)
            FindMe = Cll.Value
            With Sheets("Data").Range("Item_Code")
                Set Rng = .Find(What:=FindMe, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
                If Not Rng Is Nothing Then
                    Cll.Offset(0, 1).Value = Rng.Offset(0, 1).Value
                    Cll.Offset(0, 3).Value = Rng.Offset(0, 2).Value
                    Cll.Offset(0, 5).Value = Rng.Offset(0, 3).Value
                    Cll.Offset(0, 6).Value = Rng.Offset(0, 4).Value
                    Cll.Offset(0, 7).Value = Rng.Offset(0, 5).Value
                End If
            End With
        Next Cll
    End If

End Sub

Andrew-R / Whizbang,

You guys are amazing, thank you so much. It all works perfectly now I have much to learn.

All kudos goes to Whizbang, all I did was moan about a minor bit of coding

Glad you're sorted, though.

Yeah, I was a little shocked when I ran his original code and didn't get an error or have some wierd results. Still, having the same variable perform two different functions at the same time will just lead to confusion.