0

I'm trying to grab a value from one cell, then in another worksheet find that value, if there is a match return the ID in the column to the left (this all works fine). If there isn't a match I want to create an ID and add that + the value to a table. Problem is when there isn't a match I get the error 'Object variable or With block variable not set'. I'm expecting the .find to return 'nothing' hence the If - Is Nothing statement, don't understand why I keep getting this error, please help!

Dim selected_item As String
Dim found_item_id As String
Dim found_item_id_cell As Range

Range("D3").Select
Do Until IsEmpty(ActiveCell)
    selected_item = ActiveCell.Value
    Set found_item_id_cell = Worksheets("items").Range("B:B").Find(selected_item, LookIn:=xlValues).Offset(0, -1)
        If found_item_id_cell Is Nothing Then
            'nothing found - generate new ID and add record
            Range("G1").Value = "Item not Found"
        Else
            found_item_id = found_item_id_cell.Value
            'something is found - no action
            Range("G1").Value = found_item_id
        End If
    
ActiveCell.Offset(1, 0).Select
Loop

Inputting a 'selected_item' I know doesn't match and expect "item not found" to appear in cell G1, but just get the error. Works fine for values that do match. Debug shows error on the Set found_item_id_cell = Worksheets("items").Range("B:B").Find(selected_item, LookIn:=xlValues).Offset(0, -1)

wbusby1
  • 1
  • 1
  • 3
    You are going to need to test that the `Find` returned something before trying to offset. If the `Find` does not find a match it will return `Nothing` and you cannot offset `Nothing` – Scott Craner Apr 17 '23 at 19:12
  • When using `Find`, *always* specify the `What`, `LookIn`, and `LookAt` parameters (you're missing `LookAt`), and then always test if the find succeeded. – BigBen Apr 17 '23 at 19:15
  • Testing if the find succeded means you can't chain anything after the find call, so the `.Offset(0, -1)` in `Find(...).Offset(0, -1)` is a no-go. Move the `Offset` to inside the `Else`: `found_item_id = found_item_id_cell.Offset(0, -1).Value`. – BigBen Apr 17 '23 at 19:22
  • 1
    @BigBen Got it! Thank you so much, moved the '.offset(0, -1)' down to the Else block and works perfectly, much appreciated – wbusby1 Apr 17 '23 at 19:30

0 Answers0