none
Macro String search RRS feed

  • Question

  • I need help.  I am new to macros.

    I have a macro buttons titled 0, a, b, c...z.  When I push the "T" button, I want it to go to the first entry in column A that begins with the letter "T".

    My sheet is alphabetical (numeric alpha) on column A with varying number of blank cells (rows) between letters to fill a page.  In this instance I want to find the first entry in column A beginning with the letter "T" even if there are blank cells in the column.

    Please help.

    Friday, August 9, 2019 10:53 PM

All replies

  • To:  1ms
    Re:  Find names in list

    I have a workbook that will do that; see picture below.
    It uses VBA double-click event code. (3000 rows)

    If you are interested, I will post it up at MediaFire for download.
    (free - limited time offer)

    '---

    Free Excel workbooks and add-ins at MediaFire...
    http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents

    Saturday, August 10, 2019 12:41 AM
  • Hi,

    This is a sample VBA: find the first row where character "a" or "A" is in column A.
    (change A to some letter, as you like)    
    Private Sub btn_A_Click()
        ' --- get the Last/Max Row in column A
        Dim LastRow As Long
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        ' --- search cell value beginning "A"
        Dim r As Long
        Dim firstChar As String
        For r = 1 To LastRow
            ' --- get first character
            firstChar = Left(Cells(r, 1).Value, 1)
            ' --- decide if first character is "a" or "A"
            If (firstChar = "a" Or firstChar = "A") Then
                MsgBox "first entry is Row " & r
                Exit Sub
            End If
        Next
        ' --- reached at last row
        MsgBox "not found"
    End Sub
    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Saturday, August 10, 2019 1:24 AM
  • Hi,

    I'll show a more universal sample which makes VBA code much smaller

    ' --- used in each button_click: argument = capital letter
    Private Sub prc_Find_Letter(ByVal letter As String)
        ' --- get the Last/Max Row in column A
        Dim LastRow As Long
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        ' --- search cell value beginning "A"
        Dim r As Long
        Dim firstChar As String
        For r = 1 To LastRow
            ' --- get first character
            firstChar = Left(Cells(r, 1).Value, 1)
            ' --- decide if first character is "a" or "A"
            If (firstChar = letter Or firstChar = LCase(letter)) Then
                MsgBox "first entry is Row " & r
                Exit Sub
            End If
        Next
        ' --- reached at last row
        MsgBox "not found"
    
    End Sub
    ' ---
    Private Sub btn_A_Click()
        Call prc_Find_Letter("A")
    End Sub
    ' ---
    Private Sub btn_B_Click()
        Call prc_Find_Letter("B")
    End Sub
    ' ---
    Private Sub btn_T_Click()
        Call prc_Find_Letter("T")
    End Sub
    


    Regards,


    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Saturday, August 10, 2019 1:42 AM
  • Assuming you have the similar setup for the buttons (form control command buttons not ActiveX buttons)...

    Then place the following code on a Standard Module like Module1 and assign this macro to all the buttons on the sheet, that's it.

    Sub FindFirstLetter()
        Dim btnCaption As String
        Dim Rng As Range
        
        btnCaption = VBA.Trim(ActiveSheet.Buttons(Application.Caller).Caption)
        
        Set Rng = Range("A:A").Find(what:=btnCaption & "*", lookat:=xlWhole, MatchCase:=False)
        
        If Not Rng Is Nothing Then
            Rng.Select
        Else
            MsgBox "No entry found which stars with letter""" & btnCaption & """.", vbExclamation, "Not Found!"
        End If
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    Saturday, August 10, 2019 5:39 AM