• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

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
0
mak345
Asked:
mak345
  • 2
  • 2
1 Solution
 
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
 
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
 
mak345Author Commented:
THANK YOU!!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now