search column for Quote, then msgbox

[solidENM]

Hello,
I am trying to get a textbox to show if quotes are found. Column C is the description column, and contains 1/4" oak, 3/4" poplar, etc. if found, i want to call another macro, and if not found, display a messagebox that says "no quotes found".

I have this in a larger macro and i get an error if i run the code sorting all the thicknesses if there arent any descriptions with a thickness. I am hoping to use this to only call the sub macro if there are items with [a single quote] in it.

 

[mumps]

See if this helps:

Sub test()
    If InStr(1, Range("C2"), """") > 0 Then
        MsgBox "Quote found."
    Else
        MsgBox "Quote not found."
    End If
End Sub

 

[solidENM]

Hi Mumps,
No it is not working. It will always show quote not found.

 

[mumps]

I entered 1/4" in cell C2 and when I ran the macro it worked properly. Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[solidENM]

ahh, i gotcha. it is only searching cell C2. how would i modify this to search the entire column C? I put a single quote in one cell and 1/4" in another, but it only shows the quote found message if either is in cell c2.
I am getting errors when i edit the range to C2:C200

 

[mumps]

Try:Sub Test()
    Application.ScreenUpdating = False
    Dim bottomC As Long
    bottomC = Range("C" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("C2:C" & bottomC)
        If InStr(1, rng, """") > 0 Then
        MsgBox "Quote found."
    Else
        MsgBox "Quote not found."
    End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[solidENM]

Thanks again Mumps. This was working, but it would list every instance of quote or no quote. I was able to get it working with the below code. Thank you for your help.


' Macro1 Macro
'


Dim myRangePNP As Range
Dim myCellPNP As Range
Set myRangePNP = Range("d2:d200")


Set myCellPNP = myRangePNP.Find("""", myRangePNP.Cells(1), LookAt:=xlPart)


If Not myCellPNP Is Nothing Then
MsgBox "quotes found"
Else
MsgBox "quotes found" 'value not found
End If



'
End Sub

 

[mumps]

You are very welcome.