VBA code for formula

[AnthonyWB]

Hello everyone. I have followed many of the suggestions that were given to me w.r.t an automation I am trying to develope. But most resulted in fails.

I am using the following excel formula within a worksheet with approximately 20,000 records.

=IF(OR(E6="PV",E6="PV_C",E6="DC_FUT_B",E6="CETES"),IF(ISERROR(VLOOKUP(I6,[Illiquid]RVFL_C_Step_C!E:E,1,FALSE)),E6&"_1",E6&"_2"),"")

Originally I was going to paste the data necessary to compute the formula into the worksheet, and allow the formulas to automatically "update" after pasting. This is causing the macro to crash [ white screens, not responding etc.] This formula resides in column AC.

Is it possible that an actual VBA code to compute the formula as oppsed to an excel formula in the sheet will speed things up? Maybe a different excel formula? The Find_Method?

Any suggestions? I do not want to use access.

I can post the entire VBA code for the project if necessary.

[Domski]

Rather than performing the Vlookup on the whole column which I think will be very expensive calculation wise you might want to define the last row and determine the range to look at.

Dom

[AnthonyWB]

I'll give that a go, I doubt it will solve the problem. Below is the entire macro, Any improvements would be appreciated.

Option Explicit

Sub Macro1()
    Const sPath1 As String = "L:\12_31_2009_157_Reports\"
    Const sPath2 As String = "L:\12_31_2009_105_Support_Summaries\"
    Const SFileInp1 As String = "105xls_version 5.xls"
    Const SFileInp2 As String = "Formulas.xlsm"
    Const sFileOut1 As String = "12_31_2009_Data.xlsm"

    Workbooks.Add
    With ActiveSheet
        .Name = "12_31_2009_105_Data"
        .Parent.SaveAs Filename:=sPath1 & sFileOut1, _
                       FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                       CreateBackup:=False
        .Parent.Close
    End With

    Workbooks.Open Filename:=sPath1 & SFileInp1
        Range("A4:Z50000").Copy
               
    Workbooks.Open Filename:=sPath2 & SFileInp2
    With ActiveSheet
        Range("A1").Select
        .Paste
        .Name = "105_12_31_2009_version1"
        Range("C1").Value = "Book_ID"
        Range("E1").Value = "Deal_ID"
        Range("F1").Value = "Instrument_ID"
        Range("G1").Value = "Trade_Type"
        Range("H1").Value = "Security_ID"
        Range("I1").Value = "Trade_ID"
        Range("J1").Value = "PR_Flag"
        Range("K1").Value = "Amortized_Cost_USD_12/31/2009"
        Range("L1").Value = "MTM_USD_12/31/2009"
        Range("M1").Value = "Unrealized_P/L_USD_12/31/2009"
        Range("N1").Value = "Amortized_Cost_USD_11/31/2009"
        Range("O1").Value = "MTM_USD_11/31/2009"
        Range("P1").Value = "Unrealized_P/L_USD_11/31/2009"
        Range("Q1").Value = "Amortized_Cost_USD"
        Range("R1").Value = "MTM_USD"
        Range("S1").Value = "Unrealized_PL_USD"
        Range("T1").Value = "Initial_Notional_USD"
        Range("U1").Value = "Notional_Exchange"
        Range("V1").Value = "Maturity_Date"
        Range("W1").Value = "Trade_Date"
        Range("X1").Value = "Settlement_Date"
        Range("Y1").Value = "Expected Maturity_Yrs"
        Range("Z1").Value = "Expected_Maturity_Date"
    End With
       
    ActiveWorkbook.Close SaveChanges:=True
    
End Sub

Sub Macro2()
    Const sPath1 As String = "L:\12_31_2009_157_Reports\"
    Const sPath2 As String = "L:\12_31_2009_105_Support_Summaries\"
    Const SFileInp1 As String = "Formulas.xlsm"
    Const SFileInp2 As String = "12_31_2009_Data..xlsm"
    
    Workbooks.Open Filename:=sPath2 & SFileInp1
        Range("A1:AV50000").Copy
               
    Workbooks.Open Filename:=sPath2 & SFileInp2
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=False
                                           
End Sub

Sub Main()

    Call Macro1
    Call Macro2
    
End Sub

[Domski]

Avoiding the use of Select which is practically never needed in VBA and turning off screen updating can make a big difference as well:

