When I perform a "Cells.Find" after selecting a column the search unexpectedly leaves the selected column.

Question

I would expect the Find to remain in the column I selected just like when using the 'Find" function in Excel.

'Select first row of data set locations
ActiveWorkbook.Worksheets("Sheet1").Activate
Cells.EntireColumn("C").Select
Set First = Cells.Find(What:=ww_from, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate

Thanks in advance for your help.

Scott

[How to avoid `.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Plagon, Mar 05 '18 at 22:44

score 0 · Accepted Answer · answered Mar 05 '18 at 22:48

0

Cells.Find( is looking at the whole sheet not just what is selected. If you only want to search in column C then use this in place of all you have given:

With ThisWorkbook.Worksheets("Sheet1")
    Set First = .Column("C").Find(What:=ww_from, After:=.Range("C1"), LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False)
End With

answered Mar 05 '18 at 22:48

Scott Craner

148,073
10
49
81

Thanks SC, I have another issue. I declared "First" as a range but when I get to the End With there is nothing in the variable, I need to get the row to define the start of my data range. Thank in advance. Scott – Scott Mar 06 '18 at 16:44
That is a new question and as such needs to have its own post. – Scott Craner Mar 06 '18 at 16:47

score 0 · Answer 2 · answered Mar 05 '18 at 22:50

If you do not want to change Selection, consider:

ActiveWorkbook.Worksheets("Sheet1").Activate
Dim r as Range
Set r = Columns(3).Cells
Set First = r.Find(What:=ww_from, After:=r(1), LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)

When I perform a "Cells.Find" after selecting a column the search unexpectedly leaves the selected column.

2 Answers2