abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,507
- Office Version
- 2019
- 2010
- Platform
- Windows
Hello
I'm not sure why suddenly shows "Method 'Range' of object '_Worksheet' failed" error ,sometimes in this line
and sometimes in this
and somtimes there is no error !
I'm so confused how should fix this error!!
I'm not sure why suddenly shows "Method 'Range' of object '_Worksheet' failed" error ,sometimes in this line
VBA Code:
If Not Intersect(Target, Range("E21:E" & Cells(Rows.Count, "E").End(xlUp).Row)) Is Nothing Then
VBA Code:
x = Evaluate("=Match(""" & Target.Offset(, -3) & """ & """ & Target.Offset(, -2) & """ & """ & Target.Offset(, -1) & """,'PriceList'!B1:B" & lr & "&'PriceList'!C1:C" & lr & "&'PriceList'!D1:D" & lr & ",0)")
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x, lr As Long: With Sheets("PriceList"): lr = .Cells(.Rows.Count, 1).End(xlUp).Row: End With
If Not Intersect(Target, Range("E21:E" & Cells(Rows.Count, "E").End(xlUp).Row)) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
x = Evaluate("=Match(""" & Target.Offset(, -3) & """ & """ & Target.Offset(, -2) & """ & """ & Target.Offset(, -1) & """,'PriceList'!B1:B" & lr & "&'PriceList'!C1:C" & lr & "&'PriceList'!D1:D" & lr & ",0)")
If Not IsError(x) Then
With Sheets("PriceList")
If Sheets("INVOICE").Range("G1") = "ss" Then
.Range("E" & x) = .Range("E" & x) - Target.Value
MsgBox ":this item is not existed " & vbLf & .Range("B" & x).Value & " " & .Range("C" & x).Value & " " & .Range("D" & x).Value & " " & .Range("E" & x).Value, vbInformation
ElseIf Sheets("INVOICE").Range("G1") = "pp" Then
.Range("E" & x) = .Range("E" & x) + Target.Value
MsgBox .Range("E" & x).Value
ElseIf Sheets("INVOICE").Range("G1") = "rr" Then
.Range("E" & x) = .Range("E" & x) + Target.Value
MsgBox .Range("E" & x).Value
ElseIf Sheets("INVOICE").Range("G1") = "tt" Then
.Range("E" & x) = .Range("E" & x) - Target.Value
MsgBox .Range("E" & x).Value
End If
End With
Else
MsgBox "ITEM DOES NOT EXIST", vbInformation, ""
End If
Application.EnableEvents = True
End If
If Target.Column = 2 And Target.Row > 20 Then
Target.Offset(, -1).Value = Target.Row - 20
End If
End Sub
I'm so confused how should fix this error!!