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!A14,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
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!A14,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