Formula to always be in row where specfic account is

[howard]

I have a formula in E4 in Sheet Summary in same row where account KX-1081 appears in COL A which is generated by VBA

I would like to text and formula to always be in the same row where KX-1081 appears in Col A on workbook Check Balance KX-1081.xlsm

 Sub Formula()
With Sheets("Sheet1")
    .Range("E4").FormulaR1C1 = "Opening Bal "
    .Range("F4").FormulaR1C1 = "=VLOOKUP(RC1&"""",'[TB 2019.xlsx]Sheet1'!C1:C6,COLUMNS(C1:C),FALSE)"
    .Range("G4").FormulaR1C1 = "Closing Bal"
    .Range("H4").FormulaR1C1 = "=VLOOKUP(RC1&"""",'[TB 2019.xlsx]Sheet1'!C1:C6,COLUMNS(C1:C[-4]),FALSE)"
    .Range("I4").FormulaR1C1 = "=+RC[-3]-RC[-1]"
    .Range("J4").FormulaR1C1 = "=RC[-6]+RC[-1]"
    .Range("K4").FormulaR1C1 = "=IF(RC[-1]<0.00005,""In balance"",""Not in Balance-Please re-download reports and re-import"")"
    End With
    
End Sub

It would be appreciated if someone could kindly assist me


I have also posted on
https://www.excelforum.com/excel-pr...lways-be-in-row-where-specfic-account-is.html

 

[Fluff]

How about like

Sub Howard()
    Dim Fnd As Range
    With Sheets("Sheet1")
        Set Fnd = .Range("A:A").Find("KX-1081", , , xlWhole, , , False, , False)
        Fnd.Offset(, 4).Value = "Opening Bal "
        Fnd.Offset(, 5).FormulaR1C1 = "=VLOOKUP(RC1&"""",'[TB 2019.xlsx]Sheet1'!C1:C6,COLUMNS(C1:C),FALSE)"
    End With
End Sub

 

[howard]

Thanks Fluff. This is perfect

 

[Fluff]

You're welcome & thanks for the feedback