Insert a formula in a range of cells using VBA

[sn152]

Hi All,

I am trying to insert a formula in a dynamic range of cells using VBA.

Formula - =IF(D2="-",E2,IF(E2="-",D2,CONCATENATE(D2,"-",E2)))

I want to insert this formula in column P. That is from Range P2 till the last row in which data is available in column A.

I tried recording the macro (below is the code). But it is not working. Tried few other methods, but no luck.

Sub Macro3()
'
' Macro3 Macro
'
    Range("P2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-12]=""-"",RC[-11],IF(RC[-11]=""-"",RC[-12],CONCATENATE(RC[-12],""-"",RC[-11])))"
    Range("P2").Select
    Selection.Copy
    Range("O2").Select
    Selection.End(xlDown).Select
    Selection.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Selection.End(xlUp).Select
    Application.CutCopyMode = False
End Sub

Please help me here. Thanks!

 

[Luke M]

Give this a shot.

Sub ExampleCode()

    Dim lastRow As Long
   
    'Where is last row of data?
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
   
    'Apply formula
    Range("P2:P" & lastRow).Formula = "=IF(D2=""-"",E2,IF(E2=""-"",D2,CONCATENATE(D2,""-"",E2)))"

End Sub

 

[sn152]

Great. It works. You are superb.. Thanks a lot and Merry Christmas.

 

[inddon]

Give this a shot.

Sub ExampleCode()

    Dim lastRow As Long
  
    'Where is last row of data?
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
    'Apply formula
    Range("P2:P" & lastRow).Formula = "=IF(D2=""-"",E2,IF(E2=""-"",D2,CONCATENATE(D2,""-"",E2)))"

End Sub

Hi Luke,

If I want an array formula (below) to be applied to a Table Column, will this work applying it through VBA?

Table Name : Table1
Table Column Name : ListingCD
ListingCD Column Formula :

{=IFERROR(INDEX(Table1[ContDate],MATCH([@ListingCB],Table1[ContBill],0)),"")}

How would this be done?

Look forward to hearing from you.

Thanks & regards,
Don

 

[Luke M]

Hi Don,

Why would this be an array formula? Looks like it's a regular MATCH/INDEX formula?

 

[inddon]

Hi Luke,

Yes, you are right. I removed the curly bracets and entered all the formulas with ctrl+enter, except for the first column (code below) which has the curly bracets:

1.
{=IFERROR(INDEX(Table1[ListingContBill],SMALL(IF(Table1[ListingContBill]<>"",ROW(Table1[ListingContBill])-ROW(Table1[[#Headers],[ContBill]])),ROWS($1:1))),"")}

' works good now
2. 
=IFERROR(INDEX(Table1[ContDate],MATCH([@ListingCB],Table1[ContBill],0)),"")

Could you please let me know if the above is an array formula? When I press ctrl+enter it does not work, and when I press shift+ctrl+enter it works.


In either case just would like to know out of curiosity how would one assign formulas via VBA to Table Columns both array (shift+ctrl+enter & ctrl+enter) and normal ones.


Thanks & regards,
Don

 

[Luke M]

Since the FormulaArray has to use R1C1 reference, and this formula would get mis-interpreted if we applied array to range, rather than array in a single cell copied to range, I'd use the Table's autocomplete ability like so

Sub ExampleCode()
    Dim strForm1 As String
    Dim strForm2 As String
    Dim boolComplete As Boolean
    Dim tb As ListObject
    Dim lc As ListColumn
   
    'What formulas will we try to do?
    strForm1 = "=IFERROR(INDEX(Table1[ContDate],MATCH([@ListingCB],Table1[ContBill],0)),"""")"
   
    'NOTE: Array formulas must use R1C1 references
    strForm2 = "=IFERROR(INDEX([ListingContBill],SMALL(IF([ListingContBill]<>"""",ROW([ListingContBill])-ROW(Table1[[#Headers],[ContBill]])),ROWS(R1:R[-1]))),"""")"
   
    Set tb = ActiveSheet.ListObjects("Table1")
    Set lc = tb.ListColumns("ListingCD")
   
    'Make sure autocomplete is on
    boolComplete = Application.AutoCorrect.AutoFillFormulasInLists
    Application.AutoCorrect.AutoFillFormulasInLists = True
   
    'Apply formula to whole range
    tb.ListColumns("ListingCD").DataBodyRange.Cells(1).Formula = strForm1
    MsgBox "First formula done"
    tb.ListColumns("ListingCD").DataBodyRange.Cells(1).FormulaArray = strForm2
    MsgBox "Second formula done"
   
    'Reset autocomplete to original state
    Application.AutoCorrect.AutoFillFormulasInLists = boolComplete
End Sub