Excel VBA | How to Insert Excel Formula in Cells Using VBA?

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
Hi,

Can someone help me with how to insert the MS-Excel formula in cells using VBA?

Rich (BB code):
    Dim Lst As Object
    Dim Cl As Range
    Application.ScreenUpdating = False
    
    Set Lst = CreateObject("system.collections.arraylist")
    With Sheets("2. TJ2A (VBA Fill)")
        For Each Cl In .Range("T19", .Range("T" & Rows.Count).End(xlUp))
            If Left(Cl.Value, 1) <> "(" And Cl.Value <> "" Then
            If Not Lst.Contains(Cl.Value) Then Lst.Add Cl.Value
                MsgBox Cl.Value
            End If
        Next Cl
    End With
    Sheets("4. Traded Stocks").Range("T9:AD5000").Value = ""
    Lst.Sort
    Sheets("4. Traded Stocks").Range("U9").Resize(Lst.Count).Value = Application.Transpose(Lst.ToArray)
    
    '------------------[Insert Formulas]-----------------------
    Dim vCtr, vLineNum, vRow, vCol
    vLineNum = 1
    Range("U9").Select
    vRow = ActiveCell.Row: vCol = ActiveCell.Column

    For vCtr = 1 To Lst.Count
        Cells(vRow, 20).Value = vLineNum
        Cells(vRow, vCol + 1).Value = "=SUMIFS(TJ202AmtGain,TJ202StockCode,RC[-1])"
        Cells(vRow, vCol + 2).Value = "=SUMIFS(TJ202AmtLoss,TJ202StockCode,RC[-2])"
        Cells(vRow, vCol + 4).Value = "=IF(RC[-4]="""","""",+RC[-3]+RC[-2])"
        
        '*** [ HERE's THE PROBLEM, to insert the Excel Function / formulas and let's say we don't know how many rows are there ... ] ***

        'Run-time error '1004': Application-defined or object-defined error
        Cells(vRow, vCol + 6).Value = "=IFERROR(INDEX($U$9:$U$11,AGGREGATE(15,6,(ROW($V$9:$V$11)-ROW($V$9)+1)/($V$9:$V$11=AB9),COUNTIFS($AB$9:$AB9,AB9))),"")"
        Cells(vRow, vCol + 7).Value = "=IFERROR(AGGREGATE(14,6,$V$9:$V$11/($V$9:$V$11>0),ROWS(AB$9:AB9)),"")"
        Cells(vRow, vCol + 8).Value = "=IFERROR(INDEX($U$9:$U$11,AGGREGATE(15,6,(ROW($U$9:$U$11)-ROW($W$9)+1)/($W$9:$W$11=AD9),COUNTIFS(AD$9:AD9,AD9))),"")"
        Cells(vRow, vCol + 9).Value = "=IFERROR(AGGREGATE(14,6,$W$9:$W$11/($W$9:$W$11<0),ROWS(AD9:AD$9)),"")"
        '*** [ till here (I think Excel VBA instead of Excel function will be better) ... ]-------------------------------------------------------***
        
        vLineNum = vLineNum + 1: vRow = vRow + 1
    Next vCtr

FNI


Why do I need to code formula insertion?

It's because, note that I am deleting all Named Range list every time I close my Excel file, with this code:

Rich (BB code):
Function ClearWrkBooks()
    For Each pubWrkSheet In ThisWorkbook.Worksheets
        If pubWrkSheet.Name = "Dashboard" Or pubWrkSheet.Name = "Home" Then
            Application.DisplayFullScreen = True
            Application.DisplayFormulaBar = False
            ActiveWindow.DisplayHeadings = False
            ActiveWindow.DisplayGridlines = False

           'User have to login first to change the settings to False / True and view the worksheets

        Else
            pubWrkSheet.Visible = xlSheetVeryHidden
        End If
    Next

    '----------Delete all ranged names----------
    Dim rName As Name
    For Each rName In Application.ActiveWorkbook.Names
        rName.Delete
    Next
End Function

And re-update the list every time I open the same file, with this code:

Rich (BB code):
Private Sub Worksheet_Activate()
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    
    '[Assign Named Range]---------------------------------------
    Dim rngJ2Div As Range
    Set rngJ2Div = Range("BU19:BU5000")
    ThisWorkbook.Names.Add Name:="TJ202Dividend", RefersTo:=rngJ2Div
    
    Dim rngJ2MFund As Range
    Set rngJ2MFund = Range("CA19:CA5000")
    ThisWorkbook.Names.Add Name:="TJ202MFund", RefersTo:=rngJ2MFund

    Dim rngJ2TransDate As Range
    Set rngJ2TransDate = Range("K19:K5000")
    ThisWorkbook.Names.Add Name:="TJ202TransDate", RefersTo:=rngJ2TransDate

    '(And so on... it's a long list.)

    '[/Assign Named Range]--------------------------------------

Purpose:
To refresh the list and make sure the broken named range references will be eliminated.

Thank you in advance.
 

