Adding a variable to index & match function using VBA

[prestopr]

I have tried to add a variable to an index & match worksheet function in vba without success as shown below.

The variable is mat_ox

CU_LL = Evaluate("INDEX($E$4:$E$23,MATCH(" & mat_ox & ",$C$4:$C$23&$D$4:$D$23,0))")

Any assistance would be greatly appreciated.

Thank you

[xladept]

Have you tried:

CU_LL = Evaluate(INDEX($E$4:$E$23,MATCH(mat_ox & "," & $C$4:$C$23 &","  & ,$D$4:$D$23,0)))

[prestopr]

Thank you for your assistance

I have now just tried your recommedation and it did not work. However, vba return "invaild character" error.

[xladept]

Can I see the entire code and maybe a sample worksheet?

[prestopr]

Thank you for your prompt reply.
Here is a copy of the code.

It is in the early stages of development!
Essentialy I want to validate data in a worksheet against reference data (defined upper and lower limits) in another spreadsheet.

For example a record with a "mat_ox" value of "blabla" would have defined upper and lower limits for each measure eg copper (Cu) Uranium (U) and Specific Gravity (SG). The macro tests each value aganst the data in Ref_Table spreadsheet and if the value is outside the upper and lower limits the cell is flagged (infilled red).

Sheets("Chimi").Select
For Each Record In Product_List

Dim mat_ox
Sheets("Chimi").Select
mat_ox = Record.Offset(0, 1) + Record.Offset(0, 2)


Sheets("Ref_Tables").Select
CU_LL = Evaluate("INDEX($E$4:$E$23,MATCH(" & mat_ox & ",$C$4:$C$23&$D$4:$D$23,0))")
CU_UL = Evaluate("INDEX($F$4:$F$23,MATCH(D1,$C$4:$C$23&$D$4:$D$23,0))")
U_LL = Evaluate("INDEX($G$4:$G$23,MATCH(D1,$C$4:$C$23&$D$4:$D$23,0))")
U_UL = Evaluate("INDEX($H$4:$H$23,MATCH(D1,$C$4:$C$23&$D$4:$D$23,0))")
SG_LL = Evaluate("INDEX($I$4:$I$23,MATCH(D1,$C$4:$C$23&$D$4:$D$23,0))")
SG_UL = Evaluate("INDEX($J$4:$J$23,MATCH(D1,$C$4:$C$23&$D$4:$D$23,0))")

           
If Record.Offset(0, 7) >= CU_LL And Record.Offset(0, 7) <= CU_UL Then
    MsgBox "Cu fails"
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
If Record.Offset(0, 7) >= U_LL And Record.Offset(0, 7) <= U_UL Then
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
If Record.Offset(0, 7) >= SG_LL And Record.Offset(0, 7) <= SG_UL Then
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
     
       
       Next
'--------------------------------------------------------------
    
End Sub

[prestopr]

here is the ws

[xladept]

Hi prestopr,

I've altered your code but am unable to test the coloring aspect since I'm using 2003 and it protests against the "too many formats" in your sample - let me know if it works for you:

Sub prestopr(): Dim wc As Worksheet, wr As Worksheet, Product_List As Range
Set wc = ActiveWorkbook.Sheets("Chimi"): Set wr = ActiveWorkbook.Sheets("Ref_Tables")
' Checks that all grade variables are within the defined material type's grade ranges.
If wc.Range("A5") = "" Then
       MsgBox "There is no data to check" & Chr(13) & Chr(13)
        Exit Sub: End If
    
Set Product_List = wc.Range("Block_ID")
    
'-------------------------------------------------------------
Dim mat_ox As String, Record As Range, i As Integer
Dim CU_LL As Single, CU_UL As Single, U_LL As Single
Dim U_UL As Single, SG_LL As Single, SG_UL As Single

For Each Record In Product_List
mat_ox = Record.Offset(0, 1) + Record.Offset(0, 2)
For i = 4 To 23
If mat_ox = UCase(wc.Range("C" & i) & wc.Range("D" & i)) Then

CU_LL = wr.Range("E" & i): CU_UL = wr.Range("F" & i): U_LL = wr.Range("G" & i)
U_UL = wr.Range("H" & i): SG_LL = wr.Range("I" & i): SG_UL = wr.Range("J" & i)

Exit For: End If: Next i

    'MsgBox "Mat_ox = " & mat_ox & vbNewLine & _
    '       "Cu_LL = " & CU_LL & vbNewLine & _
    '       "Cu_UL = " & CU_UL & vbNewLine & _
    '       "U_LL = " & U_LL & vbNewLine & _
    '       "U_UL = " & U_UL & vbNewLine & _
    '       "SG_LL = " & SG_LL & vbNewLine & _
    '       "SG_UL = " & SG_UL
    '
           
If Record.Offset(0, 7) >= CU_LL And Record.Offset(0, 7) <= CU_UL Then
    MsgBox "Cu fails"
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
If Record.Offset(0, 7) >= U_LL And Record.Offset(0, 7) <= U_UL Then
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
If Record.Offset(0, 7) >= SG_LL And Record.Offset(0, 7) <= SG_UL Then
Else
Record.Offset(0, 7).Interior.Color = RGB(100, 0, 0)
End If
       
       
       
       
       
       Next
'--------------------------------------------------------------

    
End Sub

[JosephP]

mat_ox is a string so you must enclose the value in quotes

CU_LL = Evaluate("INDEX($E$4:$E$23,MATCH(""" & mat_ox & """,$C$4:$C$23&$D$4:$D$23,0))")

[xladept]

Hi prestopr,

It works with Joseph P's code up to where the coloring starts if you do it this way:

Sheets("Ref_Tables").Select
'CU_LL = Application.WorksheetFunction.Index(Range("Ref_Tables!E4:E23"), _
Application.WorksheetFunction.Match(""" & mat_ox & """, Range("Ref_Tables!C4:C23"), 0), 0)
CU_LL = Evaluate("INDEX($E$4:$E$23,MATCH(""" & mat_ox & """,$C$4:$C$23&$D$4:$D$23,0))")
CU_UL = Evaluate("INDEX($F$4:$F$23,MATCH(""" & mat_ox & """,$C$4:$C$23&$D$4:$D$23,0))")
U_LL = Evaluate("INDEX($G$4:$G$23,MATCH(""" & mat_ox & """,$C$4:$C$23&$D$4:$D$23,0))")
U_UL = Evaluate("INDEX($H$4:$H$23,MATCH(""" & mat_ox & """,$C$4:$C$23&$D$4:$D$23,0))")
SG_LL = Evaluate("INDEX($I$4:$I$23,MATCH(""" & mat_ox & """,$C$4:$C$23&$D$4:$D$23,0))")
SG_UL = Evaluate("INDEX($J$4:$J$23,MATCH(""" & mat_ox & """,$C$4:$C$23&$D$4:$D$23,0))")

[JosephP]

yeah-there's no match for mat_ox in that table :-)