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

What does Set sColumn = TargetRange.Cells.Find("This Column") give you? you also need to check if sColumn is nothing before assigning it to targetColumn — Siddharth Rout
– Siddharth Rout, Commented May 28, 2017 at 12:51
I just tested the code. it works for me. BTW .Find retains the last setting. Ensure you set all parameters. For exampleLookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False — Siddharth Rout
– Siddharth Rout, Commented May 28, 2017 at 12:55

Shai Rado · Accepted Answer · 2017-05-28 13:50:23Z

2

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

answered May 28, 2017 at 13:50

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ike · Accepted Answer · 2017-07-22 10:47:59Z

0

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)

answered Jul 22, 2017 at 10:47

Ike

12 bronze badges

Collectives™ on Stack Overflow

Excel VBA Find Method for specific column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related