Home/ Questions/Simple solutions for the object variable or with block variable not set error
Next
Answered
Lou Mouton
  • 10
Asked: 2022-05-12T09:00:10+00:00 In:

Simple solutions for the object variable or with block variable not set error

  • 10

. Advertisement .

..3..

. Advertisement .

..4..

How to check – object variable or with block variable not set? I have the sample detail:

Dim Field_Name, Datatype, row As Integer
Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
 Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
 row = Worksheets(i).UsedRange.Find("Field Name").row + 1
Set Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
 Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
 Set row = Worksheets(i).UsedRange.Find("Field Name").row + 1

While I run it, I found the warning message:

Object variable or with block variable not set

I searched for the key on some websites, but I didn’t get it. I may miss any line or other changes. I appreciate your assistance!

block variable not set
Share

2 Answers

  1. Best Answer
    Expert

    The cause: Your .Find call is having an error.

    Solution: Adding “Set ” to that line will resolve the problem. i.e…

    Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column

    If don’t add “Set” you are trying to assign “nothing” to a variable. “Nothing” can only be assigned to an object.

    • 21

  2. This is an old question but I would like to answer it.

    This error occurred to me while using .Find, and I also experienced it. This is the question I asked myself.

    I came up with a simple solution:

    If Find cannot find the string specified, it returns Nothing. This error will occur if you call anything after Find. This error will be thrown by .Column and .row.

    In my case, I needed a Offset for the found cell. This was how I solved it: 

    Set result = Worksheets(i).Range("A:A").Find(string)
 If result Is Nothing Then
 'some code here
 ElseIf IsEmpty(result.Offset(0, 2)) Then
 'some code here
 Else
 'some code here
 End If
    • 8

Leave an answer

You must login to add an answer.

Need An Account,