VBA for Excel throws "Object variable or no block variable set" when there is no object

Question

In my code, I declared these variables:

Dim Field_Name, Datatype, row As Integer

Then, inside the For loop, I have this code:

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

However, this code generates an "object variable" or with an invalid block variable "runtime error". According to the API, the property Range.Column and Range.row is Long-Only Long. I tried to make the datatype of my variables long, but without success. Looks like VBA expects me to do

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

However, the specified variables are not objects, so this makes the compilation error required by "Object required".

. , , .

Answer 1

, .

, .Find. , .

:

Find , Nothing. - Find . , .Column .row .

Offset :

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

Answer 2

:

    For i = 1 to 1 ' change to the number of sheets in the workbook
    Set oLookin1 = Worksheets(i).UsedRange
    sLookFor1 = "Field Name"
    Set oFound1 = oLookin1.Find(What:=sLookFor1, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

    If Not oFound1 Is Nothing Then
    Field_Name = oFound1.Column
    RRow = oFound1.Row +1

' code goes here

    Else
    Msgbox "Field Name was not found in Sheet #" & i
    End If

    Set oLookin2 = Worksheets(i).UsedRange
    sLookFor2 = "Datatype"
    Set oFound2 = oLookin2.Find(What:=sLookFor2, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

    If Not oFound2 Is Nothing Then
    DataType = oFound2.Column

' code goes here

    Else
    Msgbox "Datatype was not found in Sheet #" & i
    End If
    Next i

Answer 3

:

.Find .

"" , . ...

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

"" "". "" .

, , (, ) .

To rephrase all critical code (guaranteed), your Dim statement is bad. The first two variables are not typed and end as options. Ironically, therefore, the solution I just described works.

If you decide to clear this Dim statement, declare a DataType as an option ...

Dim DataType as variant