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 "Thumbs Up" 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 "Thumbs Up" 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 "Thumbs Up" 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!