jk's blog

MS Excel: Moving Cursor to the First Occurence of a String

This works like “find”, but you can restrict the range it searches (by editing the code). You can also switch the loops around so it scans columns before rows.

This code is part of an Excel importer project for Access. The data is kinda weird, and can’t be imported via the normal importer. I’m using FunctionX’s VBA for Excel tutorial as a reference.

Public Sub test()
    import_goto_start ("Customer #")
End Sub

Public Sub import_goto_start(search As String)
    ' moves cursor to the first likely line of data, which is the first
    ' cell of the header row.  Call this before anything else.
    r = 1
    While (r < 20)
        c = "A"
        While (c <> "E")
            With Workbooks(1).Worksheets(1)
                If (.Range(c & r) = search) Then
                    .Range(c & r).Select
                    Exit Sub
                End If
            End With
            c = Chr(Asc(c) + 1)
        r = r + 1
End Sub