marco running extremely slow and doesnt return result

Question

I was trying to make unique identifiers in two different worksheet and do a loopup see if any unmatch, if there is unmatch, will indicate "new this month" on sheet1, however, my code below is running very slow and I did not even get the result, please help

Sub Mycomp ()

Dim sht1 As Worksheet
Dim lastrow2 As Integer
Dim j As Integer
Dim m As Integer
Dim n As Integer

Dim sht4 As Worksheet
Dim lastrow As Integer
Dim i As Integer
Dim x As Integer
Dim a As Integer


Dim mycompariosn As Integer


'concatenate column 1and column 5 in sheet 1, and generate result in column 20

lastrow2 = Worksheets(1).UsedRange.Rows.Count

For n = 1 To lastrow2

Worksheets(1).Cells(n, 20).Value = Worksheets(1).Cells(n, 1).Value & Worksheets(1).Cells(n, 5).Value

Next n

'concatenate column 1and column 5 in sheet 1, and generate result in column 20

lastrow = Worksheets(4).UsedRange.Rows.Count


For a = 1 To lastrow

Worksheets(4).Cells(a, 20).Value = Worksheets(4).Cells(a, 1).Value & Worksheets(4).Cells(a, 5).Value

Next a

'compare the two column 20 in sheet 1 and sheet 4, find any new row in sheet1 with a return "New in This month" if it is new

For n = 1 To lastrow2
    For a = 1 To lastrow

    mycompariosn = StrComp(Worksheets(1).Cells(n, 20).Value, Worksheets(4).Cells(a, 20).Value, vbTextCompare)

    If mycompariosn = 1 Then Worksheets(1).Cells(n, 7).Value = "New in This month"

    Next a

Next n

End Sub

Answer 1

Two suggestions first

1. Do not use Integer as a variable type for rows in Excel. Use a Long. You may get Overflow errors in xl2007+
2. Please do not use UsedRange.Rows.Count to get the last row in a worksheet. You may want to see THIS

Now to address your question.

Instead of using a loop, use a formula to make your code faster. Also simply concatenating may not give you the correct result. For example, once you concatenate the below, it will be the same but are they actually the same?

Cell 1    Cell 2    Concatenate Cell

Sid       Rout      SidRout
Si        dRout     SidRout

To overcome this problem, use a delimiter which has not been used in a sheet. Perhaps something like "#YourName#"? So the above looks like

Cell 1    Cell 2    Concatenate Cell

Sid       Rout      Sid#user2864813#Rout
Si        dRout     Si#user2864813#dRout

Next thing is you can use .Formula instead of looping. That will make your code faster. See this example.

Const Delim As String = "#user2864813#"

Sub Mycomp()
    Dim sht1 As Worksheet, sht2 As Worksheet
    Dim lRow As Long

    Set sht1 = ThisWorkbook.Sheets(1)
    Set sht2 = ThisWorkbook.Sheets(4)

    With sht1
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        With .Range("T1:T" & lRow)
            .Formula = "=A1 & """ & Delim & """ & E1"
            .Value = .Value
        End With
    End With

    With sht2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        With .Range("T1:T" & lRow)
            .Formula = "=A1 & """ & Delim & """ & E1"
            .Value = .Value
        End With
    End With
End Sub

Now instead of looping to find matches, you can again use .Formula to find a match for whatever you are looking.

If you are doing a cell to cell match in a particular row then use this code to generate the result in col 21 or whatever empty column that you have.

sht2.Range("U1:U" & lRow).Formula = "=T1='" & sht1.Name & "'!T1"
sht2.Range("U1:U" & lRow).Value = sht2.Range("U1:U" & lRow).Value

If there is a match then you will get a TRUE else you will get a FALSE

If you are not doing a cell to cell match in a particular row but checking for the existence of a word in a column then use =Countif(). Something like this

sht2.Range("U1:U" & lRow).Formula = "=COUNTIF('" & sht1.Name & "'!T:T,T1)"
sht2.Range("U1:U" & lRow).Value = sht2.Range("U1:U" & lRow).Value

If you get a value greater than 0 then there is a match found :)

Hope this gets you started.

Disclaimer: The above code is UNTESTED so please let me know if you face any problems.