CODEWITHSUNDEEP
excelexcel-2007vba
Jared
Jared

Reputation: 2954

checking if cell value exists in two other sheets

I am trying to track IDs within several different sheets and I want to be able to give a True value for one criteria and false for another. Here is my pseudo code for what I am trying to accomplish.

IF sheet1.A1.value EXISTS AND DOES NOT EXIST IN sheet2.A:A OR sheet3.A:A
THEN RETURN "true"
ELSE RETURN "false"

Upvotes: 0

Views: 11316

Answers (2)

danielpiestrak
danielpiestrak

Reputation: 5439

You could also do this with just an excel formula like this:

=IF(AND(len('Sheet1'!A1)>0 , ISERROR(MATCH('Sheet1'!A1,'Sheet2'!A:A,0)), ISERROR(MATCH('Sheet1'!A1,'Sheet3'!A:A,0))) , "True", "False")

This Reads:

  • If the following three condiitons are met, then True, otherwise False
  • Condition 1: Sheet1's Cell A1 exists
    • Check if the length of any value in it is greater than 0
  • Condition 2: Sheet1's Cell A1 not in Sheet 2's column A
    • Run a match formula to check this
    • If the match returns an error, then it was not found
  • Condition 3
    • Run a match formula to check this
    • If the match returns an error, then it was not found

Upvotes: 1

Siddharth Rout
Siddharth Rout

Reputation: 149287

Try this

Sub Sample()
    Dim SearchString As String

    SearchString = "Blah"

    If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Columns(1), SearchString) > 0 And _
    Application.WorksheetFunction.CountIf(Sheets("Sheet2").Columns(1), SearchString) = 0 And _
    Application.WorksheetFunction.CountIf(Sheets("Sheet3").Columns(1), SearchString) = 0 Then
        '~~> Display relevant message
    Else
        '~~> Display relevant message
    End If
End Sub

Upvotes: 1

Related Questions