CODEWITHSUNDEEP
excelvba
Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Vba -excel .Formula method is slow

What's wrong with the below code? I am trying to vlookup with closed data from another workbook. If I run this code it takes a long time to execute. Finally it hangs ... but it produces the correct result eventually:

Sub testing()
  Range("G1").EntireColumn.Insert
  With Range("G2")
    .Formula = "=VLOOKUP(F2,'C:\Users\sathisk\Desktop\Macros\ReferencePath\[HUL_Assets_18th April 2012.xls]HUL_Assets_18th April 2012.xls'!A$1:J$65536,10,0)"
    .Copy
    .Offset(0, -1).Select
  End With

  Selection.End(xlDown).Offset(0, 1).Select

  With ActiveCell
    .PasteSpecial xlPasteFormulas
    .Copy
    .Select
  End With

  Range(ActiveCell, Selection.End(xlUp)).PasteSpecial xlPasteFormulas

End Sub

Upvotes: 2

Views: 3216

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149287

Is this what you are trying to do?

Option Explicit

Sub testing()
    Dim LastRow As Long

    Application.Calculation = xlCalculationManual

    Range("G1").EntireColumn.Insert

    LastRow = Range("F" & Rows.Count).End(xlUp).Row

    With Range("G2:G" & LastRow)
        .Formula = "=VLOOKUP(F2,'C:\Users\sathisk\Desktop\Macros\ReferencePath\[HUL_Assets_18th April 2012.xls]HUL_Assets_18th April 2012.xls'!A$1:J$65536,10,0)"
    End With

    Application.Calculation = xlCalculationAutomatic
End Sub

EDIT

I would also recommend you to change J$65536 to the actual last row in that sheet. For example if in that sheet the data is till row 1000 then change it to J$1000

Upvotes: 2

D.Flowers
D.Flowers

Reputation: 11

in addition to Siddharth recommendation I would also add

Application.ScreenUpdating = False 'Turn off screen updating to speed up process. at the top of the macro along with Application.Calculation = xlCalculationManual

then add Application.ScreenUpdating = True 'Turn on screen updating. at the bottom along with Application.Calculation = xlCalculationAutomatic

I use both methods to free up the CPU to just running the macro's, no screen updates or calculating until macro is finished.

Upvotes: 1

Related Questions