[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Application.Match with 2 Dimenstional Array

Posted on 2014-04-21
4
Medium Priority
?
169 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

656 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