0

I have three sheet, Sheet1 , sheet2, and sheet3. Sheet3 is my result sheet.

I have the ID in column E of sheet3, copied from Column P of sheet1. I compare the ID of sheet3, with ID of sheet2. I am successful. but, i have an issue while comparing. The ID are generally 11 to 13 Digit Long. Case1, in few cases i have id in sheet 3 as D2C12682300 and in sheet2 the same ID as D2C1268230000, in this case, i want them to be matched, but according to my code, it is not getting matched.

Case2, in somecase i have the id in sheet3 as D2C12682300_id4576901 and in the sheet2 i have the same id as D2C1268230000. I want them to be matched, but my code is not working this way.

Could someone suggest, how i could include These condition in my code.I am struck how to do it.

Below is the code, i am using to look for id from sheet3 to sheet2. I want to include These cases in this code. 

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("Result").Range("E5")
Range("G5:G" & lLastRow).Copy Destination:=Sheets("Result").Range("H5")
'Go to the destination sheet
Sheets("Result_").Select
For i = 5 To lLastRow
'Search for the value on sheet2
Set rng = Sheets("P").UsedRange.Find(Cells(i, 5).Value)
'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, 12).Value = rng.Offset(0, 6).Value
Cells(i, 13).Value = rng.Offset(0, 5).Value
Cells(i, 14).Value = rng.Offset(0, 8).Value
End If
Next i
End Sub
Mikz
  • 571
  • 2
  • 11
  • 29

1 Answers1

0

Use a Wildcard:

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

Also avoid using .Select and objectify .Range, .Cells etc. Read How to Avoid Select.

Plagon
  • 2,689
  • 1
  • 11
  • 23
  • what does the Wildcard mean ? Sorry, i am learning VBA so i dint get it. – Mikz Jul 11 '17 at 09:40
  • There are different kinds of [wildcards](https://msdn.microsoft.com/en-us/library/ee440632(v=office.12).aspx). The `*` means any number of chars instead of it, i.e. If `.Cells(i, 5).Value = "D2C12682300"` and you want to find a match starting with this string. `.Find("D2C12682300" & "*")` would match `D2C12682300_id4576901` where the wildcard would be stand for `_id4576901` – Plagon Jul 11 '17 at 09:50
  • but this is for a single case. How would i do it for multiple cases, wherever i find my cases with D2C12682300_id4576901 , or with other id , i should be able to match it.Also, not everytime i will These cases. sometimes i just have D2C126838500. – Mikz Jul 11 '17 at 09:54
  • This is just an example, to show you how it works. Just try out `Set rng = Sheets("P").UsedRange.Find(Cells(i, 5).Value & "*")`. You will assign `.Cells(i,5).Value` dynamically and you will find any string that start with it using the wildcard after. Its best to try it out yourself and you will see what it does. You can use `F8` to go trough the code line by line, hover over variables and you can see the values. – Plagon Jul 11 '17 at 09:57
  • thank you for the Suggestion. I will work on it and try to figure it out. – Mikz Jul 11 '17 at 09:59
  • @UGP would it be worth using the '?' wildcard instead? – mojo3340 Jul 11 '17 at 10:02
  • @mo.h As it would only stand for one character and the strings he is looking for have more than one extra char, it wouldnt make much sense in this case. – Plagon Jul 11 '17 at 10:24
  • @UGP I tried the Wildcard you suggested me , it does not work – Mikz Jul 11 '17 at 10:50
  • is it possible i could use an left function ? – Mikz Jul 11 '17 at 11:09
  • What does it do? Is `Cells(i, 5).Value` on another `Sheet`? – Plagon Jul 11 '17 at 11:10
  • Means ? i am not understanding your Point – Mikz Jul 11 '17 at 15:12
  • They value you are searching for is on Sheet "P"? – Plagon Jul 11 '17 at 16:11