VBA insert a Row with formula - Unable to Track

Question

Al,

There is a sheet which Macro is written to add a new team along with formula. I would like to change the formula, but i am unable to find it.

The formula is =IFNA(INDEX($F$24:$F$9223,MATCH($A4,$A$24:$A$9223,0)),0)

VBA Code is:

    frmAllTeams.Show
    
    'Adding a team to the dtl overview page
    Sheets("HLE").Activate
    Call unprotect_sheet
    Worksheets("HLE").Range("A1").Activate
    
    'Identifying String name
    strname = frmAllTeams.cbox1.Value
    typenote = frmAllTeams.cbox2.Value
    
    Dim i As Integer, intValueToFind As String
    intValueToFind = frmAllTeams.cbox1.Value
    For i = 1 To 30    ' Revise the 500 to include all of your values
        If Cells(i, 1).Value = intValueToFind Then
            MsgBox ("You cannot add a team twice  " & i)
            Exit Sub
        End If
    Next i

    'Un-Hiding the third row on dtl overview
    ActiveSheet.Rows("3:3").Hidden = False
    
    'loop until you find the row "Project Management" and insert line above
    Do
      ActiveCell.Offset(1, 0).Select
      Loop Until ActiveCell = "Project Management"
    
    Rows(Selection.Row - 1).Copy
    Rows(Selection.Row).Insert Shift:=xlDown
    
    'name the cell in col A the name of the page
    Cells(ActiveCell.Row, 1).Select
    ActiveCell.Value = strname

    'formatting
    Range(ActiveCell, Cells(ActiveCell.Row, ActiveCell.Column + 8)).Select
        With Selection.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
        End With
    
    ActiveCell.Offset(0, 8).Select
        With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThick
        .ColorIndex = xlAutomatic
        End With
        
        With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
        End With
    
        With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
        End With
    
    'go to the team sheet and select col 3-5 on last row and copy
    Sheets("HLETeams").Activate
    Range("F1:P16").Select
    Selection.Copy

    'select the col 2 on team line and paste
    Sheets("HLE").Select
    Range("A1").Select
    Dim lastRow As Long
    lastRow = ActiveSheet.UsedRange.Rows.Count
    'lastRow = lastRow
    'MsgBox ("Last Row" & lastRow)
    ActiveCell.Offset(lastRow, 0).Select
    ActiveCell.PasteSpecial xlPasteAll
            
    Cells.Replace What:="TMxxxx", Replacement:=strname, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    
    Cells.Replace What:="TEAM: TMxxxx", Replacement:="TEAM: " + strname, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    
    Dim c As Range
        For Each c In Range("A23:I1000").Cells
            If c.Value = strname Then
                c.EntireRow.Hidden = True
            End If
    Next c
        
    Worksheets("HLE").Range("A1").Activate
    
    Do
        ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell = "TEAM: " & strname

    ActiveCell.AddComment typenote
    ActiveCell.Offset(2, 2).Select
    
        If typenote = "Mainframe" Then
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:="=mfmod"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        ElseIf typenote = "Distributed" Then
            With Selection.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                 xlBetween, Formula1:="=distmod"
                .IgnoreBlank = True
                .InCellDropdown = True
                .InputTitle = ""
                .ErrorTitle = ""
                .InputMessage = ""
                .ErrorMessage = ""
                .ShowInput = True
                .ShowError = True
            End With
        ElseIf typenote = "Other" Then
            ActiveCell.Value = "Other Item"
        End If
        
    'Hiding the third row on dtl overview
    ActiveSheet.Rows("3:3").Hidden = True
    
    Call protect_sheet
    teamcount = teamcount + 1
End Sub

I verified the formula and checked the dependents, but no clue, i just clicked on the dotted line, i didn't see the above said formula.

Where that formula is constructed in VBA code?

EDIT: Please refer below the default row added in the sheet.

Answer 1

As I mentioned in the comments, the formula already exists in row 3, but the row is hidden by default. The code unhides this row and then hides it again. You can accomplish the same thing manually by highlighting rows 2 through 4, right clicking, and clicking Unhide. You should then be able to manually update this formula and hide the row again.

Alternatively, as Siddharth Rout mentioned in the comments, you can press CTRL + F, type =IFNA(INDEX in the Find Box, set the "Within" to "Workbook" and "Look In" to "Formulas", and simply find it. It will directly take you to the cell which has that formula. Then you can edit the formula without unhiding the row.

If you are able to edit the code, I would suggest making some changes to avoid using Select, as discussed here.