Run-time error 91 Object variable [2003]

  • Thread starter Thread starter Hennie Neuhoff
  • Start date Start date
H

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