Thanks Dave. But how do I get the number of cells containing VLOOKUP fomulas
displayed?. The below code fails to display the value Z

Private Sub CommandButton1_Click()
Dim A As Variant
Macro1 ' macro doing the counting
A = Z ' assign the loop value to A
MsgBox ("The number of cells containig VLOOKUP formula is " & A)
End Sub



"Dave Peterson" wrote in message
...
Remove that portion from the code.

J_J wrote:

Hi David,
I find out with your code that
SearchFormat:=
is not recognized with XL2K
any suggestions?
TIA

"David" wrote in message
...
Hi Golzill,
This assumes there is at least two instances of a Vlookup. Z is the
counter
and it will start out in "A1" and look forward from there. It starts
out
with
Z set to 0, but it will go through the loop and end up at the first
"Find"
and count it again.

Sub Macro1()
Range("A1").Select
Cells.Find(What:="=VLOOKUP", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
StartAddress = ActiveCell.Address
Z = 0
Do
Cells.FindNext(After:=ActiveCell).Activate
Z = Z + 1
Loop While ActiveCell.Address < StartAddress
End Sub


"golzilla" wrote:


How can I count all the VLOOKUP() functions on an active worksheet
using
VBA?

Thank you very much for the help!


--
golzilla

------------------------------------------------------------------------
golzilla's Profile:
http://www.excelforum.com/member.php...o&userid=16789
View this thread:
http://www.excelforum.com/showthread...hreadid=319863



--

Dave Peterson