## 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)

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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?

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

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