Solved

Problem with Match searching Min amount

Posted on 2013-11-02
11
127 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

808 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