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)
What I will prefer is not to go with UiAutomation.
You should go with using VBA or interop library.
Hope that helps.
Would you mind to share the VBA codes that I can apply in the workflow?
I am not too familiar with the coding language.
Could you please highlight and show in excel what do you want to capture exactly.
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?
Sure @WS_Chai let me check.
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?
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Dim myCell as Range
For Each myCell In ws.Range("")
myCell.Formula = "=#DIV/0!"
Looks good for me. Hope you ran it and fine ?
I still get an error, not sure what’s the problem?
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
e.g. if i change
wks.Range("A1:A5") it will work but it just makes each cell in A1:A5 = #Div/0 , so its not detecting any error