Count all VLOOKUP() functions on worksheet

[golzilla]

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

Thank you very much for the help

 

[Guest]

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

 

[J_J]

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

 

[Dave Peterson]

Remove that portion from the code.

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

 

[J_J]

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]

You could convert that suggested code into a function and have a value returned,
or just plop the code into you commandbutton's code:

Option Explicit
Sub CommandButton1_Click()

Dim FoundCell As Range
Dim vlookupCtr As Long
Dim FirstAddress

ActiveCell.Activate

With ActiveSheet
With .Cells

Set FoundCell = .Find("=vlookup(", after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, lookat:=xlPart, _
searchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False)
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
vlookupCtr = vlookupCtr + 1
Set FoundCell = .FindNext(FoundCell)
Loop While Not FoundCell Is Nothing _
And FoundCell.Address <> FirstAddress

End If
End With
End With

MsgBox vlookupCtr

End Sub

 

[J_J]

Thank you Dave, AFAIK this solves the problem totally...