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?

CodePudding user response：

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.

CodePudding user response：

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.