CODEWITHSUNDEEP
vbaexcel
excelguy
excelguy

Reputation: 1624

VBA, Optimize VLookup/Formulas

I have a code, largely done from the macro recorder. Runs very slow. I think this is due to the vlookups or formula, can anyone help make this code faster?

I also have filtering and copy and pasting, doubt these are slowing this down. Perhaps it is just because it is being use on a large data set. Can anything be done?

Sub filtering()
'


' filtering Macro
'



    Dim Rng1 As Range, Rng2 As Range
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
        Dim src As Range

    Set ws1 = ThisWorkbook.Sheets("eodcpos")
    Set ws2 = ThisWorkbook.Sheets("valumeasure3")
    Set ws3 = ThisWorkbook.Sheets("File")

    Set ws4 = ThisWorkbook.Sheets.Add
    ws4.Name = "lookup"

    Set ws5 = ThisWorkbook.Sheets("Sample File")

    Set Rng1 = ws1.UsedRange
    Set Rng2 = ws2.UsedRange
    Set src = Worksheets("File").Range("2:757")
    Set src1 = Worksheets("lookup").Range("2:17783")

'
   Rng1.AutoFilter Field:=11, Criteria1:= _
        "=Traded Position"

  Rng1.AutoFilter Field:=2, Criteria1:= _
        "<>*-C*", Operator:=xlAnd, Criteria2:="<>*-P*"

    Rng1.AutoFilter Field:=109, Criteria1:=Array _
        ("Foreign Exchange Forward", "Foreign Exchange Spot", "Foreign Exchange Swap"), _
        Operator:=xlFilterValues

    Rng1.AutoFilter Field:=33, Criteria1:= _
        "<>NA"

    Rng1.AutoFilter Field:=63, Criteria1:= _
        "<>129540", Operator:=xlAnd, Criteria2:="<>135845"

    Rng2.AutoFilter Field:=5, Criteria1:= _
        "=Buy Notional Amount", Operator:=xlOr, Criteria2:="=Sell Notional Amount"

    '' vlookup file

        ws4.Activate

        Range("A1").Value = "val pos id"
        ws2.Columns(3).Copy Destination:=Sheets("lookup").Columns(1)
        ws4.Range("A:A").RemoveDuplicates Columns:=Array(1)

        Range("B1").Value = "eodc pos id"
        ws1.Columns(2).Copy Destination:=Sheets("lookup").Columns(2)
        Range("C1").Value = "eodc pos decor id"
        ws1.Columns(41).Copy Destination:=Sheets("lookup").Columns(3)

        Range("D1").Value = "pos id lookup"
        Range("D2").Select
        ActiveCell = "=VLOOKUP(A2,B:B,1,FALSE)"
        Selection.AutoFill Destination:=src1.Columns("D")


        Range("E1").Value = "pos decor id lookup"
        Range("E2").Select
        ActiveCell = "=VLOOKUP(fxpd!B2,C:C,1,FALSE)"
        Selection.AutoFill Destination:=src1.Columns("E")

        ''Filtering File data
        ws4.UsedRange.AutoFilter Field:=4, Criteria1:= _
        "<>#N/A"

       ''creating File tab
        ws4.Columns(4).Copy Destination:=Sheets("File").Columns(1)  ''copy and paste filtered values from valuation measure file
        ws4.Columns(5).Copy Destination:=Sheets("File").Columns(2)  ''copy and paste filtered values from fxpd

        ws3.Activate


        Range("X2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BP,COLUMNS(B:BP),FALSE)"   ''product classification code
        Selection.AutoFill Destination:=src.Columns("X")

     '' ActiveCell.FormulaR1C1 = _
     ''   "=IF(RIGHT(LEFT(RC[20],64),40)=""ProductType:'FXD';ProductSubType:'SWLEG'"",""XSW"",IF(RIGHT(LEFT(RC[20],64),40)=""ProductType:'FXD';ProductSubType:'FXD'"",""FXD"",""NA""))"
     ''  Range("D2").Select
     ''   Selection.AutoFill Destination:=src.Columns("D")

        Range("D2").Select
        ActiveCell = "=IF(RIGHT(LEFT(X2,64),40)=""ProductType:'FXD';ProductSubType:'SWLEG'"",""XSW"",IF(RIGHT(LEFT(X2,64),38)=""ProductType:'FXD';ProductSubType:'FXD'"",""FXD"",""NA""))"
        Selection.AutoFill Destination:=src.Columns("D")

        Range("E2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BK,62,FALSE)"   ''counterparty short name  / client id
        Selection.AutoFill Destination:=src.Columns("E")
        Range("F2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BK,17,FALSE)"  ''source trade id  / deal id
        Selection.AutoFill Destination:=src.Columns("F")
        Range("G2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BK,27,FALSE)"   ''trade date / contract date
        Selection.AutoFill Destination:=src.Columns("G")
        Range("H2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:V,COLUMNS(B:V),FALSE)"   ''settlement date / actual settlement date
        Selection.AutoFill Destination:=src.Columns("H")
        Range("I2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:BG,COLUMNS(B:BG),FALSE)"   ''book runner / source book name
        Selection.AutoFill Destination:=src.Columns("I")

''lookup into fxpd now

          Range("J2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:AO,COLUMNS(B:AO),FALSE)"   ''pull in pos decorator id
        Selection.AutoFill Destination:=src.Columns("J")
             Range("K2").Select
        ActiveCell = "=VLOOKUP(B2,fxpd!B:U,COLUMNS(B:U),FALSE)"   ''spot rate / forward rate
        Selection.AutoFill Destination:=src.Columns("K")

        ws3.Columns(12).Copy Destination:=Sheets("File").Columns(11)   ''outright rate / forward rate

            Range("M2").Select
        ActiveCell = "=VLOOKUP(B2,fxpd!B:I,COLUMNS(B:I),FALSE)"   ''buy currency code / buy curency
        Selection.AutoFill Destination:=src.Columns("M")
         Range("N2").Select
        ActiveCell = "=VLOOKUP(B2,fxpd!B:AK,COLUMNS(B:AK),FALSE)"   ''sell currency code / sell curency
        Selection.AutoFill Destination:=src.Columns("N")




        src.Columns("O") = "LIVE"  ''hardcode type name
        src.Columns("P") = "1"  ''hardcode leg number
        src.Columns("Q") = "S"  ''hardcode leg duration code
        src.Columns("R") = ""  ''hardcode option value date



         Range("S2").Select
        ActiveCell = "=IF(valumeasure3!E2=""Buy Notional Amount"",VLOOKUP(A2,valumeasure3!C:U, COLUMNS(C:U),FALSE),0)"   ''buy currency amt
        Selection.AutoFill Destination:=src.Columns("S")


          Range("T2").Select
        ActiveCell = "=IF(valumeasure3!E2=""Sell Notional Amount"",VLOOKUP(A2,valumeasure3!C:U, COLUMNS(C:U),FALSE),0)"   ''sell curency amt
        Selection.AutoFill Destination:=src.Columns("T")

            Range("U2").Select
        ActiveCell = "=VLOOKUP(A2,eodcpos!B:I,8,FALSE)"   ''setup for buy risk currency flag
        Selection.AutoFill Destination:=src.Columns("U")


            Range("V2").Select
        ActiveCell = "=IF(U2=""Long"",""B"",""S"")"   ''buy risk currency flag
        Selection.AutoFill Destination:=src.Columns("V")

         Range("W2").Select
        ActiveCell = "=IF(S2<>""0"",T2/S2,""0"")"   ''
        Selection.AutoFill Destination:=src.Columns("W")



''headers
        Range("C1").Value = ""
         Range("D1").Value = "Product Family Name"
           Range("E1").Value = "Client name"
              Range("F1").Value = "deal ID"
                 Range("G1").Value = "trade Date"
                    Range("H1").Value = "settlement"
                       Range("I1").Value = "source book"
                          Range("J1").Value = "PD ID"
                             Range("K1").Value = "forward rate"
                                Range("L1").Value = "forward rate"
                                 Range("M1").Value = "buy currency"
                                        Range("N1").Value = "sell currency"
                                           Range("O1").Value = "type name"
                                               Range("P1").Value = "leg number"
                                                   Range("Q1").Value = "duration"
                                                           Range("R1").Value = "option value date"
                                                                Range("S1").Value = "buy ccy amt"
                                                                         Range("T1").Value = "sell ccy amt"
                                                                            Range("U1").Value = "N/A"
                                                                            Range("V1").Value = "buy risk ccy flag"
                                                                            Range("W1").Value = "sell buy ratio"



     'headers for Sample Data sheet
     Range("A1").Value = "Product Family Name"
     Range("B1").Value = "Client Name"
        Range("C1").Value = "Deal ID"
         Range("D1").Value = "Amendment Number"
           Range("E1").Value = "Trade Date"
              Range("F1").Value = "Settlement Date"
                 Range("G1").Value = "Book Runner"
                    Range("H1").Value = "Leg Status Type Name"
                       Range("I1").Value = "Leg Number"
                          Range("J1").Value = "Leg Duration Code"
                             Range("K1").Value = "Spot Rate"
                                Range("L1").Value = "Outright Rate"
                                 Range("M1").Value = "Buy Currency Code"
                                        Range("N1").Value = "Sell Currency Code"
                                           Range("O1").Value = "Buy Currency Amt"
                                               Range("P1").Value = "Sell Currency Amt"
                                                   Range("Q1").Value = "Buy Risk Currency Flag"
                                                           Range("R1").Value = "Option Value Date"
                                                                Range("S1").Value = "Sell Buy Ratio"
       ' Copying data from File sheet
       ws3.Columns(4).Copy Destination:=Sheets("Sample File").Columns(1)
       ws3.Columns(5).Copy Destination:=Sheets("Sample File").Columns(2)
       ws3.Columns(6).Copy Destination:=Sheets("Sample File").Columns(3)
       src.Columns("D") = "1"  ''hardcode amendment number
       ws3.Columns(7).Copy Destination:=Sheets("Sample File").Columns(5)
       ws3.Columns(8).Copy Destination:=Sheets("Sample File").Columns(6)
       ws3.Columns(9).Copy Destination:=Sheets("Sample File").Columns(7)
       src.Columns("H") = "LIVE"  ''hardcode type name
       src.Columns("I") = "1"  ''hardcode leg number
       src.Columns("J") = "S"  ''hardcode leg duration code
       ws3.Columns(11).Copy Destination:=Sheets("Sample File").Columns(11)
       ws3.Columns(11).Copy Destination:=Sheets("Sample File").Columns(12)
       ws3.Columns(13).Copy Destination:=Sheets("Sample File").Columns(13)
       ws3.Columns(14).Copy Destination:=Sheets("Sample File").Columns(14)
       ws3.Columns(19).Copy Destination:=Sheets("Sample File").Columns(15)
       ws3.Columns(20).Copy Destination:=Sheets("Sample File").Columns(16)
       ws3.Columns(21).Copy Destination:=Sheets("Sample File").Columns(17)
       src.Columns("R") = ""  ''hardcode option value date
       ws3.Columns(22).Copy Destination:=Sheets("Sample File").Columns(19)

   End Sub

Upvotes: 1

Views: 238

Answers (4)

u8it
u8it

Reputation: 4296

With all those VLookup equations you'll probably speed things up using Application.Calculation = xlCalculationManual at the very beginning of your macro, while you should set your calculation back to Application.Calculation = xlCalculationAutomatic when your macro finishes, and perhaps use error handling to make sure this happens as discussed here: https://stackoverflow.com/a/31923310/3546415

Also, you're autofilling entire columns. That's probably not what you want and it will really slow you down.

You'll speed things up if you limit your autofilling to a limited range like this, where autofilling stops at row 99...

Range("E2").AutoFill Destination:=Range("E2:E99")

And your VLookups might be improved if you limit them to specific ranges like this eodcpos!$B$1:$BK$9 rather than entire columns like this eodcpos!B:BK...

Range("E2") = "=VLOOKUP(A2,eodcpos!$B$1:$BK$99,62,FALSE)"

Might as well do Application.ScreenUpdating = False as well. And you can probably shave off a few seconds with other general improvements that are pretty easy to find if you Google VBA optimization or such.

Beyond that, you've got a lot of code there and probably a lot of room to make improvements. Like you said, you might be experiencing slowness because of the actual spreadsheet.

If you're still having problems. I'd suggest going through your code and reporting the time at various points like this...

Debug.Print Now & "Say what the code just did here"

You can then narrow down your slow sections to specific actions and focus on what's costing you the most time.

I imagine it's the autofilling and recalculating of all those VLookups. VLookup will really slow things down when used extensively. If slowness is still too much of a problem even when not auto-recalculating, then Id consider how to redesign the spreadsheet and/or VBA to use VLookups less.

Upvotes: 1

Harassed Dad
Harassed Dad

Reputation: 4704

Don't Select cells! 

  Range("D2").Select
    ActiveCell = whatever
    Selection.AutoFill Destination:=src.Columns("D")

Should be

 With Range("D2")
  .formula = whatever
  .autofill Destination := src.columns(4)
end with

Upvotes: 2

Zerk
Zerk

Reputation: 1593

As for speed, you can use the following at the start of the sub:

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

And restore them at the end of the sub

With Application
    .ScreenUpdating = True
    .DisplayAlerts = True
End With

The main red flag with this code though is that it is incredibly brittle. Row and column extents and references are hardcoded throughout the sub, the slightest change will cause things to fall over.

Additionally you are copying entire columns, you could limit the copy to the extents of the data and potentially combine contiguous copies to single transactions.

Upvotes: 0

Seo Yat Lau
Seo Yat Lau

Reputation: 1

You can try to turn off screen updating at the start of code. Application.ScreenUpdating = False

Remember to turn it back on at the end of code. Application.ScreenUpdating = True

Upvotes: -1

Related Questions