On May 8, 4:52*pm, joel wrote:
the code assume there is a sheet caleed Check

Sub GetcheckData()

Set Salesht = Sheets("Sale")
Set DataSht = Sheets("Data")
Set CheckSht = Sheets("Check")
CheckSht.ClearContents

CheckRow = 2

With DataSht
* *'copy head row
* *.Rows(1).Copy Destination:=CheckSht.Rows(1)
* *LastRow = .Range("E" & Rows.Count).End(xlUp).Row
* *For RowCount = 2 To LastRow
* * * If .Range("E" & RowCount) < "" Then
* * * * *Data = .Range("E" & RowCount)
* * * * *Set c = Salesht.Columns("A").Find(what:=Data, _
* * * * * * LookIn:=xlValues, lookat:=xlWhole)
* * * * *'if data is not found in sheet Sales
* * * * *If Not c Is Nothing Then
* * * * * * .Rows(RowCount).Copy _
* * * * * * * *Destination:=CheckSht.Rows(RowCount)
* * * * * * RowCount = RowCount + 1
* * * * *End If
* * * End If
* *Next RowCount
End With
End Sub



"Angela" wrote:
Hello friends,

I have a set of work sheets in a workbook.

sheet1 name is (DATA)
sheet2 name is (SALE)

In sheet SALE, cell B2 value is either blank or "Found" using formula
=IF(A2<"","FOUND","") and it continues for each value of found in
corresponding columnA.

In sheet DATA, cell I2, value is either "Check" or "Found" using =IF(IF
(E2<"",ISNA(VLOOKUP(E2,SALE!$A$2:$B$65535,2,FALSE )),"")
<FALSE,"Check","Found") and it continues for each value found in
corresponding columnE that matches with columnA value of sheet (SALE).

Now problem is that it takes too much time to calculate formulas in
both sheets since data is extended to 30000+ lines.

I would also like to generate a new worksheet with name "CHECK" which
should contain only check data from sheet(DATA). I can do this with
pivot table but I would like to get this using VB.

Hope I have explained things clearly here.

Would appreciate if someone can code all the formulas to reduce
calculation time of workbook.

Thx.- Hide quoted text -

- Show quoted text -

Hi Joel,

Thanks for the reply.
On line "CheckSht.ClearContents" it is giving an error "Object doesn't
support this property or method".

In check sheet, the result is like one line skip and another.
Example if in sale sheet I have

1745
1746
1747
1748
1749

Then result I get in check sheet is
1745

1747

1748

Why is that so?
Would appreciate if you can check it pls.

Thx.