Speed up if...next VBA

[olwy]

Dear all,

having this macro involved in my list, it takes ages until it is done (200 sec aprox.). As it should be just a part of longer macro, I need to figure out, how to speed it up.
Any idea I can understand? ( I am macro begginers. :-) )

This code should be instead of VLOOKUP function. TREndRow has value 1500, PocetBridge around 4000. It goes one by one - I thing that is why is is so slow.

Sub svyhledatBridge()


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


Dim TBEndRow As Long
TBEndRow = Sheets("Trial Balance").Range("B9").End(xlDown).Row

Dim pocetBridge As Long
pocetBridge = Sheets("Bridge").Range("A2").End(xlDown).Row

  
   '
   For j = 2 To pocetBridge
          
   For i = 4 To TBEndRow
      
         
          If Sheets("Trial Balance").Cells(i, 20) = Sheets("Bridge").Cells(j, 1) Then
              
             Sheets("Trial Balance").Cells(i, 24) = Sheets("Bridge").Cells(j, 7)
             Exit For
          End If
        Next i
   Next j

Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic

End Sub

[jmac1947]

Hi olwy,

it would help us if you had a sample workbook to post, we always work better if we can see the spreadsheet and experiment with solutions

Jmac1947

[olwy]

Here is the file where I need it - it is without company data, so it looks a litte bit strange.
If anybody has more time, please, check, how could I replace all formulas in green. Normally, they are on each row.
(run macro separatelly, my composed macro will not work - as the Trial balance sheet is deleted.

Thanks a lot for any hint.
UpFile makrohrátky - funkční 01 - Copy.xlsm

[nilem]

Hi Olwy,
try it

Sub ertert()
Dim x, i&
With Sheets("Bridge")
    x = .Range("A1:G" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        .Item(x(i, 1)) = x(i, 7)
    Next i

    With Sheets("Trial Balance")
        x = .Range("T4", .Cells(Rows.Count, "T").End(xlUp)).Value
    End With
    For i = 1 To UBound(x)
        If .Exists(x(i, 1)) Then x(i, 1) = .Item(x(i, 1)) Else x(i, 1) = "not found"
    Next i
End With
Sheets("Trial Balance").Range("X4").Resize(i - 1).Value = x
End Sub

[olwy]

Holly crab,
you are genious. It takes less than wink. I cannot count it on seconds. Great, I am going to study, what has happend.
Olwy