Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Problem with Match searching Min amount

Posted on 2013-11-02
11
Medium Priority
?
145 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
Independent Software Vendors: 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

580 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