Extract Excel Formula Errors

Help Activities
, ,
1

Hello,

I would like to extract formula errors via the excel’s “formula check” function for every sheet and run it for a long list of excel files. The results should be populated into a new excel file called “Summary”.

However, I am not sure what went wrong in that section of my workflow and would like to seek for help to solve it. The anchor base is not working as well.

Hope to hear some helpful guidance. Attached is the screenshot and my workflow.

OpenAuraFile with detect error.xaml (63.6 KB)

Snipaste_2022-04-21_19-10-33
Snipaste_2022-04-21_19-10-331041×806 37.7 KB

1 Like
2

Hey @WS_Chai

What I will prefer is not to go with UiAutomation.

You should go with using VBA or interop library.

Hope that helps.

Thanks
#nK

3

Hey Nithin,

Would you mind to share the VBA codes that I can apply in the workflow?
I am not too familiar with the coding language.

1 Like
4

Hey @WS_Chai

Could you please highlight and show in excel what do you want to capture exactly.

Thanks
#nK

5

Hi @Nithinkrishna,

I would like to extract the details of error in cell for every sheets in a list of excel files.
Some excel files may or may not have formula errors, if they have, the nature and no. of errors are different.

Could you advice what is the VBA code that I can apply?

image
image971×530 27.6 KB

1 Like
6

Sure @WS_Chai let me check.

7

Hey @WS_Chai

Check the below once,

Thanks
#nK

8

Hey @Nithinkrishna,

Thanks for sharing. Based on your example, it detects error within a specified range.
For the excel files that I have, the formula errors can appear at any cells and each files have different length depending on the documentations.

I tried to tweak and tailor the vba codes that you shared to cope with the abovementioned scenario (formula errors can appear in any parts within a worksheet).

May I get your advice if I did it correctly?

Sub PopulateErrors()

Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets
       With wks
            Dim myCell as Range            
            For Each myCell In ws.Range("")
               myCell.Formula = "=#DIV/0!"
        Next myCell

      End With
 Next

End Sub

1 Like
9

Hey @WS_Chai

Looks good for me. Hope you ran it and fine ?

Thanks
#nK

10

Hi Nithin,

I still get an error, not sure what’s the problem?

image
image1435×249 15.6 KB

11

its not correct.

  • should be wks instead of ws
  • you cant have an empty range ws.Range(“”)
  • this code doesnt detect where the errors are, it fills in all the cells in the range with =#DIV/0!"

e.g. if i change ws.Range("") to wks.Range("A1:A5") it will work but it just makes each cell in A1:A5 = #Div/0 , so its not detecting any error

image