VBA: Checking if a row exists with exact data in multiple columns

[mwl_y]

I'm trying to write some VBA code that will tell me if a row containing exact data exists.
The table (Table1) is formatted as a table with headers, and has 20 columns; the first three columns are what I am concerned with right now.
- Some projects will have multiple associated sites
- Some sites are associated with multiple projects
- Some sites will have multiple pieces of equipment
- The same equipment ID may be used in multiple sites
- Each row will contain only one Project Code, Site, and Equipment ID

Project Code	Site	Equipment ID
XY_ADT	QRT	25A1
XY_ADT	AMZ	25A7
XY_MDF	SLO	12C11
XY_CHG	QRT	60A2
XY_MDF	SLO	12C12

The data in this table will be updated from multiple other tables (example shown below; Table name = Project Code; Headers = Sites; Data = Equipment ID) upon a button click, which I am currently working on. I need the VBA code to be able to check whether or not a row for XY_MDF SLO 12C33 exists, and if not, paste the info into the next available row.

I figured out how to check this in-line with hard coded values, which I'll replace as variables in my VBA code:
=IF((Table1[@[Project Code]]="XY_MDF")*(Table1[@Site]="SLO")*(Table1[@[Equipment ID]]="12C33"),"It's a match!", "Nope")
But I'm not sure how to translate this to VBA and get it to check each row to look for a matching row. I don't need to know which row it's in - just whether or not it exists.

Would using the row/column numbers instead of the header names for Table1 make it any easier?

Any guidance on how to approach this would be greatly appreciated!

 

[Akuini]

@mwl_y, welcome to the forum.

But I'm not sure how to translate this to VBA and get it to check each row to look for a matching row. I don't need to know which row it's in - just whether or not it exists.

Untested, here's one way to do it.

Function check_it(ByVal a As String, ByVal b As String, ByVal c As String) As Boolean
Dim va
Dim i As Long
va = ActiveSheet.ListObjects("Table1").DataBodyRange.Resize(, 3)
For i = 1 To UBound(va, 1)
    If va(i, 1) = a And va(i, 2) = b And va(i, 3) = c Then
        check_it = True
        Exit Function
    End If
Next
End Function

on your "upon a button click" code, use the function like this:

If check_it("XY_MDF", "SLO", "12C33") Then  '<-- replace the values with your corresponding variables, must be string variable
    'do something if true
Else
    'do something if false
End If

 

[mwl_y]

@mwl_y, welcome to the forum.

Untested, here's one way to do it.

Function check_it(ByVal a As String, ByVal b As String, ByVal c As String) As Boolean
Dim va
Dim i As Long
va = ActiveSheet.ListObjects("Table1").DataBodyRange.Resize(, 3)
For i = 1 To UBound(va, 1)
    If va(i, 1) = a And va(i, 2) = b And va(i, 3) = c Then
        check_it = True
        Exit Function
    End If
Next
End Function

on your "upon a button click" code, use the function like this:

If check_it("XY_MDF", "SLO", "12C33") Then  '<-- replace the values with your corresponding variables, must be string variable
    'do something if true
Else
    'do something if false
End If

Thanks Akuini! I'll give this a go in the morning and report back, but I think it'll do the trick!

 

[mwl_y]

I need the VBA code to be able to check whether or not a row for XY_MDF SLO 12C33 exists, and if not, paste the info into the next available row.

Oops, I meant to use XY_MDF SLO 12C33 as an example, not that I'd be checking for that row specifically - but I think my point still got across.

 

[mwl_y]

Thanks Akuini! I'll give this a go in the morning and report back, but I think it'll do the trick!

@Akuini I tested it out and it works! Thank you so much for your help
Just out of curiosity, what is the purpose of DataBodyRange.Resize(, 3)? I tried removing the bit to see what would happen, and it would result in a "Type Mismatch" error, highlighting the For i = 1 To UBound(va, 1) line.

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

Just out of curiosity, what is the purpose of DataBodyRange.Resize(, 3)?

DataBodyRange refers to table data part, excluding the header.
Resize(, 3) means get only the first three columns
And I amend the code a bit to make it faster:

Function check_it(ByVal a As String, ByVal b As String, ByVal c As String) As Boolean
Dim va
Dim i As Long
va = ActiveSheet.ListObjects("Table1").DataBodyRange.Resize(, 3)
For i = 1 To UBound(va, 1)
    If va(i, 1) = a Then         'check 1st column, it it's false then no need to check 2nd column
        If va(i, 2) = b Then     'check 2nd column, it it's false then no need to check 3rd column
            If va(i, 3) = c Then
                check_it = True
                Exit Function
            End If
        End If
    End If
Next
End Function

 

[mwl_y]

You're welcome, glad to help & thanks for the feedback.

DataBodyRange refers to table data part, excluding the header.
Resize(, 3) means get only the first three columns
And I amend the code a bit to make it faster:

Function check_it(ByVal a As String, ByVal b As String, ByVal c As String) As Boolean
Dim va
Dim i As Long
va = ActiveSheet.ListObjects("Table1").DataBodyRange.Resize(, 3)
For i = 1 To UBound(va, 1)
    If va(i, 1) = a Then         'check 1st column, it it's false then no need to check 2nd column
        If va(i, 2) = b Then     'check 2nd column, it it's false then no need to check 3rd column
            If va(i, 3) = c Then
                check_it = True
                Exit Function
            End If
        End If
    End If
Next
End Function

I see; thank you for the additional help!