Exel run time error 13 [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi All,

Please can you trie and help, I have a error message in my code.

The error message comes in at this line.

If cell.Value = 4 And cell.Offset(0, 1).Value <> "C" Then

Total code

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("T12:T111")) Is Nothing Then Exit Sub
Set MR = Range("T12:T111")
For Each cell In MR
If cell.Value = 4 And cell.Offset(0, 1).Value <> "C" Then
Range(cell.Offset(0, -14), cell).Copy
Sheets("Cash flow").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
cell.Offset(0, 1).Value = "C"
cell.Offset(0, 1).Font.ColorIndex = 2
End If
Next
Application.CutCopyMode = False
End SubThanks in advance for the help!!!!

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Two things.
How you get that error you get would have helped. Just looking at the code what struck to me is that you are updating the cell in the code. This would put you in a loop. You should disable the event before updating the cell and then enable it back

Some thing like this
Private Sub Worksheet_Change(ByVal Target As Range) 
   If Intersect(Target, Range("T12:T111")) Is Nothing Then Exit Sub 
   Set MR = Range("T12:T111") 
   Application.EnableEvents = False 
   For Each cell In MR 
      If cell.Value = 4 And cell.Offset(0, 1).Value <> "C" Then 
         Range(cell.Offset(0, -14), cell).Copy 
         Sheets("Cash flow").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial 
         cell.Offset(0, 1).Value = "C" 
         cell.Offset(0, 1).Font.ColorIndex = 2 
      End If 
   Next 
   Application.EnableEvents = True 
End Sub


How ever this may not resolve your issue as there is not much information. If you still have issue, post a sample book with the macro and sample data on some shared site and post back the link to the file back here. Also do mention what error you get and what you do to get that issue.
Hi, Thanks for the help, here are the code I got from a nother site SOLVED!!

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range, MR As Range
If Not (Intersect(Target, Range("T12:T111")) Is Nothing) Then
Set MR = Range("T12:T111")
For Each cell In MR
If cell.Value = 4 And cell.Offset(0, 1).Value <> "C" Then
Range(cell.Offset(0, -14), cell).Copy
Sheets("Cash flow").Range("E" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial
cell.Offset(0, 1).Value = "C"
cell.Offset(0, 1).Font.ColorIndex = 2
End If
Next cell
Application.CutCopyMode = False
End If
Set cell = Nothing
Set MR = Nothing
End Sub
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Thanks for you feed back as well as solution. Cheers!!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!