I've had another play around with the code but still cant get it working
here's the full code
Sub COMPARE_RTR_SCORES()
Application.Calculation = xlCalculationManual
Dim outarr() As Variant ' this defines an array of variants.
' so that it can write it back to the worksheet
With Sheets("RTR_Import") ' the following code is exececuted on the RTR_Import sheet
lastrow = .Cells(Rows.Count, "K").End(xlUp).Row ' this finds the last cell with data in it on column "K"
Import = Range(.Cells(1, 1), .Cells(lastrow, 56)) ' this loads all data from column 1 to column 56 (Columns A to BD)into the virtual array "Import"
lastnam = .Cells(Rows.Count, "K").End(xlUp).Row ' This finds the last cell with data in it in column "K"
Namearr = Range(.Cells(1, 1), .Cells(lastnam, 56)) ' This loads all data from column 1 to column 56 (Columns A to BD)into the virtual array "Namearr"
ReDim outarr(1 To lastrow - 6, 1 To 1) ' this redimensions the output array to the correct size
For i = 7 To lastnam ' this controls the loop through all the rows in the virtual array "Namearr"
For j = 6 To lastrow ' this controls the loop through the data in the virtual array "Import"
If Namearr(i, 7) = Import(j, 7) And (Namearr(i, 56)) + 1 = (Import(j, 56) Then
' this compares the data in "Import" array
outarr(i - 6, 1) = Namearr(i, 55) - Import(j, 55)
Exit For
End If
Next j
Next i
Range(.Cells(7, 24), .Cells(lastrow, 24)) = outarr ' write the output data to column 24 (X)
End With
Application.Calculation = xlCalculationAutomatic
End Sub
When i run the code i get Run-time error '13': Type mismatch
Im wondering if the issue is because im trying to check the value of the cell "Namearr(i, 56)" is 1 less than the value of the cell "Import(j, 56)"