Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complie Error Help
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complie Error Help
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complie Error Help
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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Complie Error Help
Thanks Tom, as usual
"Tom Ogilvy" wrote in message ... 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fix "complie error:procedures" in Excel? | Excel Discussion (Misc queries) | |||
complie error | Excel Programming | |||
Excel - Visual Basic Complie error | Excel Programming | |||
Complie Error | Excel Programming | |||
Variable produces a complie error | Excel Programming |