Check if Range Contains (n)

    Hi All


    Any help much appreciated here.
    Using VBA, how can I check a range to see if a specific string exist and error if not


    Tried several options myself but cannot tie it down


    i.e.


    Sheet1
    Range B3:B14


    searchvalue= "SampleText"
    msgbox="Error not found"


    Thanks

    Hello,


    You can test following macro :


    Code
    Sub Catch()
Dim res As Variant
res = Application.Match("SampleText", Sheet1.Range("B3:B14"), 0)
  If IsError(res) Then
       MsgBox "No match found"
  Else
       MgsBox "Match found in row " & res
  End If
End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Hi Carim


    Once again, many thanks and great work.
    The macro works great right up to the part where I replace the range data from text to using an array formula.
    I assume this is because there is no value in the cell where the array formula is?


    Is ther a way to have the macro view the "Values" in the range rather than the array formula?
    or
    should I just copy and paste the values from the range to the next column (Hidden) using "Selection.PasteSpecial Paste:=xlPasteValues" and reference this new range C3:C14 instead?

    Thanks

    Hi,


    Not fully sure to understand your constraint about the Array Formula...


    Why don't you attach a sample file ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    This is you wanted,if your search more than > 1 without loop

    Code
    Sub test()
dim x,Txt$
Txt ="Sample Text"
x =Filter(application.Transpose(columns(2).Specialcells(2).value)),Txt,False)
if ubound x>-1 then
   msgbox "Data exists in row " & join(x,Vblf)
Else
  Msgbox "No data to Found"
end if
End Sub

    Hi Carim,


    No worries, I managed to figure it out.
    The range I had set wasnt dynamic so when it changed form the sample range, things were no reported properly. I set a new named range using offset funtion and referenced that.


    Works a treat with this tweak.


    Thanks for the response though.

    Quite glad to hear you could fix your problem ... :wink:


    Thanks for your kind Thanks ... AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    i am sorri edit for my post

    Code
    Sub test()
Dim x, Txt$
Txt = "Sample Text"
x = Filter(Application.Transpose(Columns(2).SpecialCells(2).Value), "Sample Text", True)
If UBound(x) > -1 Then
   MsgBox "Found : " & UBound(x) + 1 & " " & Join(x, Chr(2))
Else
  MsgBox "No data to Found"
End If
End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login