asked on

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

byundt

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

Frank Freese

ASKER

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"

Frank Freese

ASKER

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?

byundt

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

Frank Freese

ASKER

Here's the file as requested
MinEmployeeSales.xlsm

Saqib Husain

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)

Frank Freese

ASKER

My objective was lowest month (B3:E7) which are multiple rows and employee (A3:A7) single column.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Saqib Husain

Try

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

Frank Freese