Lookup code - I am missing something from my code!!

[jaedong1221]

Hi everyone:


1. I have two sheets, "Sheet1" and "Sheet2" containing data and they
are in the same workbook.

2. "Sheet2" have some data from "Sheet1" but also includes different
data as well.


3. I can find new data (different) data in "Sheet2" by using vlookup.
however I have macro running on sheet1 to create pivot table report.
I am just missing those new data from Sheet2.
4. Both "Sheet1" and "Sheet2" has data from column A to X. Common
lookup column being D. Row numbers change each month.


I got this code at the moment and instead of attaching all the new
data in "Sheet2" to "Sheet1" it just copies all the data in "Sheet1"
and paste right under the bottom in "Sheet1".

Can anybody point out what is wrong with this code?

I defined "Look" as Range of Column("D:X") in Sheet1.
"Testing" as workbook containing 'Sheet1'




Option Explicit
Option Base 1



Sub UpdateSheet1()

Dim DataArray(65000, 24) As Variant
Dim Fnd As Double
Dim X As Double
Dim Y As Double
Dim Z As Double
Dim LookupRng As Range
Dim Res As Variant

Sheets("Sheet1").Select
Set LookupRng = Workbooks("Testing.xls").Names("Look").RefersToRange

X = 1
Do While True
If Cells(X, 1).Value = Empty Then Exit Do
Res = Application.VLookup(Cells(X, 4), LookupRng, 4, False)
If (IsError(Res)) Then
'Else
Fnd = Fnd + 1
For Y = 1 To 24
DataArray(Fnd, Y) = Cells(X, Y).Value
Next
End If
X = X + 1
Loop

Windows("Testing.xls").Activate
Sheets("Sheet1").Select
Range("A65000").End(xlUp).Select
'This is a row with data, this row + 1 is empty'
X = ActiveCell.Row + 1
For Y = 1 To Fnd
For Z = 1 To 24
Cells(X, Z).Value = DataArray(Y, Z)
Next
X = X + 1
Next
End Sub


thank you!

regards,

James.

[lucas]

Hi James,
If you select your code and hit the VBA button when posting it will be formatted as I have done to your code in the first post.

Can you explain a little more what you mean by this and it seems to lead up to your question but I'm not sure what you want to do since this seems not to be it:

I got this code at the moment and instead of attaching all the new
data in "Sheet2" to "Sheet1" it just copies all the data in "Sheet1"
and paste right under the bottom in "Sheet1".