Hi,

I am tryihg to compare the worksheet names between two worksheets to a list
on the main page. If it does not exist on the main page list the sheet name
is placed on the maintenance page. I get a method range of object worksheet
failed message. I've been trying to find an explanation with not luck. What
does this error mean and how do I fix it?

Also, I've sometimes seen the Set cleared with Set Whatever = Nothing, but I
usually don't see it. What is the best practice and what are the
ramifications if it's done vs not done?

Thanks for your help.

Dim ws As Worksheet
Dim FirstIndex As Long
Dim LastIndex As Long
Dim Nuserow As Long
Dim Lusedrow As Long
Dim CellRange As Range

'get the last cell on Main Page for the sheet names in BD
Lusedrow = MainPagepg.Cells(Rows.Count, "BD").End(xlUp).Row

Set CellRange = MainPagepg.Range("BD13:BD" & Lusedrow)
FirstIndex = Firstpg.Index
LastIndex = Lastpg.Index
ShNotLogged = False

For Each ws In ThisWorkbook.Worksheets
If ws.Index FirstIndex Then
If ws.Index < LastIndex Then
If (IsError(Application.WorksheetFunction.Match(ws.Na me,
CellRange, 0))) Then
With Maintenancepg
'find the next empty cell on maintenance page
Nuserow = Maintenancepg.Cells(Rows.Count,
"B").End(xlUp).Row + 1
'save the missing sheet name
Maintenancepg.Cells(Nuserow, 2).Value = ws.Name
ShNotLogged = True
End With
End If
End If
End If
Next ws
--
Thanks for your help.
Karen53

Try changing this line:
If (IsError(Application.WorksheetFunction.Match(ws.Na me, CellRange, 0))) Then
to
If (IsError(Application.Match(ws.Name, CellRange, 0))) Then
(removed .worksheetfunction)

Application.vlookup will return a value that you can test with iserror().
Application.worksheetfunction.vlookup causes a runtime error if there's an
error.

Are you asking about resetting procedural object variables at the end of the
procedure?

sub somesub()
Dim Something as Object
'''lots of code
Set something = nothing
End sub

If yes, then I don't bother. When the procedure ends, VBA will clean up for me.

On the other hand, if that object variable is persistent:

Option explicit
dim something as object
sub somesub()
set something = someobjecthere
'''lots of code
Set something = nothing
End sub

I'd reset the variable only if I wanted to lose what it pointed to.




Karen53 wrote:

Hi,

I am tryihg to compare the worksheet names between two worksheets to a list
on the main page. If it does not exist on the main page list the sheet name
is placed on the maintenance page. I get a method range of object worksheet
failed message. I've been trying to find an explanation with not luck. What
does this error mean and how do I fix it?

Also, I've sometimes seen the Set cleared with Set Whatever = Nothing, but I
usually don't see it. What is the best practice and what are the
ramifications if it's done vs not done?

Thanks for your help.

Dim ws As Worksheet
Dim FirstIndex As Long
Dim LastIndex As Long
Dim Nuserow As Long
Dim Lusedrow As Long
Dim CellRange As Range

'get the last cell on Main Page for the sheet names in BD
Lusedrow = MainPagepg.Cells(Rows.Count, "BD").End(xlUp).Row

Set CellRange = MainPagepg.Range("BD13:BD" & Lusedrow)
FirstIndex = Firstpg.Index
LastIndex = Lastpg.Index
ShNotLogged = False

For Each ws In ThisWorkbook.Worksheets
If ws.Index FirstIndex Then
If ws.Index < LastIndex Then
If (IsError(Application.WorksheetFunction.Match(ws.Na me,
CellRange, 0))) Then
With Maintenancepg
'find the next empty cell on maintenance page
Nuserow = Maintenancepg.Cells(Rows.Count,
"B").End(xlUp).Row + 1
'save the missing sheet name
Maintenancepg.Cells(Nuserow, 2).Value = ws.Name
ShNotLogged = True
End With
End If
End If
End If
Next ws
--
Thanks for your help.
Karen53

--

Dave Peterson

Thank you, Dave. That was what I was asking.

It's working now. Thanks!
--
Thanks for your help.
Karen53


"Dave Peterson" wrote:

Try changing this line:
If (IsError(Application.WorksheetFunction.Match(ws.Na me, CellRange, 0))) Then
to
If (IsError(Application.Match(ws.Name, CellRange, 0))) Then
(removed .worksheetfunction)

Application.vlookup will return a value that you can test with iserror().
Application.worksheetfunction.vlookup causes a runtime error if there's an
error.

Are you asking about resetting procedural object variables at the end of the
procedure?

sub somesub()
Dim Something as Object
'''lots of code
Set something = nothing
End sub

If yes, then I don't bother. When the procedure ends, VBA will clean up for me.

On the other hand, if that object variable is persistent:

Option explicit
dim something as object
sub somesub()
set something = someobjecthere
'''lots of code
Set something = nothing
End sub

I'd reset the variable only if I wanted to lose what it pointed to.




Karen53 wrote:

Hi,

I am tryihg to compare the worksheet names between two worksheets to a list
on the main page. If it does not exist on the main page list the sheet name
is placed on the maintenance page. I get a method range of object worksheet
failed message. I've been trying to find an explanation with not luck. What
does this error mean and how do I fix it?

Also, I've sometimes seen the Set cleared with Set Whatever = Nothing, but I
usually don't see it. What is the best practice and what are the
ramifications if it's done vs not done?

Thanks for your help.

Dim ws As Worksheet
Dim FirstIndex As Long
Dim LastIndex As Long
Dim Nuserow As Long
Dim Lusedrow As Long
Dim CellRange As Range

'get the last cell on Main Page for the sheet names in BD
Lusedrow = MainPagepg.Cells(Rows.Count, "BD").End(xlUp).Row

Set CellRange = MainPagepg.Range("BD13:BD" & Lusedrow)
FirstIndex = Firstpg.Index
LastIndex = Lastpg.Index
ShNotLogged = False

For Each ws In ThisWorkbook.Worksheets
If ws.Index FirstIndex Then
If ws.Index < LastIndex Then
If (IsError(Application.WorksheetFunction.Match(ws.Na me,
CellRange, 0))) Then
With Maintenancepg
'find the next empty cell on maintenance page
Nuserow = Maintenancepg.Cells(Rows.Count,
"B").End(xlUp).Row + 1
'save the missing sheet name
Maintenancepg.Cells(Nuserow, 2).Value = ws.Name
ShNotLogged = True
End With
End If
End If
End If
Next ws
--
Thanks for your help.
Karen53

--

Dave Peterson