Excel VBA macro: "Object required" in the "For each ... In ..." loop
This is a VBA macro in Excel 2013.
I go through the cells in Sheet 1, Col B. For each cell, I want to take its value and search in Sheet 2, Col A. If found, I want to take the corresponding value in Sheet 2, Col B and put it on the corresponding 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 my understanding of how Excel-VBA structures data. Unfortunately MSDN is really useless in this regard and I've managed to collect a lot of how things work from experimentation.
When I run the code, I get
Run-time error '424': Object required
and debug highlighting
If Not c.Value Is Nothing Then
Can anyone shed some light on what is causing the error? I'm sure my logic is ok, but as I said, I'm not 100% about how to reference cells / how data structures work.
I'm new to VB and Excel macros, so shout out if there is a better way to structure things. This is also my first StackOverflow post, so please let me know if I did anything wrong.
source to share
The error here is that it If Not c.Value Is Nothing
checks if the value contained in cell c is an object, and that this object was not created.
Since the cell value is a primitive type (indeed an option), the correct usage check is
If c.Value <> vbNullString
or
If IsEmpty(c)
your later use Is Nothing
, in, If Not searchRng Is Nothing
is correct, as this checks if the object contains a Range Nothing
.
source to share