Attachments

  • Problem in Excel - JNL 2.png
    Problem in Excel - JNL 2.png
    92 KB · Views: 0

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You should really use the Formula property if you're adding a formula. You also need to double up any quotation marks that are part of the formula string itself - for example:

Rich (BB code):
Cells(vRow, vCol + 6).Value = "=IFERROR(INDEX($U$9:$U$11,AGGREGATE(15,6,(ROW($V$9:$V$11)-ROW($V$9)+1)/($V$9:$V$11=AB9),COUNTIFS($AB$9:$AB9,AB9))),"""")"
 
Solution

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
You should really use the Formula property if you're adding a formula. You also need to double up any quotation marks that are part of the formula string itself - for example:

Rich (BB code):
Cells(vRow, vCol + 6).Value = "=IFERROR(INDEX($U$9:$U$11,AGGREGATE(15,6,(ROW($V$9:$V$11)-ROW($V$9)+1)/($V$9:$V$11=AB9),COUNTIFS($AB$9:$AB9,AB9))),"""")"
Dear,

You got it. I had headache solving this problem and I feel bad seeing that the only problem is the missing double-quotes.

I will thick the "Mark as solution" as you have answered my concern.

But I have another problem, hope you will help me fix it.

My output is incorrect. Please see the uploaded image.

Let's say we do not know how many rows are there so I think we have to include counting of non-blank cells and I don't know how to incorporate it in the formula ...

Thanks a lot.
 

Attachments

  • Problem with Excel - JNL 3.png
    Problem with Excel - JNL 3.png
    108.6 KB · Views: 3

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
As that's a different question, please start a new thread with it.
 

SoniboiTM

New Member
Joined
Jul 25, 2020
Messages
38
Office Version
  1. 2019
Platform
  1. Windows
  2. Web
As that's a different question, please start a new thread with it.
It's working perfectly now.
Thanks.

I just added this code using Record Macro and modify:

VBA Code:
    Dim Lst As Object
    Dim Cl As Range
    Application.ScreenUpdating = False
    
    Set Lst = CreateObject("system.collections.arraylist")
    With Sheets("2. TJ2A (VBA Fill)")
        For Each Cl In .Range("T19", .Range("T" & Rows.Count).End(xlUp))
            If Left(Cl.Value, 1) <> "(" And Cl.Value <> "" Then
            If Not Lst.Contains(Cl.Value) Then Lst.Add Cl.Value
            End If
        Next Cl
    End With
    Sheets("4. Traded Stocks").Range("T9:AD5000").Value = ""
    Lst.Sort
    Sheets("4. Traded Stocks").Range("U9").Resize(Lst.Count).Value = Application.Transpose(Lst.ToArray)
    '----------------------------------------------------------------------------------------------------------------
        
    'Find the last row using Find function
    'Dim myLastRow As Long
    'myLastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    
    '[Insert Formulas]-----------------------
    Dim vCtr, vLineNum, vRow, vCol
    vLineNum = 1
    Range("U9").Select
    vRow = ActiveCell.Row: vCol = ActiveCell.Column     '9 and 21
    
    For vCtr = 1 To Lst.Count
        Cells(vRow, 20).Value = vLineNum
        Cells(vRow, vCol + 1).Value = "=SUMIFS(TJ202AmtGain,TJ202StockCode,RC[-1])"
        Cells(vRow, vCol + 2).Value = "=SUMIFS(TJ202AmtLoss,TJ202StockCode,RC[-2])"
        Cells(vRow, vCol + 4).Value = "=IF(RC[-4]="""","""",+RC[-3]+RC[-2])"
        vLineNum = vLineNum + 1: vRow = vRow + 1
    Next vCtr
    
    Dim myLastRowTJ204 As Long
    myLastRowTJ204 = Range("U" & Rows.Count).End(xlUp).Row
    
    Range("U9").Select
    vRow = ActiveCell.Row: vCol = ActiveCell.Column     '9 and 21
    Cells(vRow, vCol + 6).Value = "=IFERROR(INDEX($U$9:$U$11,AGGREGATE(15,6,(ROW($U$9:$U$11)-ROW($V$9)+1)/($V$9:$V$11=AB9),COUNTIFS($AB$9:$AB9,AB9))),"""")"
    Cells(vRow, vCol + 7).Value = "=IFERROR(AGGREGATE(14,6,$V$9:$V$11/($V$9:$V$11>0),ROWS(AB$9:AB9)),"""")"
    Cells(vRow, vCol + 8).Value = "=IFERROR(INDEX($U$9:$U$11,AGGREGATE(15,6,(ROW($U$9:$U$11)-ROW($W$9)+1)/($W$9:$W$11=AD9),COUNTIFS(AD$9:AD9,AD9))),"""")"
    Cells(vRow, vCol + 9).Value = "=IFERROR(AGGREGATE(14,6,$W$9:$W$11/($W$9:$W$11<0),ROWS(AD9:AD$9)),"""")"
    
    Range("AA9:AD9").Select
    Selection.AutoFill Destination:=Range("AA9:AD" & myLastRowTJ204), Type:=xlFillDefault
    Range("T" & Rows.Count).End(xlUp).Select
    '[/Insert Formulas]-----------------------
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top