Help with this macro

[Guest]

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

 

[Bob Phillips]

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)

 

[Don Guillett]

Might have been grants vs Grants. Sheet not necessary if on that sheet, etc.

Private Sub Worksheet_Activate()
Rows.Hidden = False
Set rng = Range("a9:a100")
r1 = rng.Find(".").Row
r2 = rng.Find(UCase("FUNDING COUNCIL GRANTS")).Row - 3
Rows(r1 & ":" & r2).Hidden = True
Cells(1, 1).Select
End Sub

 

[Guest]

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.

 

[saubhagyam]

Sub COPYSHEET()
'
' COPYSHEET Macro
' COPY
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Sheets("CALCULATOR").Select
Sheets("CALCULATOR").Copy Before:=Sheets(5)
Range("c3:d3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G3:i3").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B6:i7").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("k17").Select
Range(Selection, Selection.End(xlDown)).Select
Range("k17:k232").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("r17").Select
Range(Selection, Selection.End(xlDown)).Select
Range("r17:r232").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("CALCULATOR (2)").Name = Mid(Range("c3:d3"), 9, 14)
Range("c3:d3").Select
Range("A17:A232").EntireRow.Hidden = True
Dim R1 As String, R2 As String
R1 = Range("t6").Value
R2 = Range("t7").Value
Rows(R1 & ":" & R2).EntireRow.Hidden = False
End Sub
Resolve the error

 

[saubhagyam]

Please go through this and resolve the programme so as to run smoothly.