Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
What's your question???
-- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
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?
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
what is the formula?
=ShiftTCC(A2:C3) ? isabelle |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
ShiftTCC is just the formula name. The input would be a single cell input.
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
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, a écrit : ShiftTCC is just the formula name. The input would be a single cell input. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
WorksheetFunction.Match Usage?
Correction...
and probably use WorksheetFunction.Counta() on the range being searched ...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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheetfunction.match | Excel Programming | |||
Problems with WorksheetFunction.Index and WorksheetFunction.Match | Excel Programming | |||
WorksheetFunction.Match | Excel Programming | |||
worksheetfunction.match | Excel Programming | |||
Worksheetfunction MATCH | Excel Programming |