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

[srosk]

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:

=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!

 

[alansidman]

For VBA

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

 

[srosk]

Thanks Alan! I updated:

[COLOR=#333333]If instr(Range("B" & i), "Budget") > 0 then
[/COLOR]

To:

[COLOR=#333333]If instr(Range("B" & i), "Budget") Or [/COLOR][COLOR=#333333]instr(Range("B" & i), "BUDGET")[/COLOR][COLOR=#333333] > 0 then
[/COLOR]

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!

 

[alansidman]

Adding the Option Compare as explained here -->https://docs.microsoft.com/en-us/do...reference/statements/option-compare-statement

 

[srosk]

Hmm?

 

[DanteAmor]

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

Sub find_Budget()
    Set b = Range("B:B").Find("budget", LookIn:=xlValues, [COLOR=#ff0000]lookat:=xlPart[/COLOR])
    If Not b Is Nothing Then
        MsgBox "Budget word found"
    Else
        MsgBox "Not found"
    End If
End Sub

 

[DanteAmor]

Thanks Alan! I updated:

[COLOR=#333333]If instr(Range("B" & i), "Budget") > 0 then
[/COLOR]

To:

[COLOR=#333333]If instr(Range("B" & i), "Budget") Or [/COLOR][COLOR=#333333]instr(Range("B" & i), "BUDGET")[/COLOR][COLOR=#333333] > 0 then
[/COLOR]

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:

    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([B][COLOR=#ff0000]UCase[/COLOR][/B](Range("B" & i)), [B][COLOR=#ff0000]UCase[/COLOR][/B]("Budget")) > 0 Then
            MsgBox "OK" 'change this to something else if you want it to display in a cell
            [B][COLOR=#ff0000]exit sub[/COLOR][/B]
        End If
    Next i

 

[srosk]

Magic! Thanks as always, Dante!

 

[srosk]

You are so knowledgeable. Thank you again!!

As informative data, in case you require it for some search:

    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([B][COLOR=#ff0000]UCase[/COLOR][/B](Range("B" & i)), [B][COLOR=#ff0000]UCase[/COLOR][/B]("Budget")) > 0 Then
            MsgBox "OK" 'change this to something else if you want it to display in a cell
            [B][COLOR=#ff0000]exit sub[/COLOR][/B]
        End If
    Next i

 

[DanteAmor]

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