INSTR or MATCH - Unsure how to tackle logic

[Sphinx404]

Good Morning Experts,

Please see code below. I tried to explain what I want to do, but I'm having trouble putting it all together. Any solutions, guidance or advice would be appreciated.

Sub HELPME()


Dim lrow As Long
Dim myworksheet As Worksheet
Dim myWorksheet2 As Worksheet
Dim lrow2 As Long


Set myworksheet = Worksheets("Sheet1")
lrow = myworksheet.Cells(myworksheet.Rows.Count, "A").End(xlUp).row
Set myWorksheet2 = Worksheets("Sheet2")
lrow2 = myWorksheet2.Cells(myWorksheet2.Rows.Count, "A").End(xlUp).row


Application.ScreenUpdating = False
Application.DisplayAlerts = False


r = 2


Do Until r = 2 & lrow2


lookup_cell = Sheet1.Range("B" & r)[COLOR=#008000] 'look up this cell - SHEET1[/COLOR]
lookup_range = Sheet2.Range("B" & r) [COLOR=#008000]'look for it in this range - SHEET2[/COLOR]
compare_cell1 = Sheet2.Range("A" & r) [COLOR=#008000]'IF FOUND,compare_cell1 vs. compare_cell2[/COLOR]
compare_cell2 = Sheet1.Range("A" & r)
return_value = Sheet1.Range("A" & r) [COLOR=#008000]'If the compared cells match, then return TRUE, otherwise FALSE in SHEET1 "C2"[/COLOR]


[COLOR=#ff0000]        if istr(lookup_cell,[/COLOR]
    
r = r + 1


Loop


End Sub

 

[Sphinx404]

That last line should read

return_value = [COLOR=#ff0000][B]Sheet2[/B][/COLOR].Range("A" & r) [COLOR=#008000]'If the compared cells match, then return TRUE, otherwise false in SHEET1 "C2"[/COLOR]

 

[Fluff]

Is this what you're after

Sub HELPME()


Dim lrow As Long
Dim myworksheet As Worksheet
Dim myWorksheet2 As Worksheet
Dim lrow2 As Long


Set myworksheet = Worksheets("Sheet1")
lrow = myworksheet.Cells(myworksheet.Rows.Count, "A").End(xlUp).Row
Set myWorksheet2 = Worksheets("Sheet2")
lrow2 = myWorksheet2.Cells(myWorksheet2.Rows.Count, "A").End(xlUp).Row


Application.ScreenUpdating = False
Application.DisplayAlerts = False


R = 2


Do Until R = lrow2
   Sheet1.Range("C2").Value = False
   If Sheet1.Range("B" & R).Value = Sheet2.Range("B" & R).Value Then
      If Sheet2.Range("A" & R).Value = Sheet1.Range("A" & R).Value Then
         Sheet1.Range("C2").Value = True
         Exit Do
      End If
   End If
       
   R = R + 1
Loop



End Sub

Not sure if the sheet names are correct, as you seem to be mixing sheet names & sheet codenames

 

[Sphinx404]

I'm sorry if it confuses you... I just wanted to make it as generic as possible so it was more easily understood. I've been working this problem all **** day... Search, Index, Match, Find, etc. etc... Articles, videos... I'm at my wits end with this problem.

It's directly related to this post I put up a couple of months back, but couldn't work it out:

https://www.mrexcel.com/forum/excel-questions/1031225-vlookup-table-user-defined-argument.html

Still the same problem, and it's not the logic I'm having trouble with... it's the coding.

 

[Fluff]

Not quite sure If I've understood correctly, but maybe something like this

Do Until R = lrow2
   Sheet1.Range("C2").Value = False
   If InStr(1, Sheet1.Range("B" & R).Value, Sheet2.Range("B" & R).Value, vbTextCompare) > 0 Then
      If Sheet2.Range("A" & R).Value = Sheet1.Range("A" & R).Value Then
         Sheet1.Range("C2").Value = True
         Exit Do
      End If
   End If
       
   R = R + 1
Loop

If the value from sheet1 exists anywhere in the value from sheet2 it will move to the next if

 

[Sphinx404]

Thanks @Fluff

This took to me to where I needed to be with my code. Always appreciated.

 

[Fluff]

Glad to help & thanks for the feedback