Styjun

I am building a program that measures the metrics of our company actions and progress and I am having trouble with a particular part. I am doing this in VBA, and I want to perform the following actions:

1. Compare a range on two spreadsheets to find mismatches.
2. Calculate (not format) how many cells are different and then send that 
 resultant value a target cell on another spreadsheet in the workbook.
 EX: If 2 out of 10 cells do not match then the target cell 
 value should be 2
3. Replicate this process for about 6 other parameters in the workbook 
 (That is the easy part I think).

I have used/given the following VBA functions to try to get a resultant:
WorksheetFunction.sum
Evaulate(SumProduct)

I do not believe the problem is in the compare code itself (As I have just borrowed some internet examples I have seen.) But in the function that occurs after the 'Else' statement. The code runs and debugs fine, however no answer is produced. The code is below, make the assumptions that variables are declared (for space purposes).

Worksheets("Generalized Report").Activate
strRangeToCheck = "F2:F10000" 'Range can change depending on amount of data on sheets'

varSheetA = Worksheets("Contract").Range(strRangeToCheck)
varSheetB = Worksheets("As Built").Range(strRangeToCheck)

'Loop thru and do the comparison cell by cell via an array
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
 For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
 If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
 ' Cells are identical.
 ' Do nothing.
 Else
 ' Cells are different.

 ' Calculate how many cells are different
 Worksheet("Generalized Report").Activate
 Worksheet("Generalized Report").Range("A8") = Evaluate("=SUMPRODUCT(--('Contract'!F2:F10000<>'As Built'!F2:F10000)")

In this particular case I am expecting a value of 0 (As this is test data), but the value should equal the number of cells that are different. The following 3 things occur with my code based on my adjustments:

1. No answer given


2. 
   #Value! error


3. For already assigned

Thanks for your time.

I would count the differences in VBA instead of trying to use the SUMPRODUCT formula:

Dim DifferenceCount as Long
Worksheets("Generalized Report").Activate
strRangeToCheck = "F2:F10000" 'Range can change depending on amount of data on sheets'

varSheetA = Worksheets("Contract").Range(strRangeToCheck)
varSheetB = Worksheets("As Built").Range(strRangeToCheck)

'Loop thru and do the comparison cell by cell via an array
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
 For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
 If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
 ' Cells are identical.
 ' Do nothing.
 Else
 ' Cells are different.
 DifferenceCount = DifferenceCount + 1
 End If
 Next iCol
Next iRow
Worksheet("Generalized Report").Range("A8") = DifferenceCount

Edit

When I think about it there are two other alternatives using your formula...

Firstly your whole macro could just be

Worksheet("Generalized Report").Range("A8") = Evaluate("=SUMPRODUCT(--('Contract'!F2:F10000<>'As Built'!F2:F10000)")

This would put the formula result in A8.

The second alternative is just to use the formula =SUMPRODUCT(--('Contract'!F2:F10000<>'As Built'!F2:F10000) straight in A8.

The code in your question is evaluating the formula multiple times in the two loops if you have multiple cells that don't match. This is unnecessary and could be very slow for a large/complex spreadsheet.