Forum Discussion
After upgrade the MS Office 365 getting error 2147417848 Method 'Value' of object 'Range' failed
I do not get that error. However, I see that the Worksheet_Change event risks going into an endless loop, because the Change event causes itself to be called again and again due to the fact that the value of the cell that triggered the event to fire is changed within the event itself. This may cause a stack overflow. This may be prevented by adding Application.EnableEvents = False prior to any action in that event that changes a cell, e.g.:
' Existing other code here Else sString2Replace = Target.Text 'removes any instances of a £ symbol For iLoopCount = 1 To Len(sString2Replace) If Mid(sString2Replace, iLoopCount, 1) = "£" Then sString2Replace = Replace(sString2Replace, "£", "") Next Application.EnableEvents = False 'Added by JKP Target.Value = sString2Replace Application.EnableEvents = True 'Added by JKP End If 'Remainder of code
Find attached error msg.
I do not get that error. However, I see that the Worksheet_Change event risks going into an endless loop, because the Change event causes itself to be called again and again due to the fact that the value of the cell that triggered the event to fire is changed within the event itself. This may cause a stack overflow. This may be prevented by adding Application.EnableEvents = False prior to any action in that event that changes a cell, e.g.:
' Existing other code here
Else
sString2Replace = Target.Text
'removes any instances of a £ symbol
For iLoopCount = 1 To Len(sString2Replace)
If Mid(sString2Replace, iLoopCount, 1) = "£" Then sString2Replace = Replace(sString2Replace, "£", "")
Next
Application.EnableEvents = False 'Added by JKP
Target.Value = sString2Replace
Application.EnableEvents = True 'Added by JKP
End If
'Remainder of code- Excellent! You may have to inspect the entire call tree, starting from the Change event, for other areas where cells are modified in code.
- Thanks alot. after this change now it starts working.. Thanks for your help.