select specified row in table

[charly1]

Hi all

Suppose I have an excel table with col 1 already sorted in alphabetical order. How would I write a macro that would open an input box for user to enter a single character, and the table would then jump down to the first row in which col1 begins with that letter.

In my particular case, the data in col1 is actually a list of names, all of them beginning either with Ms or Mrs. I would therefore need the macro to jump to the first cell in which the second word of the data in the cell begins with the specified letter.

Thanks So Much in advance.

 

[Georgiboy]

Here is an idea:

Sub test()
    Dim tbl As ListObject, lc As ListColumn
    Dim rVar As Variant, ib As String
    
    ib = InputBox("Type single letter")
    
    Set tbl = Sheet1.ListObjects("Table1")
    Set lc = tbl.ListColumns(1)

    rVar = Evaluate("=left(TEXTAFTER(" & lc.DataBodyRange.Address(, , , 1) & ","" "",1),1)")
    Application.Goto lc.DataBodyRange(Application.Match(ib, rVar, False)), True
End Sub

 

[charly1]

Here is an idea:

Sub test()
    Dim tbl As ListObject, lc As ListColumn
    Dim rVar As Variant, ib As String
   
    ib = InputBox("Type single letter")
   
    Set tbl = Sheet1.ListObjects("Table1")
    Set lc = tbl.ListColumns(1)

    rVar = Evaluate("=left(TEXTAFTER(" & lc.DataBodyRange.Address(, , , 1) & ","" "",1),1)")
    Application.Goto lc.DataBodyRange(Application.Match(ib, rVar, False)), True
End Sub

Thank you so much. It works beautifully!

Do you mind just showing me, if I would like to use this code to also search a separate column, in which there is no "prefix word", and the letter in the input box should make the jump to the cell in which the FIRST letter of the cell matches the input, how would I adapt the code to manage that.

A second point.

In your coding after it activates, it sets the first visible column on the screen, to the searched column. Considering that in my case the column with the names, is column 14 of the table, this is slightly inconvenient. How would I adapt the code for the first visible column on the screen to remain as it was prior to activating your code, with the code just selecting the the searched for cell?

Thanks again for your help!

 

[Georgiboy]

Maybe like the below:

Sub test()
    Dim tbl As ListObject, lc As ListColumn
    Dim rVar As Variant, ib As String
    
    ib = InputBox("Type single letter")
    
    Set tbl = Sheet1.ListObjects("Table1")
    Set lc = tbl.ListColumns(10) ' change the '10' to the column number you wish to search
    ' for the above line, you could also call the column by name, example: tbl.ListColumns("Header 10")

    rVar = Evaluate("left(" & lc.DataBodyRange.Address(, , , 1) & ",1)") ' i have removed the TEXTAFTER part as (no prefix)
    Application.Goto lc.DataBodyRange(Application.Match(ib, rVar, False)), False ' changed this line, TRUE is now FALSE to stop the scroll
End Sub

 

[charly1]

Tי

Maybe like the below:

Sub test()
    Dim tbl As ListObject, lc As ListColumn
    Dim rVar As Variant, ib As String
   
    ib = InputBox("Type single letter")
   
    Set tbl = Sheet1.ListObjects("Table1")
    Set lc = tbl.ListColumns(10) ' change the '10' to the column number you wish to search
    ' for the above line, you could also call the column by name, example: tbl.ListColumns("Header 10")

    rVar = Evaluate("left(" & lc.DataBodyRange.Address(, , , 1) & ",1)") ' i have removed the TEXTAFTER part as (no prefix)
    Application.Goto lc.DataBodyRange(Application.Match(ib, rVar, False)), False ' changed this line, TRUE is now FALSE to stop the scroll
End Sub

Thanks a mil. You've really helped me out.

 

[Georgiboy]

Happy to help, thanks for the feedback.