Perform Sum in vba code

[plasteredric]

I'm trying to minus the value of one cell in a range from another cell in a range within vba code but i'm not sure how to go about it.

The code I have is


outarr(i - 6, 1) = VirtImport(j, 56) - Namearr(i, 56)

I'd like to minus the Namearr value from the VirtImport value and have the result placed into the virtual array

Any help appreciated

[p45cal]

I don't see anything wrong with the code.
If i is less than 7 then you'll be writing to outarr(0 or less,1) which is fine as long as outarr's first dimension extends to 0 or less (it's perfectly OK to have the likes of Dim outarr(-9 to 2,1 to 1)).

[plasteredric]

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)"

[offthelip]

Since you are trying to add 1 to Namearr(i,56) You will get this error if you have something in Namearr which is not Numeric, you can also get this error if Import (j,56) is text. So to find the error you need go into debug and look at the line when the error is and hover over each variable to see what values they contain.
Just as a matter of interest you don't need to switch calculation to manual and then back to automatic during this routine because you don't write anything to the workbook until the last line when you output outarr. So the worksheet won't be doing any calcualtions anyway. (Thats is one of the real advantanges of using variant arrays)