Reinitilise a range in Excel / VBA

Question

Hello,

In a macro, I'm applying the same treatment in each Worksheets of the file.

I want to determine wich column contains a specfic text (might be different in each WS).

For Each Cell_version In Ws.Range("1:1")
    If Ws.Range(convertir(Cell_version.Column) & "1") = "ICI" Then
        Column_version = convertir(Cell_version.Column)
        Trouve_col = True
        MsgBox (Column_version)
    End If
If Trouve_col = True Then Exit For
Next Cell_version

On the first WS everithing is OK (finding in colum D). In the second WS, if it is after D, it is OK, but if it is a columns 1, B, or C, it doesn't return what I want.

Any idea.

Matthieu

Answer 1

My VBA is a bit rusty, but I always preferred the Find command, since it is the fastest way to search a worksheet, like this perhaps:

Sheets("YourSheet").Select

Cells(1, 1).Select

Set found = Cells.Find(What:="ICI", After:=ActiveCell, LookIn:=xlValues, _
 LookAt:=xlColumn, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
 MatchCase:=False, SearchFormat:=False)

If Not found Is Nothing Then
 found.Activate  
 MsgBox(Selection.Row)
End If