Run-time error 91 Object variable [2003]

[Hennie Neuhoff]

Hi. All,
I've been using this code for some time without any problems, suddenly I get
this run time error - Object variable or With block variable not set.
Appreciate your help!
Thank you

Public Sub KryShtBld()
Dim MatchSht, sht As Object

For Each sht In Sheets
If sht.Range("D1") = Range("bladsoek").Value Then
Set MatchSht = sht
Exit For
End If
Next sht
With MatchSht
sht.Activate <------------- Error here
End With
Exit Sub

End Sub

 

[ozgrid.com]

I doubt it ever worked as "sht.Activate" is outside your For each sht loop.

Try;

Sub KryShtBld()
Dim MatchSht As Worksheet, wsht As Worksheet

For Each wsht In Worksheets
If wsht.Range("D1") = Range("bladsoek").Value Then
Set MatchSht = wsht
Application.Goto MatchSht.Range("D1")
Exit For
End If
Next wsht


End Sub

 

[Dave Peterson]

Another version...

Option Explicit
Public Sub KryShtBld()
Dim MatchSht As Worksheet
Dim sht As Worksheet
Dim myVal As String 'is it a string?

myVal = Worksheets("SomeSheetnameHere").Range("bladsoek").Value

Set MatchSht = Nothing
For Each sht In Worksheets
If LCase(sht.Name) = LCase("somesheetnamehere") Then
'don't look here!
Else
If LCase(sht.Range("D1").Value) = LCase(myVal) Then
Set MatchSht = sht
Exit For
End If
End If
Next sht

If MatchSht Is Nothing Then
MsgBox "No match"
Else
sht.Activate
End If

End Sub