Complie Error Help

Below Code BOMBS at MsgBox line with: Compile Error Sub or Function not
defined;
With the word Search highlighted --- Why?????

Sub Tester()
For Each r In Rows
If r.Hidden = True Then
MsgBox "Row " & Mid(r.Address, 2, Search(":", r.Address) - 2) & " is
hidden."
End If
Next r
End Sub

TIA

I think you want the INSTR function.

You could probably also use

R.Row to get the row number.

Robin Hammond
www.enhanceddatasystems.com

"JMay" <[email protected]> wrote in message
news:xyZId.34843$jn.14460@lakeread06...
> Below Code BOMBS at MsgBox line with: Compile Error Sub or Function not
> defined;
> With the word Search highlighted --- Why?????
>
> Sub Tester()
> For Each r In Rows
> If r.Hidden = True Then
> MsgBox "Row " & Mid(r.Address, 2, Search(":", r.Address) - 2) & " is
> hidden."
> End If
> Next r
> End Sub
>
> TIA
>
>

If you want to use the worksheet function SEARCH, the syntax is

Application.Search

or

Application.WorksheetFunction.Search

VBA has the Instr function that may be useful to you.


On Sun, 23 Jan 2005 22:02:40 -0500, "JMay" <[email protected]> wrote:

>Below Code BOMBS at MsgBox line with: Compile Error Sub or Function not
>defined;
>With the word Search highlighted --- Why?????
>
>Sub Tester()
> For Each r In Rows
> If r.Hidden = True Then
> MsgBox "Row " & Mid(r.Address, 2, Search(":", r.Address) - 2) & " is
>hidden."
> End If
> Next r
>End Sub
>
>TIA
>

I don't think you want to loop over 65536 rows.

Sub Tester()
For Each r In ActiveSheet.UsedRange.rows
If r.Entirerow.Hidden = True Then
MsgBox "Row " & r.row & " is hidden."
End If
Next r
End Sub

As stated by others , Search is a worksheetfunction, not a VBA function.

--
Regards,
Tom Ogilvy

"JMay" <[email protected]> wrote in message
news:xyZId.34843$jn.14460@lakeread06...
> Below Code BOMBS at MsgBox line with: Compile Error Sub or Function not
> defined;
> With the word Search highlighted --- Why?????
>
> Sub Tester()
> For Each r In Rows
> If r.Hidden = True Then
> MsgBox "Row " & Mid(r.Address, 2, Search(":", r.Address) - 2) & " is
> hidden."
> End If
> Next r
> End Sub
>
> TIA
>
>

Thanks Tom, as usual


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> I don't think you want to loop over 65536 rows.
>
> Sub Tester()
> For Each r In ActiveSheet.UsedRange.rows
> If r.Entirerow.Hidden = True Then
> MsgBox "Row " & r.row & " is hidden."
> End If
> Next r
> End Sub
>
> As stated by others , Search is a worksheetfunction, not a VBA function.
>
> --
> Regards,
> Tom Ogilvy
>
> "JMay" <[email protected]> wrote in message
> news:xyZId.34843$jn.14460@lakeread06...
> > Below Code BOMBS at MsgBox line with: Compile Error Sub or Function not
> > defined;
> > With the word Search highlighted --- Why?????
> >
> > Sub Tester()
> > For Each r In Rows
> > If r.Hidden = True Then
> > MsgBox "Row " & Mid(r.Address, 2, Search(":", r.Address) - 2) & " is
> > hidden."
> > End If
> > Next r
> > End Sub
> >
> > TIA
> >
> >
>
>