David,

I don't see where CrosstabRowHeader and CrosstabColHeader become arrays, as
far as I can see they are ranges.

You didn't do what either Tom or I suggested, so how would you expect it to
work. If you use Evaluate, that should work, but you will need to use Range
strings, and if the value to be tested against is a string, you will need to
add quotes around the variable in the evaluate statement.
--

HTH

RP

"David" wrote in message
...
Tom & Bob,
Thanks for your help. I'm still floundering though ...
I'm trying to populate a Crosstab summary of a database using VBA arrays
and
then transferring the result back to the worksheet. Please have a look at
the
code below for me. The WorksheetFunction.Sumproduct arguments seem to be
the
sticking point:

Sub PopulateCrossTab()
'Count ocurences of Colour and Price Code _
combinations in database
Dim Field_ColourData As Variant
Dim Field_PriceCodeData As Variant
Dim CrosstabRowHeader As Variant
Dim CrosstabColHeader As Variant
Dim TempArray() As Variant

'Load arrays
Field_ColourData = Range("B12:B23")
Field_PriceCodeData = Range("C12:C23")
CrosstabRowHeader = _
Range("Crosstab").Resize(, 1).Offset(, -1)
CrosstabColHeader = _
Range("Crosstab").Resize(1).Offset(-1)

'Use Sumproduct to calculate Crosstab values _
and store in TempArray
ReDim TempArray(1 To UBound(CrosstabRowHeader, 1), _
1 To UBound(CrosstabColHeader, 2))
For r = 1 To UBound(CrosstabRowHeader, 1)
For c = 1 To UBound(CrosstabColHeader, 2)
TempArray(r, c) = _
WorksheetFunction.SumProduct(--(CrosstabColHeader(c) =
Field_ColourData), _
--(CrosstabRowHeader(r) = Field_PriceCodeData))
Next
Next

'Transfer TempArray to worksheet
Range("Crosstab") = TempArray
End Sub


"Tom Ogilvy" wrote:

?Application.WorksheetFunction.Sumproduct(array(2) ,array(3))
6