IF cell.text NOT found in range, THEN

[samuel.nunn]

I'm trying to run a Macro only if it can NOT find a single cell in a given RANGE with the same text as another particular cell. Here is the code I've come up with, but it's not working right. It's skipping directly to "End If" even when I know for a fact that no cell in the range contains the text in C1.

If Cells.Range("L1:IV1").Text <> Range("C1").Text Then
                'My code here
End If

Thanks,
Sam

 

[Legacy 68668]

Have you tried

If Application.CountIf(Range("L1:IV1"), Range("C1").Text) > 0 Then

 

[Chris Bode]

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CADMINI%7E1%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> well, I suggest you a rather simple approach as :

      Private Sub CommandButton1_Click()<o:p></o:p>
      Dim row As Integer, col As Integer<o:p></o:p>
      row = 1<o:p></o:p>
      col = 12 'start from L<o:p></o:p>
      <o:p></o:p>
      Dim cnt As Integer<o:p></o:p>
      cnt = 0<o:p></o:p>
      <o:p></o:p>
      For col = 1 To Sheet1.Columns.Count<o:p></o:p>
          If Sheet1.Cells(row, col).Value <> "" Then<o:p></o:p>
              If Sheet1.Cells(row, 3).Value = Sheet1.Cells(row, col).Value Then<o:p></o:p>
                  cnt = cnt + 1<o:p></o:p>
              End If<o:p></o:p>
          End If<o:p></o:p>
      Next<o:p></o:p>
      <o:p></o:p>
      If cnt = 0 Then<o:p></o:p>
          'your code should go here<o:p></o:p>
      End If<o:p></o:p>
  End Sub

 

[mikerickson]

If IsNumeric(Application.Match(Range("C1").Text, Range("L1:IV1"), 0)) Then
    MsgBox "its there"
Else
    MsgBox "its not"
End If

 

[ArunaMalla]

Hi,

try this one,

Assumptions:
1. My Source Data Range: A1:A8
( u can use a named range also)
2. B8 contains the value to be searched
Formula:

=IF(ISERROR(VLOOKUP(B8,A1:A8,1,FALSE)),"No match","match")

searches for the specified value, in the range A1:A8.
if a match is found,it displays "match"
else it displays "no match"

Regards,
Aruna

 

[samuel.nunn]

mikerickson,

Thanks! If you're curious, here is the code in its final version:

Range("L1").Select
    
    '''If there is something in L1
    If Range("L1").Text <> "" Then
        '''If we cannot find a duplicate path in our database
        If IsNumeric(Application.Match(Range("C1").Text, Range("L1:IV1"), 0)) Then
            GoTo AlreadyThere
        Else
                Do Until ActiveCell.Text = ""
                    ActiveCell.Offset(0, 1).Select
                Loop
                
                If ActiveCell.Offset(0, -1).Text <> Range("C1").Text Then
                    Range("C1").Copy Destination:=ActiveCell
                End If
        End If
AlreadyThere:
    End If

My last IfThen is a little redundant because the code you provided is technically already searching for that scenario, but I just kept it in there for my own reference.

If you have a few minutes, could you explain how your code is working? How does "IsNumeric" take text into account?

Thanks!
Sam