MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to find if certain number appears in formula text

Posted by Derek on February 13, 2002 1:20 AM

Hi there,

Please can someone help me with macro code for this:

A1:A500 is a series of different six digit numbers
D1:M80 contains complex formulas. Each six digit number in col A should appear in the text of one of the formulas, but only once. The problem is that not all numbers have been included.

I need macro code that will:
look at the first number in A1 and if this number is in any formula text in D1:M80 will insert either "found" or "missing" against that number in column B. Then proceed to look at the number in A2 and so on.

Any help would be much appreciated.


Posted by Bassanio on February 13, 2002 4:11 AM

Dim cell As Range, find As Range
For Each cell In [A1:A500]
Set find = [D1:D80].find(cell.Value)
If find Is Nothing Then
cell(1, 2).Value = "missing"
cell(1, 2).Value = "found"
End If

Posted by Derek on February 13, 2002 4:22 AM

Re: Thanks very much Bassanio - it works brilliantly