How can increase the speed of if statement in VBA Code

0 votes

I created this if statement as a newbie using VBA to fill out a column in an excel table. The function is functioning well, but it took close to an hour to complete. It needs to move more quickly.

2600 rows make up Table 1. 3200 rows make up Table2.

Can I help me?

Sub PreencherO()

    Dim Current As Worksheet
    Dim His As Worksheet
    Dim Table1 As ListObject
    Dim Table2 As ListObject

    Set Table1 = ThisWorkbook.Worksheets("Current").ListObjects("Data")
    Set Table2 = ThisWorkbook.Worksheets("His").ListObjects("Historical")

    Dim irow As Long
    irow = Table1.ListColumns(1).Range.Rows.Count

    Dim lrow As Long
    lrow = Table2.ListColumns(1).Range.Rows.Count

    Dim i As Integer
    Dim l As Integer

    For i = 2 To irow
        For l = 2 To lrow
            If Table1.DataBodyRange.Cells(i, 6).Value = Table2.DataBodyRange.Cells(l, 6).Value Then
                Table1.DataBodyRange.Cells(i, 20).Value = "OLD"
            End If
        Next l
    Next i
End Sub

I need to increase the speed of it.

Feb 7, 2023 in Others by Kithuzzz
• 38,010 points
403 views

1 answer to this question.

0 votes

Use a Dictionary Object.

Option Explicit

Sub PreencherO()

    Dim Table1 As ListObject, Table2 As ListObject
    Dim i As Long, t0 As Single: t0 = Timer

    With ThisWorkbook
        Set Table1 = .Sheets("Current").ListObjects("Data")
        Set Table2 = .Sheets("His").ListObjects("Historical")
    End With

    Dim dict As Object, key As String
    Set dict = CreateObject("Scripting.Dictionary")

    ' fill dictionary with historic values
    With Table2.DataBodyRange
        'Debug.Print "Table2", .Address
        For i = 1 To .Rows.Count
            key = Trim(.Cells(i, 6))
            If Len(key) > 0 Then
                 dict(key) = i
            End If
        Next
    End With

    ' compare
    Application.ScreenUpdating = False
    With Table1.DataBodyRange
        'Debug.Print "Table1", .Address
        For i = 1 To .Rows.Count
            key = Trim(.Cells(i, 6))
            If dict.exists(key) Then
                 .Cells(i, 20).Value = "OLD"
            End If
        Next
    End With
    Application.ScreenUpdating = True

    MsgBox "Completed in " & Format(Timer - t0, "0.0 secs"), vbInformation
End Sub
answered Feb 7, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
0 answers

How can I get the intersection, union, and subset of arrays in Ruby?

I want to develop many methods for ...READ MORE

Aug 8, 2022 in Others by krishna
• 2,820 points
398 views
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,720 points
1,178 views
0 votes
1 answer

How can I store the data of an open excel workbook in a collection using BluePrism?

To do what you want is like ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,720 points
1,079 views
0 votes
1 answer

How can I replace the nth occurence using regex in VBA?

Use: =RegexReplace("This 9 is 8 a 77 6 ...READ MORE

answered Jan 13, 2023 in Others by narikkadan
• 63,720 points
915 views
0 votes
1 answer

VBA Loop To Import Changing File Names

You can use a FOR loop and ...READ MORE

answered Jan 12, 2023 in Others by narikkadan
• 63,720 points
671 views
0 votes
1 answer

Trying to run different macros based on value of cell in a range

This demonstrates one approach to loop through ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
342 views
0 votes
1 answer

How to add if this cell = 0 skip and go next

Try this: Option Explicit Sub AutoMakeInv() ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,720 points
289 views
0 votes
1 answer

My VBA macro slows down dramatically with each use

You have a function called ExportRange that ...READ MORE

answered Jan 21, 2023 in Others by narikkadan
• 63,720 points
559 views
0 votes
1 answer

How can this code be modified to increase the number of months instead of days?

You have to use dateadd. For i = 1 ...READ MORE

answered Feb 3, 2023 in Others by narikkadan
• 63,720 points
340 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,720 points
624 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP