Macro to Insert Formula

[howard]

I have data columms AA to AG from Sheet BTR ERT-ATREW to the last sheet


I have the folowing formulaa in Col B:D from row 11 onwards

 ColB                                                 Col C                                             Col D
=IF(AA2="","",AA2)                            =IF(AD2="","",AD2)                  =IF(AB2="","",AB2)

When running the formula below., it insert the folowing formula

=IF(#REF!="","",#REF!)

 Sub Formula_Branches()
Dim I As Long, LR As Long
Application.ScreenUpdating = False
For I = Sheets("BTR ERT-ATREW").Index To Worksheets.Count
With Sheets(I)
 LR = .Cells(.Rows.Count, "AA").End(xlUp).Row
     .Range("B11:B" & LR).FormulaR1C1 = "=IF(R[-9]C[25]="""","""",R[-9]C[25])"
    .Range("C11:C" & LR).FormulaR1C1 = "=IF(R[-9]C[27]="""","""",R[-9]C[27])"
    .Range("D11:D" & LR).FormulaR1C1 = "=IF(R[-9]C[24]="""","""",R[-9]C[24])"
End With
Next I
Application.ScreenUpdating = False
End Sub

It would be appreciated if someone could amend my formula

 

[HaHoBe]

Hi howard,

running the given code on a sample workbook brings up this for me:

MrE_1226498_1700A11_macro to insert form_230110.xlsm
	B	C	D
11	2 27	2 30	2 28
12	3 27	3 30	3 28
13	4 27	4 30	4 28
14	5 27	5 30	5 28
15	6 27	6 30	6 28
16	7 27	7 30	7 28
17	8 27	8 30	8 28
18	9 27	9 30	9 28
19	10 27	10 30	10 28
20	11 27	11 30	11 28
21	12 27	12 30	12 28
BTR ERT-ATREW

Cell Formulas
Range	Formula
B11:B21	B11	=IF(AA2="","",AA2)
C11:C21	C11	=IF(AD2="","",AD2)
D11:D21	D11	=IF(AB2="","",AB2)

All checks (Formatting, Locked, Merged) just show the same result for me.

Holger

 

[howard]

Hi Holdger

See linmk below for my file wqhich contains the macro. See Ref errors on sheets after running macro

Formula Cheque Advises.xlsm

Shared with Dropbox

www.dropbox.com

 

[HaHoBe]

Hi howard,

aside from getting information that there is a problem with the contents of the workbook, there are links to external data, and after letting Excel restore the workbook the last filled row on sheet BTR ERT-ATREW in Column AA is 2, for sheet BGH ERT-ATREW there is no data in Column AA and the broken references are located in Row 10 (while your code starts with Row 11).

Since the result will only be shown if there is a value in the corresponding cell try

Sub Formula_Branches()
Dim I As Long
Application.ScreenUpdating = False
For I = Sheets("BTR ERT-ATREW").Index To Worksheets.Count
  With Sheets(I)
    .Range("B11:B23").FormulaR1C1 = "=IF(R[-9]C[25]="""","""",R[-9]C[25])"
    .Range("C11:C23").FormulaR1C1 = "=IF(R[-9]C[27]="""","""",R[-9]C[27])"
    .Range("D11:D23").FormulaR1C1 = "=IF(R[-9]C[24]="""","""",R[-9]C[24])"
  End With
Next I
Application.ScreenUpdating = False
End Sub

Holger

 

[howard]

Hi Holger


Many thanks for your input and help

Code works 100%