INDEX & MATCH Help with syntax please...

U

u473

I am using Interior.ColorIndex in Sheet1 to define Work Hours per day,
8, 9, 10...
and Font.ColorIndex to define Week Worked days, 5, 6 or 7
which in turn with a Select Case allows me to call the applicable Rate
Code in Sheet3.
It works well until I try to retrieve with INDEX & MATCH the Row/Colum
intersect value of Position and Rate to assign it to my variable Cost.
Finally, I want to store in Sheet2 the product of Force & Cost, in the
same relative cell position i,J of Sheet1
----------------------------------
Sub Test()
Dim ws1 As Worksheet: Dim ws2 As Worksheet: Dim ws3 As Worksheet
Dim LastRow As Long: Dim LastCol As Long: Dim i As Long, Z As Long
Dim rng1 As Range: Dim rng2 As Range: Dim rng3 As Range: Dim rngfound
As Range
Dim iClr, fClr, Force, Cost As Integer: Dim Position, Rate As String
Dim Table, Empl, Rates As Range
Set ws1 = Worksheets("Sheet1")
LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LastCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Set ws2 = Worksheets("Sheet2"): Set ws3 = Worksheets("Sheet3")
Z = 2
'Application.ScreenUpdating = False
For i = 2 To LastRow
For J = 2 To LastCol
iClr = ws1.Cells(i, J).Interior.ColorIndex
fClr = ws1.Cells(i, J).Font.ColorIndex
Force = ws1.Cells(i, J).Value
Position = ws1.Cells(i, 1).Value
Select Case iClr
Case 40
If fClr = -4105 Then
Rate = "509"
ElseIf fClr = 5 Then
Rate = "609"
Else
Rate = "709"
End If
Case 36
...............
End Select
Error here in Index & Match
in trying to assign to Cost, the value of the Row/Column
intersect
I guess my problem is in the 2 following lines.
I tried using named ranges but sill could not pass that hurdle.
Cost =INDEX(Sheet3!A1:p4,MATCH(Position,Sheet3!A:A,
0),MATCH(Rate,Sheet3!1:1,0))
ws2.Range(i,J).value = Force* Cost
Next J
Next i
Application.CutCopyMode = False
End Sub

Thank you for your help,
Celeste
 
K

Ken Johnson

I am using Interior.ColorIndex in Sheet1 to define Work Hours per day,
8, 9, 10...
and Font.ColorIndex to define Week Worked days, 5, 6 or 7
which in turn with a Select Case allows me to call the applicable Rate
Code in Sheet3.
It works well until I try to retrieve with INDEX & MATCH the Row/Colum
intersect value of Position and Rate to assign it to my variable Cost.
Finally, I want to store in Sheet2 the product of Force & Cost, in the
same relative cell position i,J of Sheet1
----------------------------------
Sub Test()
Dim ws1 As Worksheet: Dim ws2 As Worksheet: Dim ws3 As Worksheet
Dim LastRow As Long: Dim LastCol As Long: Dim i As Long, Z As Long
Dim rng1 As Range: Dim rng2 As Range: Dim rng3 As Range: Dim rngfound
As Range
Dim iClr, fClr, Force, Cost As Integer: Dim Position, Rate As String
Dim Table, Empl, Rates As Range
Set ws1 = Worksheets("Sheet1")
LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
LastCol = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Set ws2 = Worksheets("Sheet2"): Set ws3 = Worksheets("Sheet3")
Z = 2
'Application.ScreenUpdating = False
For i = 2 To LastRow
For J = 2 To LastCol
iClr = ws1.Cells(i, J).Interior.ColorIndex
fClr = ws1.Cells(i, J).Font.ColorIndex
Force = ws1.Cells(i, J).Value
Position = ws1.Cells(i, 1).Value
Select Case iClr
Case 40
If fClr = -4105 Then
Rate = "509"
ElseIf fClr = 5 Then
Rate = "609"
Else
Rate = "709"
End If
Case 36
...............
End Select
Error here in Index & Match
in trying to assign to Cost, the value of the Row/Column
intersect
I guess my problem is in the 2 following lines.
I tried using named ranges but sill could not pass that hurdle.
Cost =INDEX(Sheet3!A1:p4,MATCH(Position,Sheet3!A:A,
0),MATCH(Rate,Sheet3!1:1,0))
ws2.Range(i,J).value = Force* Cost
Next J
Next i
Application.CutCopyMode = False
End Sub

