CODEWITHSUNDEEP
excelvba
Senzar
Senzar

Reputation: 69

VBA: Find occurrences based on other column's value

First, I would like to know if the letter "A" is included in Column A, and secondly if at least one of the occurences has a 1 in Column B.

Column A   |  Column B
   A       |     0
   B       |     1
   A       |     1
   C       |     0
   A       |     0

With my poor skills I can barely know if there is such value in the column.

Set Obj = Sheets("Sheet 1").Range("Column A")
    If Not IsError(Application.Match("A", ObjColumn, 0)) Then MsgBox("There is at least one occurrence")
        If Application.Vlookup("A", ObjTable, 2, False) = 1 Then MsgBox("At least one A has 1 as value")

Unfortunately, with Application.Vlookup I can only explore first appearance's value.

I have done some research but I have just found excesively complicated codes for such a simple issue.

Thank you in advance!

Upvotes: 0

Views: 379

Answers (2)

Senzar
Senzar

Reputation: 69

Thank you @user3598756

Your suggestions helped me to figure out a solution to my needs, as I have a third column which empty would also activate the code.

Column A   |  Column B  |  Column C
   A       |     0      |    ""
   B       |     1      |    0
   A       |     0      |    1
   C       |     1      |    ""
   A       |     0      |    ""

Below is part of the code:

    Set Obj1 = Sheets("Sheet 1").Range("Table[Column A]")
    Set Obj2 = Sheets("Sheet 2").Range("Table[Column B]")
    Set Obj3 = Sheets("Sheet 3").Range("Table[Column C]")
        If Not IsError(Application.Match("A", Obj1, 0)) Then
            If Application.CountIfs(Obj1, "A", Obj2, "1") Or Application.CountIfs(Obj1, "A", Obj3, "<>") > 0 Then MsgBox ("At least one occurrence has either an 1 in B or an empty field in C.")
End If

Thanks a lot!

Upvotes: 0

user3598756
user3598756

Reputation: 29421

you could use WorksheetFunction.CountIf() and WorksheetFunction.CountIfs()

Sub main()
    With Sheets("Sheet 1") '<--| reference your sheet
        If Application.WorksheetFunction.CountIf(.Columns(1), "A") > 0 Then
            MsgBox ("There is at least one occurrence")
            If Application.WorksheetFunction.CountIfs(.Columns(1), "C", .Columns(2), 1) > 0 Then MsgBox ("At least one A has 1 as value")
        End If
    End With
End Sub

or, if you have headers on first row, you could use AutoFilter() and Find() methods:

Option Explicit

Sub main()
    With Sheets("Sheet 1") '<--| reference your sheet
        With Intersect(.Range("A:B"), .UsedRange) '<--| reference its columns A and B used cells
            .AutoFilter Field:=1, Criteria1:="A" '<--| filter referenced cells on its 1st column (i.e. column "A") with value "A"
            If Application.WorksheetFunction.Subtotal(103, .Resize(, 1)) > 1 Then '<--| if any cell filtered other than header
                MsgBox ("There is at least one occurrence")
                If Not .Resize(.Rows.count - 1, 1).Offset(1, 1).SpecialCells(xlCellTypeVisible).Find(what:=2, LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then MsgBox ("At least one A has 1 as value") '<--|search 2nd column filtered cells for "1")
            End If
        End With
    End With
End Sub

Upvotes: 1

Related Questions