Macro to insert column with a formula

[fintansmith]

Hi,

Im not very competent on VBA but really need a macro to insert a column at GB with the below formula -

=IF((IF(AJ4="CLOSED",BA4,IF(BE4<BP4,0,IF((AY4+AV4)>BQ4,BA4,BA4-(BQ4-(AY4+AV4)))))-BR4)>=0,(IF(AJ4="CLOSED",BA4,IF(BE4<BP4,0,IF((AY4+AV4)>BQ4,BA4,BA4-(BQ4-(AY4+AV4)))))-BR4),IF(AJ4="CLOSED",0,IF((AY4+AV4)>BR4,BA4+(0-BR4),0)))

Could anyone please give me a hand? Really struggling.

P.s the formula is above is row 4 but on row 5 all cells should be 5 etc.

Many thanks

 

[Saurabhj]

Hi,

Please check below code:

Sub insertFormula()
    Dim lastRow As Integer
    Sheets("Sheet1").Range("GB1").Formula = "=IF((IF(AJ1='CLOSED',BA1,IF(BE1<BP1,0,IF((AY1+AV1)>BQ1,BA1,BA1-(BQ1-(AY1+AV1)))))-BR1)>=0,(IF(AJ1='CLOSED',BA1,IF(BE1<BP1,0,IF((AY1+AV1)>BQ1,BA1,BA1-(BQ1-(AY1+AV1)))))-BR1),IF(AJ1='CLOSED',0,IF((AY1+AV1)>BR1,BA1+(0-BR1),0)))"
    Range("GB1").Select
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "AJ").End(xlUp).Row
    Selection.AutoFill Destination:=Range("GB1:GB" & lastRow)
End Sub

 

[fintansmith]

Hi,

Please check below code:

Sub insertFormula()
    Dim lastRow As Integer
    Sheets("Sheet1").Range("GB1").Formula = "=IF((IF(AJ1='CLOSED',BA1,IF(BE1<BP1,0,IF((AY1+AV1)>BQ1,BA1,BA1-(BQ1-(AY1+AV1)))))-BR1)>=0,(IF(AJ1='CLOSED',BA1,IF(BE1<BP1,0,IF((AY1+AV1)>BQ1,BA1,BA1-(BQ1-(AY1+AV1)))))-BR1),IF(AJ1='CLOSED',0,IF((AY1+AV1)>BR1,BA1+(0-BR1),0)))"
    Range("GB1").Select
    lastRow = Sheets("Sheet1").Cells(Rows.Count, "AJ").End(xlUp).Row
    Selection.AutoFill Destination:=Range("GB1:GB" & lastRow)
End Sub

Hi,
Apologies, the column i need to insert is at BG not GB as initially stated. Sheet1 is also called "Lloyds Bdx".

I have copied your code and replaced all 'GB' with BG and Sheet1 with "Lloyds Bdx" but am still getting Rune-time error below and cant figure it out

Thanks so much for your help.

 

[fintansmith]

I managed to get it thanks to your help @Saurabhj. The problem was the 'closed' in the code. When changed to ""closed"" it worked perfectly. Code I ended up using was;

Sub insertFormula()
    Dim lastRow As Integer
    Sheets("Lloyds Bdx").Range("BG1").EntireColumn.Insert
    Sheets("Lloyds Bdx").Range("BG4").Formula = "=IF((IF(AJ4=""CLOSED"",BA4,IF(BE4<BP4,0,IF((AY4+AV4)>BQ4,BA4,BA4-(BQ4-(AY4+AV4)))))-BR4)>=0,(IF(AJ4=""CLOSED"",BA4,IF(BE4<BP4,0,IF((AY4+AV4)>BQ4,BA4,BA4-(BQ4-(AY4+AV4)))))-BR4),IF(AJ4=""CLOSED"",0,IF((AY4+AV4)>BR4,BA4+(0-BR4),0)))"
    Range("BG4").Select
    lastRow = Sheets("Lloyds Bdx").Cells(Rows.Count, "AJ").End(xlUp).Row
    Selection.AutoFill Destination:=Range("BG4:BG" & lastRow)
End Sub

Thanks a million for your help!

 

[Fluff]

You can also get rid of the Select & Selection (which can slow down the code) like

Sub insertFormula()
    Dim lastRow As Integer
    Sheets("Lloyds Bdx").Range("BG1").EntireColumn.Insert
    Sheets("Lloyds Bdx").Range("BG4:BG" & lastRow).Formula = "=IF((IF(AJ4=""CLOSED"",BA4,IF(BE4<BP4,0,IF((AY4+AV4)>BQ4,BA4,BA4-(BQ4-(AY4+AV4)))))-BR4)>=0,(IF(AJ4=""CLOSED"",BA4,IF(BE4<BP4,0,IF((AY4+AV4)>BQ4,BA4,BA4-(BQ4-(AY4+AV4)))))-BR4),IF(AJ4=""CLOSED"",0,IF((AY4+AV4)>BR4,BA4+(0-BR4),0)))"
End Sub