Solved

# Problem with Match searching Min amount

Posted on 2013-11-02
125 Views
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
``````

What I am trying to do is ask the question "Which employee has the lowest sales from January - April?" using the MIN function
0
Question by:Frank Freese
• 6
• 3
• 2

LVL 80

Expert Comment

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
``````
0

Author Comment

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

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

LVL 80

Expert Comment

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

Here's the file as requested
MinEmployeeSales.xlsm
0

LVL 43

Expert Comment

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

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

LVL 80

Accepted Solution

byundt earned 500 total points
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

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
``````
MinEmployeeSalesQ28283963.xlsm
0

LVL 43

Expert Comment

Try

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

Author Comment

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

thanks to all
I appreciate everyone's help
0

## Featured Post

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…