Application.Match with 2 Dimenstional Array

I am using VBA in MS Excel 2010 and SQL Server 2008.

I have a scenario where I am filling out a spreadsheet from a data file that has 2 columns.

I then create a recordset from a SQL 2008 db that contains 4 fields, 2 of which I want to compare to the data filled out from above.

I have done this before with 1 variable by running a loop on the SQL recordset and attempting to find the value with Application.Match.

How do I do this with 2 variables?  I want to see if there is a line with both variables in the same record in my SQL table.

Thanks
mak345Asked:
Who is Participating?
 
mvidasCommented:
Sure. You should be able to use application.countifs like you would application.match (though you may have to make it Application.WorksheetFunction.Countifs). I'm not sure how you are going to refer to the values from mysql, so I left those as is:
If Application.CountIfs(Worksheets("DATA").Range("A:A"), mySQL!Port, Worksheets("DATA").Range("C:C"), mySQL!Acc) = 0 Then
     'do something
End If

Open in new window

0
 
mvidasCommented:
Are you just looking to see if a line with both exists? You could just use COUNTIFS instead of MATCH.

Matt
0
 
mak345Author Commented:
Thanks Matt,

Can you help me with the syntax?

If [COUNTIFS(DATA!A:A,mySQL!Port,DATA!C:C,mySQL!Acc))] = 0 Then
     do something
END IF
0
 
mak345Author Commented:
THANK YOU!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.