Can I use arrays to do write formulas to cells?

[JeffGrant]

Hi All,

I have many examples where I have to do this:

Sub TimerCleartblCondBal()
    Sheet52.Select
    With Sheet52
    irow = .Range("A" & Rows.Count).End(xlUp).Row
   
    'Paste formulas for new data
    .Range("FY2:FY" & irow).Formula2 = "=N2"
    .Range("FZ2:FZ" & irow).Formula2 = "=BQ2"
    .Range("GA2:GA" & irow).Formula2 = "=CF2"
    End With

End Sub

Because I am referencing back to a range to write these formulas, can these formulas be written to an array and then the array written back to the sheet?
Also, I have several situations where the With statement will only work if the Select statement is used first. I don't understand why because I thought the idea of the With statement is that we don't have to use a Select or Activate statement?
And one more . I have found several scenarios where the Select & With statements are present in the VBA Code, however, the code throws up a runtime error. If I click in the sheet and continue with the macro, it works fine.

Any ideas?

All guidance is very much appreciated.

 

[RoryA]

1. Yes
2. That means you forgot to qualify something inside the With block (e.g. your Rows property above)
3. What error?

 

[JeffGrant]

Hi Rory, usually the error is a 1004.

Can you please give me a bit more detail regarding how the .rows is not qualified?

Thanks

 

[JeffGrant]

Hi Rory, once again you have spurred me on.
The formula to find the last row is a formula that I picked up a long time ago. I see that there are many ways to find the last row, but i never looked for another way. But now that my model is so complicated, i guess that inefficient / bad code is now coming back for me to fix up.

Since all of my data is in tables, i have found this formula:

LastRow = ActiveSheet.ListObjects("Table1").Range.Rows.Count

So I will update all subs to use this formula inside the With Statement and see if the error goes away.


Cheers

 

[Peter_SSs]

can these formulas be written to an array and then the array written back to the sheet?

Why?
What are you trying to achieve?

 

[RoryA]

Can you please give me a bit more detail regarding how the .rows is not qualified?

It's not .Rows it's just Rows and that is the problem.

 

[JeffGrant]

Hi Peter, I am looking for better performance. The example used above is very very small. I have many formulas that are literally copied to thousands of rows. Especially at a weekend. Currently, from start to data prep ready for usage can take 20~30 minutes to run.

Most of this model relies on directly referencing sheets and pasting formulas . I am just thinking that using arrays could be substantially faster.

 

[Peter_SSs]

Without knowing details of the sheets/actual formulas, I'm not sure that using arrays to enter the formulas is going to be much faster. I suspect that the time delay may be more related to the time taken to actually calculate the large number of formulas rather than the time to write the formulas into the worksheet.