Runtime 1004 Range of Object worksheet failed when adding numbers to Lrow

Question

I'm trying to write a formula into a column by using Lrow. I found the formula can run without problem when it only with Lrow but I have run time error 1004 range of object worksheet failed when having Lrow - 6. The calculation is done in Column B while column A has the data I want to take the average. Col A has data from A2 to A & Lrow . Can someone point out why this yields an error message and how to fix it? Many thanks in advance! Here is the code I have:

Sub Calculation()
Dim wb As Workbook: Set wb = Workbooks("A.xlsx")
Dim sh As Worksheet
Dim Lrow As Long

For Each sh In wb.Worksheets
Lrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row

sh.Range("B8:B" & Lrow -6).Formula ="=(average(A2:A13)"&"+average(A3:A14))/2"  'Here in range Lrow - 6 give me this error

Next sh

End sub

Answer 1

Try this:

Sub Calculation()
  Dim wb As Workbook: Set wb = Workbooks("A.xlsx")
  Dim sh As Worksheet
  Dim Lrow As Long

  For Each sh In wb.Worksheets
    Lrow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    
    If Lrow > 6 then
      sh.Range("B8:B" & Lrow -6).Formula ="=average(A2:A13)"&"+average(A3:A14))/2"
    Else
      Msgbox("There were less than 6 rows", vbExclamation)
    End if

  Next sh

End sub

If you want it to run unaccompanied and just skip over the sheets that do not have enough rows, just omit these two lines from the above code:

    Else
      Msgbox("There were less than 6 rows", vbExclamation)