Like expression cause performance problem

[SHIRLEY YEUNG]

Here is part of the codes. I want to use the statements to check if the email subject matches to any of the subjects saved in column K. It can execute without problem. However, email subject can vary from time to time, but still it should contains some key words unchanged. So I tried to use Like instead of = . But when I execute it hanged my Excel. Please give me some advice. Thanks!

Range("K2").Select
For Each A In Range(Selection, Selection.End(xlDown))
subjectVal = A.Value

If UCase(OSubject) = UCase(subjectVal) Then
ActiveCell.Offset(0, -7).Range("A1").Select
------------------------------------------------------
Range("K2").Select
For Each A In Range(Selection, Selection.End(xlDown))
subjectVal = A.Value

If UCase(OSubject) Like "*" & UCase(subjectVal) & "*" Then
ActiveCell.Offset(0, -7).Range("A1").Select

 

[SHIRLEY YEUNG]

Hi Norie,

Thanks! Your codes works!
Yes, the codes are being run in Excel. I do not need

Set wb = ThisWorkbook

I have changed the codes as below.

Set Rng = Workbooks(ProcessorName).Sheets("XrefEmail").Range("C2")
For Each A In Range(Rng, Workbooks(ProcessorName).Sheets("XrefEmail").Cells(Rows.Count, Rng.Column).End(xlUp))
    senderval = A.Value
    subjectVal = A.Offset(0, 8).Value
 
  If (UCase(senderval) = UCase(TempOsender)) And (UCase(OSubject) Like "*" & UCase(subjectVal) & "*") Then
    A.Offset(0, 1).Select
 
    ArchiveNameTag = ActiveCell.Text

 

[Norie]

Well glad it works but why don't you need Set wb=ThisWorkbook?

You don't have to use it but it could make like a lot easier, it might even extend the lifetime of your keyboard.

Also, why are you still using Select/ActiveCell when it's not needed - that could even have been the root of the problem in the first place.

If you want to put the value in the cell to the right of A in ArchiveTag you can just use this.

ArchiveTag =A.Offset(,1).Text

 

[SHIRLEY YEUNG]

Yes I will change it. I never touched the vba before. I was just using my limited knowledge to modify the codes written by other. I should practise more in the future to built up the technique.