Spot the Syntax Error!

[pdevito3]

Hey guys, I've got something breaking this idex/match snippet of VBA and I can't find the issue. I can definitely elaborate on variable details or provide a sample sheet, but I didn't want to overclutter to start.


VBA:

For eachRow = 2 to lastRow

sd.Cells(eachRow, sdExclusionCol) = _
            Application.WorksheetFunction.Index(cd.UsedRange, _
            WorksheetFunction.Match(1, _
                (cd.Columns(cdMaterialCol & ":" & cdMaterialCol) = sd.Cells(eachRow, sdMaterialCol)) * _
                Int((cd.Columns(cdLevelCol & ":" & cdLevelCol)) = Int(sd.Cells(eachRow, sdLevelCol))), _
                0), _
            cdExclusionCol)

Next eachRow

Also, here is a working formula version if it helps:

=INDEX('Cd'!O:O,MATCH(1,('Cd'!K:K='Sd'!O24)*(INT('Cd'!N:N)=INT('Sd'!E24)),0),0)</SPAN>

Thanks

 

[RoryA]

You can't use that array construction in VBA with WorksheetFunction (or functions like Int). You'd need to use Evaluate:

sd.Cells(eachRow, sdExclusionCol) = sd.Evaluate("INDEX('Cd'!O:O,MATCH(1,('Cd'!K:K='Sd'!O24)*(INT('Cd'!N:N)=INT('Sd'!E24)),0),0)")

for example.

 

[pdevito3]

Gotcha. Thanks. Ended up getting the evaluate to work, but it was taking much longer than I would have liked. Came up with this instead if anyone is interested:

Application.ScreenUpdating = False</SPAN>
    loopStopper = False</SPAN>
    For eachRow = 2 To sd.Cells(Rows.count, sdMaterialCol).End(xlUp).Row</SPAN>
        Application.StatusBar = "Row " & eachRow & " of " & sd.Cells(Rows.count, sdMaterialCol).End(xlUp).Row</SPAN>
        curMaterial = Cells(eachRow, sdMaterialCol)</SPAN>
        curLevel = Cells(eachRow, sdLevelCol)</SPAN>
        Do While loopStopper = False </SPAN>
            For eachCdRow = 2 To cd.Cells(Rows.count, cdMaterialCol).End(xlUp).Row</SPAN>
                If cd.Cells(eachCdRow, cdMaterialCol) = curMaterial And cd.Cells(eachCdRow, cdLevelCol) = curLevel Then</SPAN>
                    sd.Cells(eachRow, sdExclusionCol) = cd.Cells(eachCdRow, cdExclusionCol)</SPAN>
                    loopStopper = True</SPAN>
                End If</SPAN>
                If eachCdRow = cd.Cells(Rows.count, cdMaterialCol).End(xlUp).Row Then</SPAN>
                    If sd.Cells(eachRow, sdExclusionCol) = "" Then</SPAN>
                        sd.Cells(eachRow, sdExclusionCol) = "Not Found"</SPAN>
                    End If</SPAN>
                    loopStopper = True</SPAN>
                End If</SPAN>
            Next eachCdRow</SPAN>
        Loop</SPAN>
        loopStopper = False</SPAN>
    Next eachRow</SPAN>
    Application.ScreenUpdating = True</SPAN>
   
    Application.StatusBar = ""</SPAN>