8192 characters issue - Help please

[gubertu]

Hi all,

we have an Excel file that is used for some countries to report some data. They send me these files every month.

When I receive the file, I save it in my computer without any problem.

However, If I open the file after 1 hour (for example) and I try to make any change in the file, I get the following message:

"One or more formulas in this workbook are longer than the allowed limit of 8192 characters"

I have been reading about this message and It seem to be something complicated to detect where the error is and probably I won´t ever know.

But It is curious that I can save the file at first time and when I open it again I cannot.

Anyone had the same issue?

Thanks for help!

 

[MARK858]

If you can run macro's when the book is open then the code below should pick out the offending cells.

Sub FindFormulas()
    Dim ws As Worksheet, myRng As Range, FndForm As Range
    
    For Each ws In ActiveWorkbook.Worksheets
        Set FndForm = Nothing
        On Error Resume Next
        Set FndForm = ws.Cells.SpecialCells(xlCellTypeFormulas)
        
        If FndForm Is Nothing Then
        Else
            For Each myRng In FndForm.Cells
                If Len(myRng.Formula) > 8000 Then
                    MsgBox ws.Name & " " & myRng.Address
                End If
            Next myRng
        End If
        On Error Goto 0
    Next ws
End Sub

 

[gubertu]

It worked great.

Thanks a lot!