Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

compare data in two sheets

  Asked By: Kaua    Date: Aug 10    Category: MS Office    Views: 937
  

I am writing a vba code ehich compares the value in sheet1 with the values in
sheet2.TO do so i made use of nested for loop but was taking sveeral minutes to
execute as the no of vaiues to compared are in excess of 10.000
for i=1o 40000
for j=1 to 50000
if sheet1.cells(i,1).value=sheet.cells(j,1).value then........

I tried using the following code to enhance the execution speed

Sub test()
i = 2
Do While i < 40000

curr_lot_no = sheet1.Cells(i, 1).Value
sheet2.select
On Error GoTo notfound

curr_lot_nbr_row_no = sheet2.Range(Cells(1, 1), Cells(50000,
1)).Find(what:=curr_lot_no, AFTER:=[A1], SearchDirection:=xlNext,
searchorder:=xlByRows).Row

sheet1.Cells(i, 4).Value = sheet2.Cells(curr_lot_nbr_row_no, 4).Value

notfound: i = i + 1
Loop

However i encountered the following error when no match was found i.e after
the statement "on error goto not found"

Run time error-91
object variable or with block variable not set

i fail to understand why the statement "on error goto "fails to execute.It
works only for the first run.
Is there any other efficient way to compare data in two sheets

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Jamie Roberts     Answered On: Aug 10

I would identify the matches on the sheet by using MATCH.

Put a formula in sheet1  cell B1 like =MATCH(A1,Sheet2!$A$1:$A$7,0) you will
get the row number of the corresponding value (or #N/A if there is no match).
You can then use the row number in the macro.

 
Answer #2    Answered By: Flynn Jones     Answered On: Aug 10

This is speculation, but ...

The "on error" fires and you jump to the "notfound" label. At this point,
you are in "error handler" mode. You then loop  and potentially have another
error. But you're already in error  mode, so a further error will not cause
the jump.

You could try putting an "on error goto 0" in the "notfound" code. That
might fix things.

Otherwise, you might need to exit from the loop and/or the sub to clear
error mode.

Speculation only - I could easily be completely off track.

 
Answer #3    Answered By: Kanchan Ap     Answered On: Aug 10

Trust something to do with Find.
If it cannot find its target, it returns NOTHING

 
Answer #4    Answered By: Haya Yoshida     Answered On: Aug 10

As suspected, the following code  works.

Sub test()
i = 2
Do While i < 40000

curr_lot_no = Sheet1.Cells(i, 1).Value
Sheet2.Select
On Error Resume Next 'GoTo notfound
If Not Sheet2.Range(Cells(1, 1), Cells(50000, 1)).Find(what:=curr_lot_no,
AFTER:=[A1], SearchDirection:=xlNext, searchorder:=xlByRows) Is Nothing Then
curr_lot_nbr_row_no = Sheet2.Range(Cells(1, 1), Cells(50000,
1)).Find(what:=curr_lot_no, AFTER:=[A1], SearchDirection:=xlNext,
searchorder:=xlByRows).Row

Sheet1.Cells(i, 4).Value = Sheet2.Cells(curr_lot_nbr_row_no, 4).Value
End If
notfound: i = i + 1
Loop
End Sub

 
Didn't find what you were looking for? Find more on compare data in two sheets Or get search suggestion and latest updates.




Tagged: