Help with this macro
Please help with the following macro, sometimes it works but when it doesn't
I get the following error message "Runtime error '91 object variable or With block variable not set". It stops on this row, rw1 = rng.Find(".", , , xlWhole).Row Private Sub Worksheet_Activate() Dim cell As Range Application.ScreenUpdating = False Cells.EntireRow.Hidden = False Set rng = Sheets("income").Range("A9:A100") rw1 = rng.Find(".", , , xlWhole).Row rw2 = rng.Find("Funding Council grants", , , xlWhole).Row Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True Cells(1, "A").Select Application.ScreenUpdating = True End Sub |
Help with this macro
Sounds like the Find failed, Test for it
Private Sub Worksheet_Activate() Dim rng As Range Dim cell As Range Dim rw1 As Long, rw2 As Long Application.ScreenUpdating = False Cells.EntireRow.Hidden = False Set rng = Range("A9:A100") On Error Resume Next Set cell = rng.Find(".", , , xlWhole) If cell Is Nothing Then MsgBox "Dot not fopund" Else rw1 = cell.Row Set cell = Nothing Set cell = rng.Find("Funding Council grants", , , xlWhole) If cell Is Nothing Then MsgBox "Grants not found" Else rw2 = cell.Row On Error GoTo 0 Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True Cells(1, "A").Select End If End If On Error GoTo 0 Application.ScreenUpdating = True End Sub -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "nc" wrote in message ... Please help with the following macro, sometimes it works but when it doesn't I get the following error message "Runtime error '91 object variable or With block variable not set". It stops on this row, rw1 = rng.Find(".", , , xlWhole).Row Private Sub Worksheet_Activate() Dim cell As Range Application.ScreenUpdating = False Cells.EntireRow.Hidden = False Set rng = Sheets("income").Range("A9:A100") rw1 = rng.Find(".", , , xlWhole).Row rw2 = rng.Find("Funding Council grants", , , xlWhole).Row Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True Cells(1, "A").Select Application.ScreenUpdating = True End Sub |
Help with this macro
Thanks for replying to my query. I managed to pin point the problem.
The following statement needs to be changed to rw1 = rng.Find(".", , xlValues, xlWhole) The range contains a function. "nc" wrote: Please help with the following macro, sometimes it works but when it doesn't I get the following error message "Runtime error '91 object variable or With block variable not set". It stops on this row, rw1 = rng.Find(".", , , xlWhole).Row Private Sub Worksheet_Activate() Dim cell As Range Application.ScreenUpdating = False Cells.EntireRow.Hidden = False Set rng = Sheets("income").Range("A9:A100") rw1 = rng.Find(".", , , xlWhole).Row rw2 = rng.Find("Funding Council grants", , , xlWhole).Row Range(Cells(rw1, "A"), Cells(rw2 - 3, "A")).EntireRow.Hidden = True Cells(1, "A").Select Application.ScreenUpdating = True End Sub |
All times are GMT +1. The time now is 10:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com