Finding String in Cell VBA

Question

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

You're mixing up parameters; xlPrevious is not valid for LookIn. Consult the docs. — BigBen
– BigBen, Commented Jul 14, 2021 at 14:39

Vincent · Accepted Answer · 2021-07-14 14:55:08Z

1

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.

edited Jul 14, 2021 at 14:55

answered Jul 14, 2021 at 14:43

Vincent

5083 silver badges16 bronze badges

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

4 Comments

SJR Over a year ago

Would you not need to specify the After parameter too?

BigBen Over a year ago

@SJR i believe if not specified it's the top left cell by default, here A1.

SJR Over a year ago

@BigBen - it is, I just checked the docs, as you advised above!

SJR Over a year ago

There'd be nothing to do here if people did that.

Collectives™ on Stack Overflow

Finding String in Cell VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related