## Find cells containing formulas with literal (hard coded) values

Sometimes you need to find formulas containing literals (hard coded values) in a workbook.

I found this excellent UDF in David Hager´s Excel Experts E-letter (EEE) on J Walkenbach’s website.

Function CellUsesLiteralValue(Cell As Range) As Boolean If Not Cell.HasFormula Then CellUsesLiteralValue = False Else CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*" End If End Function

---How can I locate cells containing formulas with literal values?---

Use the UDF as your **conditional formatting formula**.

It accepts a single cell as an argument. It returns True if the cell's formula contains an operator followed by a numerical digit. In other words, it identifies cells that have a formula which contains a literal numeric value. You can test each cell in the range, and highlight it if the function returns True.

by John Walkenbach and John Green

### It is a small efficient UDF

The UDF with conditional formatting highlights cells containing formulas with literals, column C. You can download an excel file at the end of this post, if you are interested in this technique.

I have bolded all literal values in the formulas, column E. It seems to work with nested formulas also. Boolean values can be expressed as TRUE/FALSE but also 1/0 in excel. Remember, some formulas are built to have hard coded arguments, like col_index_num in VLOOKUP function.

### I have a large workbook and I am in a hurry!

The authors want you to use it as conditional formatting formula to spot cells containing formulas with literal values, that can be tedious work with a large workbook. I would like to show you how to use the udf to build a list of all cells containing literal values, from an entire workbook.

This picture shows you what my macro below returns, first a new sheet is inserted. It is then populated with links to all cells containing formulas with literals and their corresponding formulas.

I have reused some parts of Rick Rothstein´s macro.

Sub FindLiteralsInWorkbook() Dim C As Range, A As Range, Addresses As String, i As Single Dim cell As Range Sheets.Add Set x = ActiveSheet x.Range("A1") = "Link" x.Range("B1") = "Formula" i = 1 For Each sh In ActiveWorkbook.Worksheets On Error Resume Next Set C = sh.Cells.SpecialCells(xlConstants) If C Is Nothing Then Set C = sh.Cells.SpecialCells(xlFormulas) Else Set C = Union(C, sh.Cells.SpecialCells(xlFormulas)) End If For Each A In C.Areas For Each cell In A If CellUsesLiteralValue(cell) = True Then i = i + 1 x.Hyperlinks.Add Anchor:=x.Range("A" & i), _ Address:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, _ SubAddress:=sh.Name & "!" & cell.Address, _ TextToDisplay:=sh.Name & "!" & cell.Address x.Range("B" & i) = "'" & cell.Formula End If Next cell Next A On Error GoTo 0 Next sh x.Columns("A:E").AutoFit End Sub

### How to use this macro

- Copy UDF CellUsesLiteralValue and macro FindLiteralsInWorkbook
- Go to VB Editor (Alt + F11)
- Go to the menu and click Insert
- Click Module
- Paste UDF and macro to your workbook´s code module
- Return to excel
- Go to tab Developer on the ribbon
- Click "Macros" button
- Click "FindLiteralsInWorkbook" and then click "Run"

### Download excel *.xlsm file

Find cells containing formulas with literal values.xlsm

### Recommended reading

Excel Expert Newsletter Issue No. 20 (July 8, 2001)

Hyperlinks.Add Method (Excel)

### User defined functions udf

The TEXTJOIN function introduced in excel 2016 allows you to concatenate values easily. It also accepts arrays and nested functions. However if your excel version is missing the TEXTJOIN function you can use a User Defined Function, I have all […]

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What is a combination? To explain combinations I must explain the difference between combinations and permutations. Think of permutations as if the order is […]

### Vba

You have quite a few options to choose from if you are looking for a way to create a unique distinct list in your workbook, all demonstrated in this post or on this website. I would choose the advanced filter […]

In a previous post: How to create a dynamic pivot table and refresh automatically in excel I demonstrated how to refresh a pivot table when a sheet is activated. This post describes how to refresh a pivot table when data is […]

Today I´ll show you how to search all excel workbooks (xls, xlsx, xlsm) in a folder for a text string. The macro creates a new sheet containing the search result. Each result contains a link to a cell where the […]

### 12 Responses to “Find cells containing formulas with literal (hard coded) values”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Given that a Boolean defaults to False, you can simplify your CellUsesLiteralValue function like so...

Actually, if you use the single line form of If..Then, then you can reduce the code to a one-liner (the code area will probably line wrap the single line of code due to its length)...

Well, my previous attempt did not come out so well. I do not understand why, though, as I used the vb code tags as suggested just below the "Leave a Reply" heading. I don't know if this will work or not, but I am going to try. Here is the three-line code without any code tags...

Function CellUsesLiteralValue(Cell As Range) As Boolean

If Cell.HasFormula Then

CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"

End If

End Function

I just noticed in my second posting that the "End If" statement is missing.

That actually looks normal, so here is the one-liner version of it (remember, it might line wrap because of its length)...

Function CellUsesLiteralValue(Cell As Range) As Boolean

If Cell.HasFormula Then CellUsesLiteralValue = Cell.Formula Like "*[=^/*+-/()><, ]#*"

End Function

Rick Rothstein (MVP - Excel),

thank you for commenting.

Oscar, you had given me this formula to show items that are in Column B, but not in A.Array formula in cell C2:=INDEX($B$1:$B$5, MATCH(0, COUNTIF($C$1:C1, $B$1:$B$5)+COUNTIF($A$1:$A$5, $B$1:$B$5), 0)) + CTRL + SHIFT + ENTER. Copy C2 and paste it down as far as needed.

I added a condition to this formula :

C2:=INDEX($B$1:$B$11, MATCH(0,IF($E$1:$E$11="AB", COUNTIF($C$1:C1, $B$1:$B$11)+COUNTIF($A$1:$A$5, $B$1:$B$11)), 0))

Oscar, so column E is criteria for column B. It works for that one condition. I would like to add a condition for column A, but it's returning the wrong result. See below.

INDEX($B$1:$B$11, MATCH(0,IF(($E$1:$E$11="AB")* $F$1:$E$5="DB") , COUNTIF($C$1:C1, $B$1:$B$11)+COUNTIF($A$1:$A$5, $B$1:$B$11)), 0))

How would you do this?

Seán,

$F$1:$E$5 is a cell ref to to two columns, is this a typo?

Try this:

INDEX($B$1:$B$11, MATCH(0,IF(($E$1:$E$11="AB"), COUNTIF($C$1:C1, $B$1:$B$11)+IF($F$1:$E$5="DB",COUNTIF($A$1:$A$5, $B$1:$B$11),0)), 0))

That was a typo. Will that make a difference to the formula?

Oscar,

I'm comparing the A column to B.

=INDEX($A$1:$A$11, MATCH(0,IF(($B$1:$B$11="AB"),COUNTIF($E$1:E1, $A$1:$A$11)+IF($D$1:$D$11="DB",COUNTIF($C$1:$C$11, $A$1:$A$11),0)), 0))

A B C D

1 DX 1 MN

2 DX 2 MN

3 DX 3 MN

4 AB 4 MN

5 AB 5 DB

6 AB 7 DB

7 AB

Formula Result 4

6

Result should be 6

This is trickier than I thought.

Seán

Oscar it doesn't make sense that it's returning the incorrect result. You want to i loo return an array of numbers after using the if condition. r

Sean,

I don´t understand. Can you send me an example workbook?

http://www.get-digital-help.com/excel-consulting/

[…] http://www.get-digital-help.com/2015/01/19/find-cells-containing-formulas-with-literal-hard-coded-va… […]