Solved

Application.Match with 2 Dimenstional Array

Posted on 2014-04-21
4
163 Views
Last Modified: 2014-04-21
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
Comment
Question by:mak345
  • 2
  • 2
4 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 40012715
Are you just looking to see if a line with both exists? You could just use COUNTIFS instead of MATCH.

Matt
0
 

Author Comment

by:mak345
ID: 40013068
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
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 40013370
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
 

Author Closing Comment

by:mak345
ID: 40013400
THANK YOU!!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question