Help with this macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
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.
 
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
 
Back
Top