Find one cell, check other ocurrences and copy offset in ocurrences. Error 91, block not set

Question

First I opened this question: Check merged cell and compare adjacent to set unique value from compared cells values. Same scenario, Key value at column 6 and value at column 31 but now requirements have changed.

Now I need to search every cell in column 6 and for each occurrence of this search, copy the the value (column 25) from the first occurrence to the others. I mean, if I have five results of searching "KEY_VALUE", I need to copy the value in column 25 associated with the first result of the search to the others results in their correspondent cells in column 25.

Problem is, when I run my code I get run time error 91: object variable or with block variable not set even when I do have a With block.

I was reading Object variable or with block not set - but in my case, I'm trying to assign a string variable with the result of the search not a range. Here's my code:

Sub CopyUUID()

Dim lRow As Long
Dim rng As Range
Dim searchResult As Range
Dim ws As Worksheet
Dim strSearch As String
Dim uuid As Variant
Set ws = Sheets(ActiveSheet.Name)

With ws
    lRow = .Range("F" & .Rows.count).End(xlUp).row
    Set rng = .Range(.Cells(3, 6), .Cells(lRow, 6))
    For Each cellchecked In rng.Cells
        If Not IsEmpty(cellchecked.Value) Or Not cellchecked.Value = "" Then
            'strSearch = cellchecked.Value
            Set searchResult = rng.Find(What:=cellchecked.Value, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
            uuid = searchResult.Offset(0, 25).Value 'Gives error "run time error 91: object variable or with block variable not set"
            Do
                Set searchResult = rng.FindNext(After:=searchResult)
                If Not searchResult Is Nothing Then
                    searResult.Offset(0, 25).Value = uuid
                Else
                    Exit Do
                End If
            Loop
        Else
            'If cell is blank, skips execution as no continue exists in VBA
        End If
    Next cellchecked
End With
ExitProgram:
  Exit Sub
End Sub

What could be the problem?

Answer 1

You are only going to want to walk through a unique set of the values and some exit strategy on the Range.FindNext method should be implemented or it may go into an infinte loop.

Sub CopyUUID()

    Dim lRow As Long
    Dim chk As Range, rng As Range
    Dim rslt As Range, addr As String
    Dim ws As Worksheet
    Dim key As Variant, dKEYs As Object

    Set ws = Sheets(ActiveSheet.Name)
    Set dKEYs = CreateObject("Scripting.Dictionary")

    With ws
        lRow = .Range("F" & .Rows.Count).End(xlUp).Row
        Set rng = .Range(.Cells(3, 6), .Cells(lRow, 6))
        For Each chk In rng.Cells
            If CBool(Len(chk.Value2)) And Not dKEYs.exists(chk.Value2) Then
                dKEYs.Add key:=chk.Value2, Item:=chk.Offset(0, 25).Value
            End If
        Next chk
        For Each key In dKEYs
            Set rslt = rng.Find(What:=key, after:=rng(rng.Cells.Count), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            addr = rslt.Address
            Do
                Set rslt = rng.FindNext(after:=rslt)
                If rslt.Address <> addr Then
                    rslt.Offset(0, 25) = dKEYs.Item(key)
                Else
                    Exit Do
                End If
            Loop
        Next key
    End With
ExitProgram:
    Exit Sub
End Sub

The dictionary object maintains a unique list of the first occurrence of each value in column F and the associated UUID value from column AE. Each will always be found at least once so even On Error Resume Next can be discarded (never a bad thing!).

To summarize, the above deals with three issues I found with your code.

1. Corrects a misspelling on the var searchResult. It was used as searResult in at least one place.
2. There is now an exit strategy to get out of the .FindNext once all the additional entries have been found and it loops back to the original.
3. Each entry in column F is only run through once with the substitution in column AE being the value from the first occurrence. The original code would run through every entry in the column so it was performing the Range.Find / Range.FindNext method on the duplcaited values. This is not so much an error as simply an inefficiency but still best if avoided.

Answer 2

You are getting that error ("run time error 91: object variable or with block variable not set") because the code didn't find a match

Hence it is always advisable to check if a match has been found or not. Can you please check which message box do you see when you make the below change?

Set searchResult = Rng.Find(What:=cellchecked.Value, LookIn:=xlValues, _
                   LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                   MatchCase:=False, SearchFormat:=False)

If searchResult Is Nothing Then
    Msgbox "No Match Found"
Else
    Msgbox "Match Found"

    uuid = searchResult.Offset(0, 25).Value
    '
    '~~> Rest of the code
    '
End If

I would recommend seeing This