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

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
Thanks Alan! I updated:

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

To:

Code:
[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!
 
Upvote 0
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, [COLOR=#ff0000]lookat:=xlPart[/COLOR])
    If Not b Is Nothing Then
        MsgBox "Budget word found"
    Else
        MsgBox "Not found"
    End If
End Sub
 
Upvote 0
Thanks Alan! I updated:

Code:
[COLOR=#333333]If instr(Range("B" & i), "Budget") > 0 then
[/COLOR]
To:
Code:
[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:

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([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
 
Upvote 0
You are so knowledgeable. Thank you again!!

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([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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top