Solved

Problem with Match searching Min amount

Posted on 2013-11-02
11
129 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
  • 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
Technology Partners: 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!

 
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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

680 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