I have a sheet with over 600 rows in Column A. What Im trying to do is to see if a particular set of 20 characters from Column A are also in Column BI.
I have come up with the following code but unfortunately it doesnt work.
All help greatly appreciated
Option Explicit Public ClaimFound As Boolean Public Sub FindDataInRange(r As Range, target As Variant) 'searches for target in a Range ,r, Dim r1 As Range Set r1 = r.Find(target) If r1 Is Nothing Then ClaimFound = False Else: ClaimFound = True End If End Sub Sub Button171_Click() Dim ClaimNo As String Dim strExtract As String Dim cell As Range For Each cell In Worksheets("Claim Errors").Range("A3", _ Worksheets("Claim Errors").Range("A6536").End(xlUp)).Cells strExtract = cell ClaimNo = Mid(strExtract, 11, 20) 'See if this claim has previously been confirmed for this error FindDataInRange ActiveSheet.Range("BI3:BI2000"), ClaimNo If cell.Offset(0, 8) = "Yes" And ClaimFound = True Then cell.Offset(0, 8) = "Confirm" End If Next cell End Sub