Code to find cells that contain a constant preceded by = sign

user_18180817 0
Viewed 63

Any suggestions for Excel vba code that will identify cells that contain values but no function. The catch being that the values I'm searching for are preceded by an "=" sign.

I often have to review other people's financial spreadsheets and knowing which cells contain the actual inputs (assumptions) is key. I can easily find cells with just a constant input with a simple:

Selection.SpecialCells(xlCellTypeConstants, 1).Select

The problem is some users have a habit of inputing numbers preceded by an = sign and these cells are treated by excel as containing a function. I know I can search for cells with functions too, but I only want to find the ones that don't actually have a function, but are input starting with an = sign.

In other words, instead of inputing 1000 in a cell the user inputs =1000. It is only these types of cells I am trying to highlight.

Any suggestions on how I can identify these cells?

vba excel
2 Answers

Exactly this "The problem is some users have a habit of inputing numbers preceded by an = sign" is your problem. Stop people having this habit!

  1. First it is a stupid habit because it slows everything down because Excel has to evaluate those cells that contain something like =1000 to find out the value and
  2. second those cells actually contain a formula and not a value. So there is no built in way to distinguish them.

Those people are just using Excel wrong.

There is only one workaround I can imagine and that is looping through all cells that contain a formula, remove the = sign and check if the rest of the "formula" is numeric IsNumeric(). If so replace the formula by its value.

So for example if the cell is =1000 then 1000 is numeric and it will replace it by the value 1000. If the cell is =1000*2 then 1000*2 is not numeric because it contains a * sign and it will keep the formula.

Note that this workaround is just fighting symptoms but not fixing the root cause. The root cause is those people using Excel wrong! And you should always fix the causes not fight the symptoms.

Option Explicit

Public Sub ReplaceFormluasWithValueIfNoCalculation()
    Dim AllCellsWithFormulas As Range
    On Error Resume Next  ' next line errors if no formulas are found, so hide errors
    Set AllCellsWithFormulas = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0  ' re-enable error reporting
    
    If Not AllCellsWithFormulas Is Nothing Then
        Dim Cell As Variant
        For Each Cell In AllCellsWithFormulas.Cells  ' loop through all formulas
            Dim ExtractedValue As String  ' extract formula right of the `=` sign
            ExtractedValue = Right$(Cell.Formula, Len(Cell.Formula) - 1)
            ' check if extracted formula is only a value, if so replace formula with value
            If IsNumeric(ExtractedValue) Then
                Cell.Value = ExtractedValue
            End If
        Next Cell
    End If
End Sub

Even the above can be done, I recommend fixing the root cause.

user_32196130

Assuming those inputs only contain number you could use a regex for that and ensure the value only contain "=" and numbers you'll have a code like this :

    Dim RegEx As Object, MyString As String
    Set RegEx = CreateObject("VBScript.RegExp")
    MyString = cell.formulas

    With RegEx
      .Pattern = "=*[0-9., ]+"
    End With

    RegEx.test(MyString) 'True if MyString is a number false if it's a function

You'll need to activate "Microsoft VBScript Regular Expression" though.

user_161952130
Related