Excel VBA Find Method for specific column

Question

VBA find method seems to fail when I am trying to search for the value in specific column. This code

Sub TargetR()
Dim CLL As Range
Dim TargetRange As Worksheet
Dim R As Range

Set CLL = ThisWorkbook.Worksheets(1).Range("J29")
Set TargetRange = ThisWorkbook.Worksheets(1)
Set R = TargetRange.Cells.Find(CLL.Value)

If Not (R Is Nothing) Then
    Debug.Print R.Address
Else: Debug.Print "Empty"
End If

End Sub

works perfectly. While the search limited by the column with keyword header fails:

Sub Target()
Dim CLL As Range
Dim TargetRange As Worksheet
Dim targetColumn As Range
Dim sColumn As Range


Dim R As Range

Set CLL = ThisWorkbook.Worksheets(1).Range("J29")
Set TargetRange = ThisWorkbook.Worksheets(1)
Set sColumn = TargetRange.Cells.Find("This Column")
Set targetColumn = sColumn.EntireColumn
Set R = targetColumn.Cells.Find(CLL.Value)

If Not (R Is Nothing) Then
    Debug.Print R.Address
Else: Debug.Print "Empty"
End If

End Sub

Specifying search direction through xlByColunm does not help

Answer 1

Try the code below (explanation inside the code as comments):

Option Explicit

Sub Target()

Dim CLL As Range
Dim TargetRange As Worksheet
Dim sColumn As Range
Dim R As Range

Set CLL = ThisWorkbook.Worksheets(1).Range("J29")
Set TargetRange = ThisWorkbook.Worksheets(1)

Set sColumn = TargetRange.Cells.Find("This Column")
If Not sColumn Is Nothing Then ' <-- make sure Find was successful
    Set R = sColumn.EntireColumn.Find(what:=CLL.Value, LookIn:=xlValues, lookat:=xlWhole)        
    If Not R Is Nothing Then
        Debug.Print R.Address
    Else: Debug.Print "Empty"
    End If
Else ' Find failed to find "This Column"
    MsgBox "Unable to find 'This Column'"
End If 

End Sub

Answer 2

As I figured out, Find method do not work correctly if value that needs to be found contained in merged cell, and Find methoud applied only for the leftmost column, that contain part of that merged cell. For my VBA code to work properly, some additional merge check , and subsequent extension of search area was the answer

If sColumn.MergeCells Then
                Set sColumn = Column.Resize(,Column.MergeArea.Columns.Count)