Runtime error 1004 Method 'Range' of object _Worksheet' failed

[Saeva]

Hello,
I am new to working with VBA, and therefore I'm learning on the go. I have a worksheet, in which I need to decide whether an issue should go after logistics, finances or quality, based on a criteria written in another cell. I found this code and edited it to work better for my purposes, but I have stumbled upon a problem. Whenever I type in "Late delivery", at first the macro does what it should and writes the word "Logistics" where I need it to. But, after few seconds window pops up, and it says:
Run-time error '1004': Method 'Range' of object '_Worksheet' failed
Then, if I click on debug or anything else, excel crashes. Could you by any chance help me please?

Private Sub Worksheet_Change(ByVal Target As Range)
                    Dim LastRow As Long
                Dim i As Long
            LastRow = Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To LastRow
        If Range("A" & i).Value = "Late delivery" Then
    Range("B" & i).Value = "Logistics"
        End If
    Next i
End Sub

This is the code in question


Thank you very much

 

[kevin9999]

Add the 2 lines as outlined below

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False    '<< Add this line...
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("A" & i).Value = "Late delivery" Then
            Range("B" & i).Value = "Logistics"
        End If
    Next i
    Application.EnableEvents = True     '<< ...and this line
End Sub

 

[HaHoBe]

Hi Saeva,

to my thought you got the wrong event. Worksheet_Change should trigger when any entry is made - your code would be better suited in a standrad module. Second: your code gets triggered whenever any change on the sheet is made.

Maybe try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub   'only 1 cell changed

If Target.Column = 1 Then
  If Target.Value = "Late delivery" Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = "Logistics"
    Application.EnableEvents = True
  End If
End If
End Sub

Ciao,
Holger

 

[Saeva]

Add the 2 lines as outlined below

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False    '<< Add this line...
    Dim LastRow As Long
    Dim i As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Range("A" & i).Value = "Late delivery" Then
            Range("B" & i).Value = "Logistics"
        End If
    Next i
    Application.EnableEvents = True     '<< ...and this line
End Sub

Thank you so much, it works like a charm.

 

[kevin9999]

Thank you so much, it works like a charm.

Glad we helped & thanks for the feedback

 

[Saeva]

Hi Saeva,

to my thought you got the wrong event. Worksheet_Change should trigger when any entry is made - your code would be better suited in a standrad module. Second: your code gets triggered whenever any change on the sheet is made.

Maybe try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub   'only 1 cell changed

If Target.Column = 1 Then
  If Target.Value = "Late delivery" Then
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = "Logistics"
    Application.EnableEvents = True
  End If
End If
End Sub

Ciao,
Holger

Hello, thank you very much for your quick reply. You are absolutely right, but it came back to bite me just when I combined it with macro to send email whenever it detect one of the departments. Hopefully, it will work with your code, since it is working on it's own.

Thank you once again

 

[HaHoBe]

Hi Saeva,

like I stated above: to me the code you posted is better suited in a standard module being called by a short-cut or button as it will not take into account which cell on the worksheet was changed.

The event you used will have the target (the cell which is monitored, any cell in Column A) and work for the given row. Caveat with my sample supplied above: if a user starts to edit a cell and decides not to make the change the event will trigger anyhow. To avoid the start maybe use codes like these:

Dim mvarOldValue As String
'

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
'only 1 cell changed
'will only allow changes if old value of cell is not Late delivery

With Target
  If .Column = 1 Then
    If .Value <> mvarOldValue Then
      If .Value = "Late delivery" Then
        Application.EnableEvents = False
        .Offset(0, 1).Value = "Logistics"
        Application.EnableEvents = True
      End If
    End If
  End If
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'will save either value of any single cell in Column A or vbNullString
Dim rng As Range

If Target.Count > 1 Then
  mvarOldValue = vbNullString
  Exit Sub
End If

Set rng = Intersect(Target, Columns(1))
If Not rng Is Nothing Then
  mvarOldValue = rng.Value
End If
Set rng = Nothing

End Sub

Ciao,
Holger