Sub Macro1()
    Const sPath1 As String = "L:\12_31_2009_157_Reports\"
    Const sPath2 As String = "L:\12_31_2009_105_Support_Summaries\"
    Const SFileInp1 As String = "105xls_version 5.xls"
    Const SFileInp2 As String = "Formulas.xlsm"
    Const sFileOut1 As String = "12_31_2009_Data.xlsm"
    
    Workbooks.Add
    With ActiveSheet
        .Name = "12_31_2009_105_Data"
        .Parent.SaveAs Filename:=sPath1 & sFileOut1, _
                       FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
                       CreateBackup:=False
        .Parent.Close
    End With

    Workbooks.Open Filename:=sPath1 & SFileInp1
        Range("A4:Z50000").Copy
               
    Workbooks.Open Filename:=sPath2 & SFileInp2
    With ActiveSheet
        Range("A1").Paste
        .Name = "105_12_31_2009_version1"
        Range("C1").Value = "Book_ID"
        Range("E1").Value = "Deal_ID"
        Range("F1").Value = "Instrument_ID"
        Range("G1").Value = "Trade_Type"
        Range("H1").Value = "Security_ID"
        Range("I1").Value = "Trade_ID"
        Range("J1").Value = "PR_Flag"
        Range("K1").Value = "Amortized_Cost_USD_12/31/2009"
        Range("L1").Value = "MTM_USD_12/31/2009"
        Range("M1").Value = "Unrealized_P/L_USD_12/31/2009"
        Range("N1").Value = "Amortized_Cost_USD_11/31/2009"
        Range("O1").Value = "MTM_USD_11/31/2009"
        Range("P1").Value = "Unrealized_P/L_USD_11/31/2009"
        Range("Q1").Value = "Amortized_Cost_USD"
        Range("R1").Value = "MTM_USD"
        Range("S1").Value = "Unrealized_PL_USD"
        Range("T1").Value = "Initial_Notional_USD"
        Range("U1").Value = "Notional_Exchange"
        Range("V1").Value = "Maturity_Date"
        Range("W1").Value = "Trade_Date"
        Range("X1").Value = "Settlement_Date"
        Range("Y1").Value = "Expected Maturity_Yrs"
        Range("Z1").Value = "Expected_Maturity_Date"
    End With
       
    ActiveWorkbook.Close SaveChanges:=True
    
End Sub

Sub Macro2()
    Const sPath1 As String = "L:\12_31_2009_157_Reports\"
    Const sPath2 As String = "L:\12_31_2009_105_Support_Summaries\"
    Const SFileInp1 As String = "Formulas.xlsm"
    Const SFileInp2 As String = "12_31_2009_Data..xlsm"
    
    Workbooks.Open Filename:=sPath2 & SFileInp1
        Range("A1:AV50000").Copy
               
    Workbooks.Open Filename:=sPath2 & SFileInp2
        Range("A1").PasteSpecial Paste:=xlPasteValues, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=False
                                           
End Sub

Sub Main()

    Application.ScreenUpdating = False

    Call Macro1
    Call Macro2
    
    Application.ScreenUpdating = True
    
End Sub

Dom

[AnthonyWB]

I tried, and I ran your macro. An error occcurs stating that that object does not support this property or method. Everyone tells me to get rid of the .Select, and when I do I have issues compiling the code.

    Workbooks.Open Filename:=sPath2 & SFileInp2
    With ActiveSheet
        Range("A1").Paste
        .Name = "105_12_31_2009_version1"

where the "RANGE("A1").Paste is highlighted in yellow. By adding ".Select" it works. Why?

[Domski]

Try:

    Workbooks.Open Filename:=sPath2 & SFileInp2
    With ActiveSheet
        .Range("A1").Paste
        .Name = "105_12_31_2009_version1"

Dom

[AnthonyWB]

Is it just me, or is that the same code?

[Domski]

There's an extra .

[AnthonyWB]

Nope same error as before. There must be something wierd going on.

   Workbooks.Open Filename:=sPath2 & SFileInp2
    With ActiveSheet
        .Range("A1").Paste
        .Name = "105_12_31_2009_version1"

[Domski]

I'm with you on that one. Can you upload a sample workbook. Have been in the pub for the last 4 days practically so I might be missing the obvious...

[shg]

There is no Paste method for a range; it's ActiveSheet.Paste or Range.PasteSpecial

   Workbooks.Open Filename:=sPath2 & SFileInp2
    With ActiveSheet
        .Range("A1").PasteSpecial

[AnthonyWB]

"I would also maybe define your workbooks as objects, makes it easier to deal with when you are working with multiple workbooks."

Can you elaborate on this?

[AnthonyWB]

I am getting an error in submacro2 , perhaps I am not implementing it correctly.

Sub Macro2()
    Const sPath1 As String = "L:\12_31_2009_157_Reports\"
    Const sPath2 As String = "L:\12_31_2009_105_Support_Summaries\"
    Const SFileInp1 As String = "Formulas.xlsm"
    Const SFileInp2 As String = "12_31_2009_Data.xlsm"
    
    Workbooks.Open Filename:=sPath2 & SFileInp1
              
    Workbooks.Open Filename:=sPath1 & SFileInp2
        Sheets ("105_12_31_2009_version1"), Range("A1:AV50000").Value = Sheets("12_31_2009_105_Data").Range("A1:AV50000").Value

[AnthonyWB]

By selecting that the file update only before closing, the performance of the macro is greatly increased.

[shg]

The following snippet works also:

    With ActiveSheet
        .Paste Destination:=Range("A1")

    With ActiveSheet
        .Paste Destination:=.Range("A1")