Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Problem with Match searching Min amount

Posted on 2013-11-02
11
Medium Priority
?
142 Views
Last Modified: 2013-11-03
Folks,
In my worksheet, the range A3:A7 are employess and the range B3:E7 are monthly sales from January - April for each employee. I keep getting an error "Run-time error '1004' for the code below at "LowestEmployeeSales = ":

Private Sub cmdAnswerEmployLowestSales_Click()
   Dim LowestEmployeeSales As String
   Dim LowestEmployee As String
    
    LowestEmployeeh = Application.InputBox("Enter Answer", "Lowest Employee", Type:=2)
        
    LowestEmployeeSales = WorksheetFunction.Index(Range("A3:A7"), _
        WorksheetFunction.Match(WorksheetFunction.Min(Range("B3:E7")), _
        Range("B3:E7"), 0))
            
    If LowestEmployee <> LowestEmployeeSales Then
        MsgBox "Incorrect answer " & LowestEmployeeMonth & " does not have the lowest month sales. Try Again!"
    Else
        MsgBox "Correct Answer - great job!"
    End If
End Sub

Open in new window


What I am trying to do is ask the question "Which employee has the lowest sales from January - April?" using the MIN function
0
Comment
Question by:Frank Freese
[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
  • 6
  • 3
  • 2
11 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 39619445
You had a typo in statement 5 (note the "h" at end of variable name)
You can't use MATCH on a 2-D range. It has to be either a row or column of cells.
Variable LowestEmployeeMonth should probably be LowestEmployee in statement 12

I patched up the above with:
Private Sub cmdAnswerEmployLowestSales_Click()
   Dim LowestEmployeeSales As String
   Dim LowestEmployee As String
   Dim LowestEmployeeMonth As String
   Dim LowestSales As Double
   Dim rgSales As Range
   Dim rgNames As Range
   Dim celMin As Range
   
   Set rgNames = Range("A3:A7")
   Set rgSales = Range("B3:B7")
    
    LowestEmployee = Application.InputBox("Enter Answer", "Lowest Employee", Type:=2)
    LowestSales = Application.Min(rgSales.Value)
    Set celMin = rgSales.Find(LowestSales, LookIn:=xlValues, lookat:=xlWhole)
        
    LowestEmployeeSales = Intersect(celMin.EntireRow, rgNames)
            
    If LowestEmployee <> LowestEmployeeSales Then
        MsgBox "Incorrect answer " & LowestEmployee & " does not have the lowest month sales. Try Again!"
    Else
        MsgBox "Correct Answer - great job!"
    End If
End Sub

Open in new window

0
 

Author Comment

by:Frank Freese
ID: 39619989
The typo doesn't surprise me - sorry about that.
I did not know the MATCH constraint.
I did copy your code and at line 17 received the following error:
"Run-time error ' 91
Object variable or With block not set"
0
 

Author Comment

by:Frank Freese
ID: 39620009
I also do not understand line 11:

Set rgSales = Range("B3:B7")

Should that not be B3:E8 the range that the sales data is?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 81

Expert Comment

by:byundt
ID: 39620011
There was a typo on statement 11 defining the range containing sales data. But that typo won't produce the error mentioned.

Could you please post a sample file that demonstrates the problem? The code is working in my test workbook attached.
LowestSalesQ28283963.xlsm
0
 

Author Comment

by:Frank Freese
ID: 39620235
Here's the file as requested
MinEmployeeSales.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39620276
The match function will work either on

one row and multiple columns or
one column and multiple rows

but not on

multiple rows and multiple columns.

You should be doing something like

    LowestEmployeeSales = WorksheetFunction.Index(Range("A3:A7"), _
        Range("B3:B7").Find(WorksheetFunction.Min(Range("B3:B7"))).Row - 2)
0
 

Author Comment

by:Frank Freese
ID: 39620316
My objective was lowest month (B3:E7) which are multiple rows and employee (A3:A7) single column.
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 39620327
I believe the problem was the Currency cell format. I moved your subs into a regular module sheet as well, so I could figure out which statement was causing an error.
Sub cmdAnswerEmployLowestSales_Click()
    Dim LowestEmployeeSales As String
    Dim LowestEmployee As String
    Dim LowestEmployeeMonth As String
    Dim LowestSales As Double
    Dim rgSales As Range
    Dim rgNames As Range
    Dim celMin As Range
    Dim rw As Range
    Dim v As Variant
   
    Set rgNames = Range("A3:A7")
    Set rgSales = Range("B3:E8")
    
    LowestEmployee = Application.InputBox("Enter Answer", "Lowest Employee", Type:=2)
        
    LowestSales = Application.Min(rgSales)
    For Each rw In rgSales.Rows
        v = Application.Match(LowestSales, rw, 0)
        If Not IsError(v) Then
            Set celMin = rw.Cells(v)
            Exit For
        End If
    Next
        
    LowestEmployeeSales = Intersect(celMin.EntireRow, rgNames)
            
    If LowestEmployee <> LowestEmployeeSales Then
        MsgBox "Incorrect answer " & LowestEmployee & " does not have the lowest month sales. Try Again!"
    Else
        MsgBox "Correct Answer - great job!"
    End If
End Sub

Sub cmdAnswerLowestSales_Click()
    Dim LowestMonthlySales As String
    Dim LowestMonthSales As String
    Dim celMin As Range
    Dim rgMonths As Range
    Dim rgSales As Range
    Dim LowestSales As Double
    Dim rw As Range
    Dim v As Variant
    
    LowestMonthSales = Application.InputBox("Enter Answer", "Lowest Month", Type:=2)
    Set rgMonths = Range("B2:E2")
    Set rgSales = Range("B3:E8")
        
    LowestSales = Application.Min(rgSales)
    For Each rw In rgSales.Rows
        v = Application.Match(LowestSales, rw, 0)
        If Not IsError(v) Then
            Set celMin = rw.Cells(v)
            Exit For
        End If
    Next
        
    LowestMonthlySales = Intersect(celMin.EntireColumn, rgMonths)
            
    If LowestMonthSales <> LowestMonthlySales Then
        MsgBox "Incorrect answer " & LowestMonthSales & " is not the most lowest month. Try Again!"
    Else
        MsgBox "Correct Answer - great job!"
    End If
End Sub

Open in new window

MinEmployeeSalesQ28283963.xlsm
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39620328
Try

LowestEmployeeSales = WorksheetFunction.Index(Range("A3:A7"), _
        Range("B3:E7").Find(WorksheetFunction.Min(Range("B3:E7"))).Row - 2)
0
 

Author Comment

by:Frank Freese
ID: 39620344
Thanks folks
I'm going with byundr's solution - he solved two birds at once. But thank you all very much
0
 

Author Closing Comment

by:Frank Freese
ID: 39620345
thanks to all
I appreciate everyone's help
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

610 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