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

Thank you very much for the help

--
golzill
-----------------------------------------------------------------------
golzilla's Profile: http://www.excelforum.com/member.php...fo&userid=1678
View this thread: http://www.excelforum.com/showthread.php?threadid=31986

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

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

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

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

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 wrote:

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

--

Dave Peterson