Thank you for your help,
Celeste
Click to expand...

INDEX and MATCH are WorksheetFunctions. I think the syntax for the
formula for Cost should be...

WorksheetFunction.Index(Sheet3.Range("A1:p4"),
WorksheetFunction.Match(Position, Sheet3.Range("A:A"), 0),
WorksheetFunction.Match(Rate, Sheet3.Range("1:1"), 0))

Also...
Dim iClr, fClr, Force, Cost As Integer: Dim Position, Rate As String
Dim Table, Empl, Rates As Range
Click to expand...

dimensions iClr, fClr, Force, Position, Table and Empl as Variant. Is
that your intention? If not then maybe it should be...

Dim iClr As Integer, fClr As Integer, Force As Integer, Cost As
Integer: Dim etc

Ken Johnson
 
U

u473

Thank you for your answer.
I copied your WorksheetFunction syntax and corrected my Dim's
I get the following error on the same line
Runtime error '1004'. Unable to get the Match property of the
WorksheetFunction class.
I am using Excel 2003. Any suggestion ?
Thank you again,
Celeste
 
K

Ken Johnson

Thank you for your answer.
I copied your WorksheetFunction syntax and corrected my Dim's
I get the following error on the same line
Runtime error '1004'. Unable to get the Match property of the
WorksheetFunction class.
I am using Excel 2003. Any suggestion ?
Thank you again,
Celeste
Click to expand...

What are the values of Position and Rate when the error occurs?
You will get that error message when either the Position value can't
be found in column A or the Rate value can't be found in Row 1.

Ken Johnson
 
U

u473

Additional research in Debug mode with watches to test each section of
that long code line.

Tst1 = WorksheetFunction.Match(Position, Sheet3.Range("A:A"), 0)
Tst2 = WorksheetFunction.Match(Rate, Sheet3.Range("1:1"), 0)

Tst1 gets populated fine but Tst2 gets the error 'Unable to get the
Match property
of the WorksheetFunction class'
My headers in each cell of Row 1 for Code Rates look like 508, 509,
510 are Text not Integers
and my Select Case populates the Rate to be searched with a string.
I cannot detect what caused the error in Tst2.
 
K

Ken Johnson

Additional research in Debug mode with watches to test each section of
that long code line.

Tst1 = WorksheetFunction.Match(Position, Sheet3.Range("A:A"), 0)
Tst2 = WorksheetFunction.Match(Rate, Sheet3.Range("1:1"), 0)

Tst1 gets populated fine but Tst2 gets the error 'Unable to get the
Match property
of the WorksheetFunction class'
My headers in each cell of Row 1 for Code Rates look like 508, 509,
510 are Text not Integers
and my Select Case populates the Rate to be searched with a string.
I cannot detect what caused the error in Tst2.
Click to expand...

So Rate is text and so are the row one values being looked up.
When the error occurs and you click the debug button, what do you see
when you hover the cursor over Rate, its error causing value should
appear in a little pale yellow box.

Ken Johnson
 
U

u473

On the first pass, Position = "Manager for Row 2 and Rate =
"508" from B2 as expected.
Tst1 = 2 which is the expected value
but hovering over Tst2 = 0 and I should expect 2 also.
I checked variables and Headers names, and I have no leading blanks in
my Rate variable nor in my headers names.
Thank you for your time,
Celeste
 
K

Ken Johnson

On the first pass, Position = "Manager for Row 2 and Rate =
"508" from B2 as expected.
Tst1 = 2 which is the expected value
but hovering over Tst2 = 0 and I should expect 2 also.
I checked variables and Headers names, and I have no leading blanks in
my Rate variable nor in my headers names.
Thank you for your time,
Celeste
Click to expand...

If it is at all possible to email me the workbook for me to have a
close look, feel free to send it (kencjohnson is my gmail (gmail.com)
account) name)

Ken Johnson
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with find then copy macro 5
Combining lists (again) 2
problem with change from excel 97 to xp 3
Match and Copy to array 6
Combine the lists problem 5
type mismatch in this code 4
Method 'Range' of Object_Worksheet Failed 23
Problem with Pearson Code 3

Top