How to determine if a cell formula contains Constants?

debugcn Published at Dev

Scheballs

When debugging or Quality Checking an Excel report at work I have found that the problem was because of text being hard coded inside a formula. I have heard this as being a Constant and Formula mixed cell.

Here are examples of what I see.

Constant =100

Constant =Facility

Formula cell =INDIRECT(ADDRESS(5,MATCH($A7&$B7&C$2,Data!$4:$4,0),,,$A$2))

Mixed cell =INDIRECT("Data!"&ADDRESS(5,MATCH($A7&$B7&C$2,Data!$4:$4,0)))

"Data!" is the Constant in the mixed cell, in this case the sheet name. If that sheet name ever changed, the formula would break. I have found and am using two conditional formats to highlight cells that are Constants and those that are formulas using this "Identify formulas using Conditional Formatting". I need to come up with a way to format those cells which contain these Constants inside of formulas.

I have found this question and tried using =IF(COUNT(SEARCH(CHAR(34),A1,1)),TRUE,FALSE) and FIND() to see if I could check if a cell had double quotes inside of it, but the SEARCH() returns FALSE since it is looking at the cells value and not it's contents. It returns TRUE if the cell contains "Constant" but if it is a formula it returns FALSE, such as if the cell contains ="Constant".

How can I find Constants inside formulas across a whole worksheet or workbook?

EDIT*

Thanks to Sidd's code below I have made a function in a module I can use in conditional formatting to at least highlight cells that contain quotes inside the cells.

Function FormulaHasQuotes(aCell)

  If InStr(1, aCell.Formula, """") Then
      FormulaHasQuotes = True
  Else
      FormulaHasQuotes = False
  End If

End Function

FormulaHasQuotes Conditional formatting pic

Siddharth Rout

Let's say your sheet looks like this.

enter image description here

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim aCell As Range, FRange As Range

    '~~> Set this to the relevant sheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find all the cells which have formula
        On Error Resume Next
        Set FRange = .Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If Not FRange Is Nothing Then
            '~~> Check for " in the cell's formula
            For Each aCell In FRange
                If InStr(1, aCell.Formula, """") Then
                    Debug.Print "Cell " & aCell.Address; " has a constant"
                End If
            Next
        End If
    End With
End Sub

When you run the above code, you get this output

Cell $A$2 has a constant
Cell $A$5 has a constant

Note: I have shown you how to do it for a sheet, i am sure you can replicate it for all sheets in a workbook?

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-06-22

Comments

0 comments

From Dev

Related Related

Article