how to paste an array of formulas into worksheet via VB

[luv2glyd]

Right now I have code that pastes a few dozen formulas one by one into a worksheet. Some of the formulas are in cells that are adjacent to one another.

So for examle, cell A1's formula that VB pastes in is =B24*3+1, cell B1's formula is =B24*5+6, cell C1's formula is =B14*5+12, and so on.... The precess of pasting one by one is a bit time consuming. Is there a way to define 10 formulas or so into an array of sorts in VB and paste it in in one step in all 10 horizontally adjacent cells vs. pasting 10 formulas one buy one in 10 steps?

Thank you.

[JBeaucaire]

If the formulas are distinctive, listing them out in separate lines of code makes maintenance of your macro and future editing simple. I would recommend leaving the individual formulas coded as you have.

This is based on your description, if you had posted your actual code we would be able to tell for certain if this is best.

Be sure to turn off ScreenUpdating at the top of your macro, and turn it back on at the end, this will speed up the execution of all your macros significantly.

Sub MyMacro()
Application.ScreenUpdating = False

'your code

Application.ScreenUpdating = True
End Sub

[luv2glyd]

Great! That speeds things up a lot! Although I'd still like to know if and how the array pasting can be done for future refence. Here's the code I have:

    Range("F33").Formula = "=INDEX($daily_signs_array,1)"
    Range("G33").Formula = "=INDEX($daily_signs_array,2)"
    Range("H33").Formula = "=INDEX($daily_signs_array,3)"
    Range("I33").Formula = "=INDEX($daily_signs_array,4)"
    Range("J33").Formula = "=INDEX($daily_signs_array,5)"
    Range("K33").Formula = "=INDEX($daily_signs_array,6)"
    Range("L33").Formula = "=INDEX($daily_signs_array,7)"
    Range("M33").Formula = "=INDEX($daily_signs_array,8)"
    Range("N33").Formula = "=INDEX($daily_signs_array,9)"
    Range("O33").Formula = "=INDEX($daily_signs_array,10)"
    Range("P33").Formula = "=INDEX($daily_signs_array,11)"
    Range("Q33").Formula = "=INDEX($daily_signs_array,12)"
    Range("R33").Formula = "=INDEX($daily_signs_array,13)"
    Range("S33").Formula = "=INDEX($daily_signs_array,14)"

[JBeaucaire]

I get the same result (after taking out the $ sign, not sure how you're able to use that and I'm not) with this one line of code:

Sub EnterArray()
    Range("F33:S33").Formula = "=INDEX(daily_signs_array,COLUMN(A1))"
End Sub

[johnjohns]

Along with suppressing screen updating, hope this also would speed up the process

Application.Calculation = xlCalculationManual

'your codes

Application.Calculation = xlCalculationAutomatic

rgds

johnjohns

[snb]

or

[A1:C1]=split("=B24*3+1|=B24*5+6|=B14*5+12","|")
[A1:C1] = [A1:C1].Formula

[luv2glyd]

Perfect! That simplifies my code a lot and gives me some versatility in the future. Application.Calculation also helps speed things up. Thank you everyone.