breynolds0431
Active Member
- Joined
- Feb 15, 2013
- Messages
- 303
- Office Version
- 365
- 2016
- Platform
- Windows
Hi. I'm trying to fill a specified formula from row B2 till there's no adjacent data in column C. The below is what I have and it works, but it takes some time, which I know is mostly because it's reaching out to another workbook. But, any ideas to speed it up would be greatly appreciated.
VBA Code:
Sub FillFormula ()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
With Windows("Open_Inventory.xlsb")
Sheets("Combined").Range("B2").Select
Set wsh = Worksheets("Combined")
i = 2
While wsh.Cells(i, 3) <> ""
wsh.Cells(i, 2).FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC[4]&RC[6]&RC[9],[Prev_Open_Inventory.xlsb]Combined!C1:C2,2,FALSE),"""")"
i = i + 1
Wend
Range("B:B").copy
Range("B1").PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = False
Windows("Prev_Open_Inventory.xlsb").Activate
ActiveWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub