Solved

Application.Match with 2 Dimenstional Array

Posted on 2014-04-21
4
164 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

733 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