Search for value, display prompt based on results of search.
Results 1 to 10 of 10

Thread: Search for value, display prompt based on results of search.

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    130
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Search for value, display prompt based on results of search.

    I need somehow to search column B for the word Budget. If the word budget is found, display a prompt that has an OK button, and once selected, macro continues.

    Just looking at formulas, before creating code, I tried:

    Code:
    =SUM(COUNTIF(B:B,"Budget"))
    Cells in column B might have a value such as Budget May 2012.

    I've also used the Find command, (=find), but this is case sensitive. Is there anyway to do this without it being case sensitive?

    Thanks!

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,885
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    For VBA

    Code:
    Dim lr as long, i as long
    lr = range("B" & rows.count).end(xlup).row
    For i = 1 to lr 'Change start row as necessary
    If instr(Range("B" & i), "Budget") > 0 then
    Msgbox "OK" 'change this to something else if you want it to display in a cell
    'If you want in a cell, then Range("Cell Address") = "OK"
    End if
    Next i
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular
    Join Date
    Sep 2018
    Posts
    130
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    Thanks Alan! I updated:

    Code:
    If instr(Range("B" & i), "Budget") > 0 then
    
    To:

    Code:
    If instr(Range("B" & i), "Budget") Or instr(Range("B" & i), "BUDGET") > 0 then
    
    This accounts for punctuation. Was there a better way to do this?


    Also, is there a way for the script to just search B:B and display 1 prompt? I do not need the same prompt for each line item (if possible).


    Thank you very much for your help!

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,885
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    Adding the Option Compare as explained here -->https://docs.microsoft.com/en-us/dot...pare-statement
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  5. #5
    Board Regular
    Join Date
    Sep 2018
    Posts
    130
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    Hmm?

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,756
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    Try this, It does not matter if the cell is in uppercase or lowercase

    Code:
    Sub find_Budget()
        Set b = Range("B:B").Find("budget", LookIn:=xlValues, lookat:=xlPart)
        If Not b Is Nothing Then
            MsgBox "Budget word found"
        Else
            MsgBox "Not found"
        End If
    End Sub
    Regards Dante Amor

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,756
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    Quote Originally Posted by srosk View Post
    Thanks Alan! I updated:

    Code:
    If instr(Range("B" & i), "Budget") > 0 then
    
    To:
    Code:
    If instr(Range("B" & i), "Budget") Or instr(Range("B" & i), "BUDGET") > 0 then
    
    This accounts for punctuation. Was there a better way to do this?
    Also, is there a way for the script to just search B:B and display 1 prompt? I do not need the same prompt for each line item (if possible).
    Thank you very much for your help!
    As informative data, in case you require it for some search:

    Code:
        Dim lr As Long, i As Long
        lr = Range("B" & Rows.Count).End(xlUp).Row
        For i = 1 To lr 'Change start row as necessary
            If InStr(UCase(Range("B" & i)), UCase("Budget")) > 0 Then
                MsgBox "OK" 'change this to something else if you want it to display in a cell
                exit sub
            End If
        Next i
    Regards Dante Amor

  8. #8
    Board Regular
    Join Date
    Sep 2018
    Posts
    130
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    Magic! Thanks as always, Dante!

  9. #9
    Board Regular
    Join Date
    Sep 2018
    Posts
    130
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    You are so knowledgeable. Thank you again!!

    Quote Originally Posted by DanteAmor View Post
    As informative data, in case you require it for some search:

    Code:
        Dim lr As Long, i As Long
        lr = Range("B" & Rows.Count).End(xlUp).Row
        For i = 1 To lr 'Change start row as necessary
            If InStr(UCase(Range("B" & i)), UCase("Budget")) > 0 Then
                MsgBox "OK" 'change this to something else if you want it to display in a cell
                exit sub
            End If
        Next i

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,756
    Post Thanks / Like
    Mentioned
    65 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Search for value, display prompt based on results of search.

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •