Problem with Match searching Min amount

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
Frank FreeseAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
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
 
byundtCommented:
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
 
Frank FreeseAuthor Commented:
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
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.

 
Frank FreeseAuthor Commented:
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
 
byundtCommented:
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
 
Frank FreeseAuthor Commented:
Here's the file as requested
MinEmployeeSales.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
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
 
Frank FreeseAuthor Commented:
My objective was lowest month (B3:E7) which are multiple rows and employee (A3:A7) single column.
0
 
Saqib Husain, SyedEngineerCommented:
Try

LowestEmployeeSales = WorksheetFunction.Index(Range("A3:A7"), _
        Range("B3:E7").Find(WorksheetFunction.Min(Range("B3:E7"))).Row - 2)
0
 
Frank FreeseAuthor Commented:
Thanks folks
I'm going with byundr's solution - he solved two birds at once. But thank you all very much
0
 
Frank FreeseAuthor Commented:
thanks to all
I appreciate everyone's help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.