Checking if defined pairs of values exists in big range - performance issue

[balth]

Hello,

I have been reading this forum for a long time and so many times was able to find solutions to the challenges I was facing so thank you all for all your contributions!

However finally came to the the point where I had to register to write the first post as I am struggling with one of the macros I have put together.

In general for each cell in column Y I need to check if there is at least one row in the spreadsheet where value in column A is the same as value in column A in the row where the cell is, and the value in column J is "yes".

I have adjusted some code I have came across online and it seems that it is working for short ranges. Unfortunately I have more than 100000 rows in the spreadsheet and this is just killing the macro.

Pasting the code below. Could you please advise me how can I make it more efficient?

Sub Macro1()

On Error Resume Next


Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False


Dim rng As Range, cell As Range
Dim FindString As String
Dim rng2 As Range
Dim count As Long
Dim myRow As Range

Set rng = Sheets("Sheet1").Range("Y2:Y100000")

For Each cell In rng
count = 0
FindString = cell.Offset(0, -24).Value

For Each myRow In Range("A2:X100000").Rows

If myRow.Cells(1, 10).Value = "yes" And myRow.Cells(1, 1).Value = FindString Then

count = count + 1

End If

If count > 0 Then Exit For

Next myRow

If count > 0 Then
cell.Value = "yes"
Else
cell.Value = "no"

End If

Next cell

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True


End Sub

Thank you in advance!