WorksheetFunction.Match Usage?

  • Thread starter Thread starter mlthornton
  • Start date Start date
M

mlthornton

I am attempting to use the following code to:
loop through a range of cells and check each cell to see if its value exists in an array in a different sheet. The function tells how many matches were found.

Function ShiftTCC(Week As Range)

Set ProductRange = Sheets(Week.Value).Range("B1:B50")
Set ProductArray = Sheets("Classes").Range("A3:A15")

If Not ProductRange Is Nothing Then
For Each ProductCell In ProductRange
MsgBox ProductCell.Value
m = WorksheetFunction.Match(ProductCell.Value, ProductArray, 0)
if m > 0 then
totalcount = totalcount + 1
end if
Next

ShiftTCC = totalcount

End If

End Function
 
Oops. I stopped short. The problem is it seems to fail at the worksheetfunction.match line. If I substitute that line for msgbox product cell.value, it returns expected results. But when the worksheet function.match line is in, it seems to cut the loop off. Is the syntax in the for loop bad?
 
Ah! Well.., that's not how I'd go about the task as its intent implies.
I'd probably want to know individual totals for each value being
searched for matches, as well as a grand total for all matches found.
That requires a slightly different approach whereby I'd store results
in a dynamic array, and probably use WorksheetFunction.Counta() on the
range being searched since it'll be orders of magnitude faster than
using Match() as well as not raising any errors in the process.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
hi,

=ShiftTCC(A1)

Function ShiftTCC(Week As Range) As Integer
Application.Volatile
Set ProductRange = Sheets(Week.Value).Range("B1:B50")
Set ProductArray = Sheets("Classes").Range("A3:A15")

If Not ProductRange Is Nothing Then
For Each ProductCell In ProductRange

On Error Resume Next
If IsError(WorksheetFunction.Match(ProductCell.Value, ProductArray,
0)) Then
Err.Clear
Else
totalcount = totalcount + 1
End If
Next

ShiftTCC = totalcount

End If
End Function


isabelle
 
OK, here's where I'm at.

There are two tabs:

In "1x1" tab, starting in A1, 3 columns

Fruit Color Number
Apple Red 1
Pear Red 2
Apple Green
Orange Red 1
Grape Blue 3
Banana Red 1
Banana Red 2
Apple Blue
Pear Red
Pear Green 3

In "Criteria" tab, starting in A1, 3 columns:

Fruit Criteria Color Criteria Number Criteria
Apple Blue 1
Orange Red 2

The code is:

Function ShiftTCC(Week As Range) As Integer

Application Volatile

'item ranges based on sheet name
Set FruitRange = Sheets(Week.Value).Range("A1:A10")
Set ColorRange = Sheets(Week.Value).Range("B1:B10")
Set NumberRange = Sheets(Week.Value).Range("B1:B10")

'value criteria
Set FruitCriteria = Sheets("Criteria").Range("A1:A10")
Set ColorCriteria = Sheets("Criteria").Range("A1:A10")
Set NumberCriteria = Sheets("Criteria").Range("A1:A10")

'initialize loop value
countvals = 0


If Not FruitRange Is Nothing Then

'loop through each value in the fruits range to see which ones match the criteria list
For Each FruitCell In FruitRange

MsgBox "round 0"

On Error Resume Next
If Not IsError(WorksheetFunction.Match(FruitCell.Value, FruitRange, 0)) Then
Err.Clear

MsgBox "made 1st if"

'second loop to check for the color criteria
For Each ColorCell In ColorRange
On Error Resume Next
If Not IsError(WorksheetFunction.Match(ColorCell.Value, ColorRange, 0)) Then
Err.Clear

MsgBox "made 2nd if"

'third and last loop for number criteria
For Each ColoCell In ColorRange
On Error Resume Next
If Not IsError(WorksheetFunction.Match(ColorCell.Value, ColorRange, 0)) Then
Err.Clear
MsgBox "made 3rd if"

'count number of rows that satisfy all 3 criteria
countvals = countvals + 1

End If
Next
End If
Next
End If
Next
End If

ShiftTCC = countvals

End Function

It returns a 1000 as if it passed every "if" statement. It should only return 1. Is the syntax incorrect?
 
hi,

http://cjoint.com/?DBwgHGkAmra

Function ShiftTCC(Week As Range) 'Week ---> name of the source sheet
Application.Volatile

Set FruitRange = Range("A2:A11")
Set ColorRange = Range("B2:B11")
Set NumberRange = Range("C2:C11")

For Each FruitCell In Sheets("Criteria").Range("A2:A20")
If FruitCell <> "" Then

' be careful with copy and paste. the following command is one single line
'--------------------------------------------------------
x = Evaluate("=SumProduct(('" & Week & "'!" & FruitRange.Address & "=" &
FruitCell.Address & ")*('" & Week & "'!" & ColorRange.Address & "=" &
FruitCell.Offset(0, 1).Address & ")*('" & Week & "'!" & NumberRange.Address &
"=" & FruitCell.Offset(0, 2).Address & "))")
'--------------------------------------------------------
End If
Total = Total + x
x = 0
Next

ShiftTCC = Total

End Function


isabelle

Le 2014-02-21 18:23, (e-mail address removed) a écrit :
 
Correction...
and probably use WorksheetFunction.Counta() on the range being
searched
Click to expand...

...should read

and probably use WorksheetFunction.CountIf() on the range being
searched

Also, now that it's clear this function is being used in worksheet
formulas, I recant the idea of getting individual match criteria
totals!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top