CODEWITHSUNDEEP
vbaexcel
Jenny
Jenny

Reputation: 451

Error, in comparing two sheets

I have two sheets, S and P.

I am trying to compare the Id between two sheets. IF I the ID matches the I pull the corresponding details of the matched ID and paste them in sheet3.

For this task, First I copied the column containing the ID in my sheet "Data". Then I compared this ID with the Id in Sheet "P". After comparision, if the ID matches, then I take the complete row details of Sheet "P".

I am using the below code for this.

During my comparison, I could see that, just in one particular line 149 in my data sheet, the data I am extracting from sheet P is not getting copied according to the code.

I also, crosschecked with my sheet "P", if they are resembling the same , but there is no problem with my data in sheet "P" as well.

Could someone help me to figure out, what is wrong with my code and why particularly in line 149, this happens. ? 

Sub lookup()
Dim lLastrow As Long
Dim rng As Range
Dim i As Long

'Copy lookup values from sheet1 to sheet3
ThisWorkbook.Sheets("S").Select
lLastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
Range("P5:P" & lLastrow).Copy Destination:=Sheets("Data").Range("E5")
Range("G5:G" & lLastrow).Copy Destination:=Sheets("Data").Range("H5")
'Go to the destination sheet
Sheets("Data").Select
For i = 5 To lLastrow
'Search for the value on sheet2
Set rng = Sheets("P").UsedRange.Find(Cells(i, 5).Value & "*", LookAt:=xlWhole)
'If it is found put its value on the destination sheet
If Not rng Is Nothing Then
Cells(i, 6).Value = rng.Value
Cells(i, 1).Value = rng.Offset(0, 1).Value
Cells(i, 2).Value = rng.Offset(0, 2).Value
Cells(i, 3).Value = rng.Offset(0, 3).Value
Cells(i, 4).Value = rng.Offset(0, 9).Value
Cells(i, 9).Value = rng.Offset(0, 10).Value
Cells(i, 13).Value = rng.Offset(0, 6).Value
Cells(i, 14).Value = rng.Offset(0, 5).Value
Cells(i, 15).Value = rng.Offset(0, 4).Value
Cells(i, 16).Value = rng.Offset(0, 8).Value
End If
Next i
End Sub

EDIT So here you could see that the row, 149 where the column A containing the ID must be in column B whereas it is in column A, also the column with data should actually be in column I . This is happening only with this particular row. I though the data is wrong and deleted the whole data but still with the new data in this particular row the problem exist. The data in the Shee P exist in the corresponding columns

Upvotes: 0

Views: 110

Answers (3)

Slaqr
Slaqr

Reputation: 563

While I can't see an exact reason why the code would fail on one row, I have taken the liberty to rewrite your code to atleast get rid of 'select', which is prone to errors. This code should be sturdier than the original, and might help solve this issue.

Are you doing the comparison between two specific columns? Then you could replace UsedRange.Find with Columns(1).Find.

Sub lookup()
 Dim lLastrow As Long
 Dim rng As Range
 Dim i As Long

'Copy lookup values from sheet1 to sheet3
With Sheets("S")
  lLastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
  .Range("P5:P" & lLastrow).Copy Destination:=Sheets("Data").Range("E5")
  .Range("G5:G" & lLastrow).Copy Destination:=Sheets("Data").Range("H5")
End with

For i = 5 To lLastrow
  'Search for the value on sheet2
  With Sheets("P")
       Set rng = .Columns(1).Find(Sheets("Data").Cells(i, 5).Value & "*", LookAt:=xlWhole)
  End with

  'If it is found put its value on the destination sheet
  If Not rng Is Nothing Then
    With Sheets("Data")
      .Cells(i, 6).Value = rng.Value
      .Cells(i, 1).Value = rng.Offset(0, 1).Value
      .Cells(i, 2).Value = rng.Offset(0, 2).Value
      .Cells(i, 3).Value = rng.Offset(0, 3).Value
      .Cells(i, 4).Value = rng.Offset(0, 9).Value
      .Cells(i, 9).Value = rng.Offset(0, 10).Value
      .Cells(i, 13).Value = rng.Offset(0, 6).Value
      .Cells(i, 14).Value = rng.Offset(0, 5).Value
      .Cells(i, 15).Value = rng.Offset(0, 4).Value
      .Cells(i, 16).Value = rng.Offset(0, 8).Value
    End with
  End If
Next i
End Sub

Upvotes: 1

user8285860
user8285860

Reputation:

@Vityata is correct to a point, your problem is the line below.

Change 

Sheets("P").UsedRange.Find(Cells(i, 5).Value & "*", LookAt:=xlWhole)

to

Set rng = Sheets("P").Cells(i, 5)

You are setting "rng" variable to the value in the cell in that line of code.

Then you use this line of code, If Not rng Is Nothing to check if there is a value in "rng", and if there is, then move the data from Sheets("P") to Sheets("S").

You have no need of a wildcard etc.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

Just guessing. Can you try like this:

With Worksheets("P")
    Set rng = .UsedRange.Find(.Cells(i, 5).Value & "*", LookAt:=xlWhole)
end with

It could be working and it could be the reason for the wrong result. This is how you refer to a range object in VBA - https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-object-excel

Upvotes: 0

Related Questions