After upgrade the MS Office 365 getting error 2147417848 Method 'Value' of object 'Range' failed

[Somesh_Jain]

After upgrade the MS Office 365 getting below error in one our VBA based tool which runs on Excel. However it is still working for our colleague who did not upgrade MS office 365. Request you to please help to get it resolved or at least provide some workaround.

Error:-2147417848
Error Description : Method 'Value' of object 'Range' failed

[#2]

That'll prove quite difficult to troubleshoot without the file I'm afraid!

[#3]

@Jan Karel Pieterse : I am attaching the file. you can just put any amount in pound column then press tab key to go to other column then you will receive the same error.

[#4]

The VBA is password protected

[#5]

You can access worksheet by entering the below character.
SST0122BP6002

[#6]

Where can I find the Pound column you refer to please?

You can enable the Debug button on the error message:
- open the file
- open the VBA editor
- expand the project so it asks for the password and enter the password
- Now repeat the actions that lead to the error.

[#7]

Open the excel file
click on Main Menu
click on Assessment Details button
excel will open- click on ctrl home button it will take you to header
Now put any numeric value in column 4 (£££)
it will through the error.

[#8]

@Jan Karel Pieterse 

Find attached error msg.

[#9 · Accepted solution]

@Somesh_Jain 

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

[#10]

After debugging found that getting the error in below code
'Currency Check
Case "$E"
If Target = "" Then
Me.Protect "SST0122BP6002"
Exit Sub
End If

If IsCurrency(Target.Text) = False Then
MSGNotValidCurrency
Target = ""
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
Target.Value = sString2Replace
End If

[#11]

Thanks alot. after this change now it starts working.. Thanks for your help.

[#12]

Excellent! You may have to inspect the entire call tree, starting from the Change event, for other areas where cells are modified in code.