Excel VBA macro: "Object required" in "For Each ... In ..." loop

Question

This is a VBA macro in Excel 2013.

I'm looping through the cells in Sheet 1, Col B. For each cell, I want take its value and search for that in Sheet 2, Col A. If it's found, I want to take the corresponding value in Sheet 2, Col B and place it in the appropriate row in Sheet 1, Col E.

These sheets:

Tagging                 Automatic Categories
B     E                 A     B
hat                     cake  Delicious cake.
cake
arm

Should become:

Tagging                 Automatic Categories
B     E                 A     B
hat                     cake  Delicious cake.
cake  Delicious cake.
arm

Code:

Sub AutoCategorize()
Dim c As Range
Dim searchRng As Range
For Each c In Sheets("Tagging").Range("B6:B500").Cells ' loop through cells to do the lookup based on
    If Not c.Value Is Nothing Then ' if there is something in the cell
        If c.Offset(0, 3).Value Is Nothing Then ' make sure the cell to fill is empty
            With Sheets("Automatic Categories").Range("A2:A500") ' using the cells we're looking up in...
                Set searchRng = .Find(What:=c.Value) ' find it
                If Not searchRng Is Nothing Then ' make sure we've found a thing
                    If Not searchRng.Offset(0, 1).Value Is Nothing Then ' make sure it has a corresponding entry
                        Set c.Offset(0, 3).Value = searchRng.Offset(0, 1).Value ' fill it in
                    End If
                End If
            End With
        End If
    End If
Next
End Sub

My problem, I think, is with my understanding of how Excel-VBA structures the data. The MSDN is really unhelpful in this regard, unfortunately, and I've only managed to piece together a lot of how things work from experimentation.

When I run the code, I get

Run-time error '424': Object required

and the debugger highlights

If Not c.Value Is Nothing Then

Can anyone shed some light on what's causing the error? I'm pretty sure my logic is okay, but as I say I'm not 100% on how to reference cells/how the data structures work.

I'm new to VB and Excel macros so shout if there's a better way to structure things. This is also my first StackOverflow post so please let me know if I've done anything wrong.

Answer 1

The error here is that If Not c.Value Is Nothing is checking if the value contained in cell c is an object, and that that object has not been instantiated.

Since a cell value is a primitive type (really a variant), then the correct check to use is either

If c.Value <> vbNullString

or

If IsEmpty(c)

your later use of Is Nothing, in If Not searchRng Is Nothing is correct, as this is checking if a Range object contains Nothing.

Answer 2

c.value refers to the value in the cell (text, number, date). This will never be an Object. One way to check the value of a cell (even with only spaces) is

If Length(Trim(c.Value)) > 0 Then ...