Excel vba Object variable or With block variable not set error

Question

I am coding in excel vba and get error 91 or a Object variable or With block variable not set error when i run my code and I am unsure as to why. I define and set my variables so I don't know what could cause the error. The relevant code is below

  Sub Button5_Click()
Dim i As Integer
Dim Month As Range
Dim Avg As Range
Dim Target As Range
Dim Incorrect As Range

Set Month = Range("J19")
Set Avg = Range("H19")
Set Incorrect = Range("A19")
Set Target = Range("M19")

'Range("A" & Rows.Count).End(xlUp).Row
For i = 0 To 1000
If IsEmpty(Avg) Then

If Month.Find("jan") <> "" Then
Set Target = Range("M19")

The error is on the If Month.Find("jan") <> "" then part of the code.

The full code is here:

Sub Button5_Click()
Dim i As Integer
Dim Month As Range
Dim Avg As Range
Dim Target As Range
Dim Incorrect As Range

Set Month = Range("J19")
Set Avg = Range("H19")
Set Incorrect = Range("A19")
Set Target = Range("M19")

'Range("A" & Rows.Count).End(xlUp).Row
For i = 0 To 1000
If IsEmpty(Avg) Then

If Month.Find("jan") <> "" Then
Set Target = Range("M19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("feb") <> "" Then
Set Target = Range("O19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("mar") <> "" Then
Set Target = Range("Q19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("apr") <> "" Then
Set Target = Range("S19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("may") <> "" Then
Set Target = Range("U19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("jun") <> "" Then
Set Target = Range("W19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("jul") <> "" Then
Set Target = Range("Y19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("aug") <> "" Then
Set Target = Range("AA19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("sep") <> "" Then
Set Target = Range("AC19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("oct") <> "" Then
Set Target = Range("AE19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("nov") <> "" Then
Set Target = Range("AG19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

Else
Set Target = Range("AI19")

If IsEmpty(Target) Then
Incorrect.Value = "X"

End If
End If

Else
If Month.Find("jan") <> "" Then
Set Target = Range("N19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("feb") <> "" Then
Set Target = Range("P19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("mar") <> "" Then
Set Target = Range("R19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("apr") <> "" Then
Set Target = Range("T19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("may") <> "" Then
Set Target = Range("V19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("jun") <> "" Then
Set Target = Range("X19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("jul") <> "" Then
Set Target = Range("Z19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("aug") <> "" Then
Set Target = Range("AB19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("sep") <> "" Then
Set Target = Range("AD19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("oct") <> "" Then
Set Target = Range("AF19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

ElseIf Month.Find("nov") <> "" Then
Set Target = Range("AH19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If

Else
Set Target = Range("AJ19")

If IsEmpty(Target) Then
Incorrect.Value = "X"
End If
End If
End If
Set Month = Month.Offset(1, 0)
Set Incorrect = Incorrect.Offset(1, 0)
Set Avg = Avg.Offset(1, 0)
Set Target = Target.Offset(1, 0)

Next i
End Sub

Any help is greatly appreciated.

Related post which is important to know while working in VBA - What does the keyword Set actually do in VBA?. — RBT
– RBT, Commented Apr 3, 2018 at 12:04

Sean Kuhlman · Accepted Answer · 2015-01-16 19:57:26Z

5

Range.Find returns a Range so you need to evaluate it as an object:

If Not Month.Find("jan") Is Nothing Then
    Set Target = Range("M19")
End If

If you need to work with the returned Range you can use something like this:

Dim foundCell As Range
Set foundCell = Month.Find("jan")

If Not foundCell Is Nothing Then
    Set Target = Range("M19")
End If

The reference for Range.Find is here.

edited Jan 16, 2015 at 19:57

answered Jan 16, 2015 at 19:47

Sean Kuhlman

5023 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kazimierz Jawor Over a year ago

Here some additional references.

timelmer Over a year ago

It's good to note that objects need to be Set, while vars should not. e.g. Set worksheet = Worksheet v.s. x=4

Collectives™ on Stack Overflow

Excel vba Object variable or With block variable not set error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related