I'm trying to use a technique of speeding up code by transferring the
data to VBA array(s), processing the array(s) then transferring the
processed data back to the worksheet.
When I type the example code from John Green's "Excel 2000 VBA
PROGRAMMER'S REFERENCE" (p155) into my VBA Editor and run it on a
Worksheet with "Kee" in some of the rows of column B and numbers in
Column F it works perfectly, producing discount figures in column G in
rows that have "Kee" in column B.
John Green's code follows:

Public Sub KeeDiscount()
Dim vaSalesData As Variant
Dim vaDiscount() As Variant
Dim i As Long

vaSalesData = Range("A2:F73").Value
ReDim vaDiscount(1 To UBound(vaSalesData, 1), 1 To 1)
For i = 1 To UBound(vaSalesData, 1)
If vaSalesData(i, 2) = "Kee" Then
vaDiscount(i, 1) = vaSalesData(i, 6) * 0.1
End If
Next i
Range("G2").Resize(UBound(vaSalesData, 1), 1).Value = vaDiscount
End Sub

When I try to use this technique my processed data is not being
transferred back to the worksheet. What on Earth am I doing wrong?

My workbook has two sheets. Sheet1 has a list of words in column A,
starting at A2, directly below the heading List1 in A1.There are no
blank cells in the list.
Sheet2 has a list of words in Column A, starting at A2, directly below
the heading List2 in A1.There are no blank rows there either.
Some of the words in List2 are also in List1 on Sheet1.
All I want my code to do is place into column B of Sheet1 a list of all
the words from List2 (Column A, Sheet2) that are not in List1 (Column
A, Sheet1). The only part of my code that is refusing to work is the
last line, where I am wanting to Resize Range B2 on Sheet1 to the
correct size for accepting all of the processed array data in
vaUniques.

My Code follows:


Public Sub List1_Not_On_List2()
Dim vaList1 As Variant, vaList2 As Variant, vaUniques() As Variant
Dim I As Long, J As Long, K As Long
Dim List1Rows As Long, List2Rows As Long
List1Rows = Application.CountA(Sheet1.Range("A2:A65536"))
List2Rows = Application.CountA(Sheet2.Range("A2:A65536"))
With Sheet1
vaList1 = .Range(.Cells(2, 1), .Cells(List1Rows + 1, 1)).Value
End With
With Sheet2
vaList2 = .Range(.Cells(2, 1), .Cells(List2Rows + 1, 1)).Value
End With
For I = 1 To UBound(vaList1)
For J = 1 To UBound(vaList2)
If vaList1(I, 1) = vaList2(J, 1) Then
Let vaList2(J, 1) = ""
End If
Next J
Next I
For I = 1 To UBound(vaList2)
If vaList2(I, 1) < "" Then
Let K = K + 1
ReDim Preserve vaUniques(K)
Let vaUniques(K) = vaList2(I, 1)
End If
Next I
Sheet1.Range("B2").Resize(UBound(vaUniques, 1), 1).Value = vaUniques
End Sub

Can anybody see what I'm doing wrong?
I'm not wanting another method, I just want this Resize to work the way
it works for John Green. To date I've had to resort to using a loop to
get the data onto the worksheet. John's technique gets the data in
place in one fell swoop.

Ken Johnson