HELP with formulas in VBA code - what's the most efficient way?

[jacksongf]

Hey guys, here's a part of my code that contains putting these formulas in 7 different columns for i amount of rows (i = ~210,000). At the end of the code, I copy the range and paste as values to get rid of the formulas, but keep the values.

It works, but the problem is that it REALLY slows down my macro. I'm wondering if anyone could look at this and think of a way to optimize it / make it more efficient?

Any tips would be great!

[COLOR=#333333]Sub Formulas()

[/COLOR]Last = DataSheet.Range("A1048576").End(xlUp).Row

    For i = 1 To Last - 1

        DataSheet.Cells(i + 1, 5).Value = "=INDEX(Table2[Category],MATCH(MID(Data!C" & i + 1 & ",4,3),Table2[Abbreviation],0))"
        DataSheet.Cells(i + 1, 6).Value = "=LEFT(Data!C" & i + 1 & ",3)"
        DataSheet.Cells(i + 1, 7).Value = "=INDEX(Table2[Department],MATCH(MID(Data!C" & i + 1 & ",4,3),Table2[Abbreviation],0))"
        DataSheet.Cells(i + 1, 8).Value = "=MID(Data!C" & i + 1 & ",7,3)"
        DataSheet.Cells(i + 1, 9).Value = "=""20"" & RIGHT(Data!C" & i + 1 & ",2)"
        DataSheet.Cells(i + 1, 10).Value = "=VLOOKUP(Data!B" & i + 1 & ",Locations,3,0)"
        DataSheet.Cells(i + 1, 11).Value = "=VLOOKUP(Data!B" & i + 1 & ",Locations,4,0)"
        
    Next i

 [COLOR=#333333]End Sub[/COLOR]

 

[James006]

Hello,

You could insert as the first line of your macro:
Application.Calculation = xlManual

and insert as the last line of your macro :
Application.Calculation = xlAutomatic

Hope this will help

 

[MARK858]

Applying the formula to the whole range in one go rather than looping will speed it up.

 

[jacksongf]

Thanks guys, I took your advice Mark and changed it to the below code and it is much faster. I also have the calculation manual/automatic triggers applied at the beginning and end of the code (just not included in this small snippet).

HOWEVER, now I've added one additional formula, and it takes an extremely long time. I think it's because it's doing a vlookup on a range of 250,000 rows. Is there a more efficient way to perform this action? (****** beside the new line).

Sub Formulas()


Last = DataSheet.Range("A" & Rows.Count).End(xlUp).Row


    With DataSheet.Range("E2:L" & Last)
    
        .Columns(2).Formula = "=INDEX(Table2[Category],MATCH(MID(C2,4,3),Table2[Abbreviation],0))"
        .Columns(3).Formula = "=LEFT(C2,3)"
        .Columns(4).Formula = "=INDEX(Table2[Department],MATCH(MID(C2,4,3),Table2[Abbreviation],0))"
        .Columns(5).Formula = "=MID(Data!C2,7,3)"
        .Columns(6).Formula = "=""20"" & RIGHT(Data!C2,2)"
        .Columns(7).Formula = "=VLOOKUP(Data!B2,Locations,3,0)"
        .Columns(8).Formula = "=VLOOKUP(Data!B2,Locations,4,0)"
        .Value = .Value
        
        MsgBox "Preliminary Formulas Complete - Moving Onto Per Ton"
        
        .Columns(1).Formula = "=iferror(D2/VLOOKUP(LEFT(C2,3)&IF(H2=""Distribution"",""TDE"",""TIP"")&RIGHT(C2,5),$C$2:$L$250000,2,0),0)"    ************
        .Value = .Value
        
    End With


End Sub

 

[James006]

Among the powerful things in VBA, you could use Evaluate(yourformulas) ...

This would also speed up your process ...

Hope this will help