CODEWITHSUNDEEP
excelvba
Danny
Danny

Reputation: 121

Finding String in Cell VBA

I'm looking to find a cell which contains DPD End. I want it to find the value farthest to the right because my row contains DPD End Jan, Feb, etc., so I'm looking for the most recent one, which will be located in the later columns.

I'm currently seeing an error, even when I add a wildcard:

With ThisWorkbook.Worksheets(1).Rows(1)
    Set b = .Find("DPD End*", LookIn:=xlPrevious)
MsgBox b.Column
End With

Upvotes: 1

Views: 86

Answers (1)

Vincent
Vincent

Reputation: 508

Instead of using LookIn which specifies the type of content in looked cells, you should use SeachDirection which let you specify the direction of search, as you intended.

Moreover, to look from right to left (and find the farthest/most right column), you need to specify the last cell as starting cell, here using .UsedRange to avoid checking blank cells as much as possible.

Please try following code (not tested):

With ThisWorkbook.Worksheets(1)
    Set b = .Rows(1).Find("DPD End*", After:= .Cells(1,.UsedRange.Columns.Count), _
                                      LookIn:=xlValues, _
                                      SearchDirection:=xlPrevious)
End With
If Not b Is Nothing Then
    MsgBox "Found match in column " & b.Column & "."
Else
    MsgBox "No match found."
End If

NB: If your sheet do NOT start on 1st column, you need to add the first used column index to .UsedRange.Columns.Count.

Upvotes: 1

Related Questions