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!
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,076
Office Version
2019
Platform
Windows
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
 

srosk

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

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
Hmm?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
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
 

srosk

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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,974
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,082,603
Messages
5,366,575
Members
400,904
Latest member
ndaines meriabi

Some videos you may like

This Week's Hot Topics

Top