Hi,
Can someone help me with how to insert the MS-Excel formula in cells using VBA?
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:
And re-update the list every time I open the same file, with this code:
Purpose:
To refresh the list and make sure the broken named range references will be eliminated.
Thank you in advance.
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 FunctionAnd 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.
