Thread: Cannot get specific array formula to work with VBA? (LONG CODE)

I have been constructing a macro with the help of this forum for a month now and I am down to trouble shooting a few details that are eluding me. Specifically, in this case my macro constructs a set of equations based on the users input.

Only the fifth equation (which is an array equation) that won't seem to work. To start here is the code: (I know its kinda long, see line 157 where the array formula is)

Sub CreateNewStrain()

Dim StrainName As String
Dim GenoTypes As Variant
Dim wSheet As Worksheet
Dim GrThan As String
Dim LsThan As String
Dim StartDate As Date
Dim EndDate As Date
Dim DOB As String
Dim DOW As String
Dim DOS As String
Dim DOE As String
Dim Saced As String
Dim Cage As String

   
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Prompts the user to provide the name of the new strain and sets it to "StrainName"
StrainName = Application.InputBox("What is the name of the strain you wish to add?", _
                                      "Add Strain", Type:=2)
                                      
If StrainName = "False" Then Exit Sub
           
On Error Resume Next
Set wSheet = Worksheets(BreederName)
If wSheet Is Nothing Then

'Sets the Dates
StartDate = Worksheets("Summary").Range("StartDate")
EndDate = Worksheets("Summary").Range("EndDate")
    
'Constructs and Defines the ranges used in later equations
    DOB = "Table" & StrainName & "[DOB]"
    DOW = "Table" & StrainName & "[DOW]"
    DOS = "Table" & StrainName & "[DOS]"
    DOE = "Table" & StrainName & "[DOE]"
    Saced = "Table" & StrainName & "[Saced]"
    Cage = "Table" & StrainName & "[Cage '#]"
    
'Constructs the Date range formulas
GrThan = "(" & """" & ">=" & """" & "&(DATEVALUE((TEXT(StartDate," & """" & "mm/dd/yyyy" & """" & ")))))"
LsThan = "(" & """" & "<=" & """" & "&(DATEVALUE((TEXT(EndDate," & """" & "mm/dd/yyyy" & """" & "))))))"
    
        'Prompts the use to provide the number of genotypes in their new strain
        GenoTypes = Application.InputBox("How any Genotypes does this strain have?  Please type either 1, 2, or 3.", _
                                          "Genotypes", Type:=2)
        If GenoTypes = "False" Then Exit Sub
    
        'Picks which template sheet to copy based on the users input for "GenoTypes"
            If GenoTypes = "1" Then
                Sheets("Template1").Visible = True
                Worksheets("Template1").Copy After:=Worksheets("Template3")
                ActiveSheet.Name = StrainName
                Worksheets(StrainName).ListObjects(1).Name = "Table" & StrainName
                Sheets("Template1").Visible = False
            ElseIf GenoTypes = "2" Then
                Sheets("Template2").Visible = True
                Worksheets("Template2").Copy After:=Worksheets("Template3")
                ActiveSheet.Name = StrainName
                Worksheets(StrainName).ListObjects(1).Name = "Table" & StrainName
                Sheets("Template2").Visible = False
            Else
                Sheets("Template3").Visible = True
                Worksheets("Template3").Copy After:=Worksheets("Template3")
                ActiveSheet.Name = StrainName
                Worksheets(StrainName).ListObjects(1).Name = "Table" & StrainName
                Sheets("Template3").Visible = False
            End If
        
        'Adds the NewStrain to the DATA Table
        Worksheets("DATA").Visible = True
        Worksheets("DATA").Select
             Range("FormulaTable").End(xlDown).Select
             ROWS(ActiveCell.Row).Copy
             ROWS(ActiveCell.Row).Offset(1).Select
             ROWS(ActiveCell.Row).Insert
             ActiveCell.Value = StrainName
             ActiveWorkbook.Names.Add Name:=StrainName & "Data", RefersToR1C1:=ActiveCell
             Worksheets("DATA").Visible = False
         
        'Adds the strain into the "Summary" sheet table including the formulas
        Worksheets("Summary").Select
             Range("SummaryStrain").End(xlDown).Select
             ROWS(ActiveCell.Row).Copy
             ROWS(ActiveCell.Row).Offset(1).Select
             ROWS(ActiveCell.Row).Insert
             ActiveCell.Value = StrainName
             ActiveWorkbook.Names.Add Name:=StrainName, RefersToR1C1:=ActiveCell
             'Constructs the finished genotypes formula
             ActiveCell.Offset(0, 1).Select
                  If GenoTypes = "1" Then
                     ActiveCell.Formula = "=COUNTA(Table" & StrainName & "[Genotype '#1])"
                  ElseIf GenoTypes = "2" Then
                     ActiveCell.Formula = "=COUNTIFS(Table" & StrainName & "[Genotype '#1]," _
                                           & """" & "*" & """" & "," & "Table" & StrainName & _
                                           "[Genotype '#2]," & """" & "*" & """" & ")"
                  Else
                     ActiveCell.Formula = "=COUNTIFS(Table" & StrainName & "[Genotype '#1]," _
                                           & """" & "*" & """" & "," & "Table" & StrainName & _
                                           "[Genotype '#2]," & """" & "*" & """" & "," & "Table" & StrainName & _
                                           "[Genotype '#3]," & """" & "*" & """" & ")"
                  End If
             ActiveCell.Offset(1, 0).Select
             ActiveWorkbook.Names.Add Name:="FinishedEnd", RefersToR1C1:=ActiveCell
                          
             'Constructs the In Progress formula
             ActiveCell.Offset(-1, 1).Select
                  If GenoTypes = "1" Then
                     ActiveCell.Formula = "=COUNTBLANK(Table" & StrainName & "[Genotype '#1])"
                  ElseIf GenoTypes = "2" Then
                     ActiveCell.Formula = "=COUNTBLANK(Table" & StrainName & "[Genotype '#1])" _
                                          & "+COUNTBLANK(Table" & StrainName & "[Genotype '#2])"
                  Else
                     ActiveCell.Formula = "=COUNTBLANK(Table" & StrainName & "[Genotype '#1])" _
                                          & "+COUNTBLANK(Table" & StrainName & "[Genotype '#2])" _
                                          & "+COUNTBLANK(Table" & StrainName & "[Genotype '#3])"
                  End If
             ActiveCell.Offset(1, 0).Select
             ActiveWorkbook.Names.Add Name:="InprogressEnd", RefersToR1C1:=ActiveCell
             
             'Contructs the Mice to be tailed formula
             ActiveCell.Offset(-1, 1).Select
             ActiveCell.Formula = "=COUNTBLANK(Table" & StrainName & "[Earmark])"
             ActiveCell.Offset(1, 0).Select
             ActiveWorkbook.Names.Add Name:="TobetailedEnd", RefersToR1C1:=ActiveCell
             
             'Constructs the Born formula
             ActiveCell.Offset(-1, 1).Select
             ActiveCell.Formula = "=COUNTIFS(" & DOB & "," & GrThan & "," & DOB & "," & LsThan
             ActiveCell.Offset(1).Select
             ActiveWorkbook.Names.Add Name:="BornEnd", RefersToR1C1:=ActiveCell
             
             'Constructs the Weaned Formula
             ActiveCell.Offset(-1, 1).Select
             ActiveCell.Formula = "=COUNTIFS(" & DOW & "," & GrThan & "," & DOW & "," & LsThan
             ActiveCell.Offset(1).Select
             ActiveWorkbook.Names.Add Name:="WeanedEnd", RefersToR1C1:=ActiveCell
             
             'Constructs the Died Formula
             ActiveCell.Offset(-1, 1).Select
             ActiveCell.Formula = "=COUNTIFS(" & DOS & "," & GrThan & "," & DOS & "," & LsThan
             ActiveCell.Offset(1).Select
             ActiveWorkbook.Names.Add Name:="DiedEnd", RefersToR1C1:=ActiveCell
             
             'Constructs the Exp Formula
             ActiveCell.Offset(-1, 1).Select
             ActiveCell.Formula = "=COUNTIFS(" & DOE & "," & GrThan & "," & DOE & "," & LsThan
             ActiveCell.Offset(1).Select
             ActiveWorkbook.Names.Add Name:="ExpEnd", RefersToR1C1:=ActiveCell
             
             'Constructs the Cages Formula
             ActiveCell.Offset(-1, 1).Select
             ActiveCell.FormulaArray = "=SUM(SIGN(FREQUENCY(IF(" & Saced & "=" & """" & "N" & """" & "," & Cage & ")," & Cage & ")))"
             ActiveCell.Offset(1).Select
             ActiveWorkbook.Names.Add Name:="CagesEnd", RefersToR1C1:=ActiveCell
             
             
             'Builds the summary total formula
             Range("FinishedEnd").Formula = "=SUM(SUMIF(FinishedStart:OFFSET(FinishedEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             Range("InprogressEnd").Formula = "=SUM(SUMIF(InprogressStart:OFFSET(InprogressEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             Range("TobetailedEnd").Formula = "=SUM(SUMIF(TobetailedStart:OFFSET(TobetailedEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             Range("BornEnd").Formula = "=SUM(SUMIF(BornStart:OFFSET(BornEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             Range("WeanedEnd").Formula = "=SUM(SUMIF(WeanedStart:OFFSET(WeanedEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             Range("DiedEnd").Formula = "=SUM(SUMIF(DiedStart:OFFSET(DiedEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             Range("ExpEnd").Formula = "=SUM(SUMIF(ExpStart:OFFSET(ExpEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             Range("CagesEnd").Formula = "=SUM(SUMIF(CagesStart:OFFSET(CagesEnd, -1,0),{" & """" & "<0" & """" & "," & """" & ">0" & """" & "}))"
             
        'Sets the summary sheet back to active
        Worksheets("Summary").Select
        
      
Else
    MsgBox "That Breeder Name already exists!! Please try again."
    Exit Sub
End If

End Sub

Hopefully I am not breaking a forum rule but my code puts me over the character limit and so here is the end of problem:

Basically this code copies the last line the summary table and then changes the equations to now match the users input for the new strain. When I step through the code line by line, the "saced" and "cage" variables are properly filled in with the users input and all the first four equations fall into place perfectly, but that array formula on line 157 wont enter into the cell. For some reason it just keeps the equation copied from the line above???? I have been scratching my head of this one for a few days now and I have gotten no where.

I know that if I replace the line with something like this:

ActiveCell.FormulaArray = "TEST STRING"

the words "Test String" appear in the proper cell. And if I use a regular formula instead of an array formula it seems to work properly. Even if I put just a formula like:

ActiveCell.FormulaArray = "=1+1"

the cell value becomes {=1+1}

BUT for some reason, if I use the equation I NEED in the cell it wont set the cells value to the right equation?

I have attached the sample spread sheet. Click the "Add Strain" button on the summary sheet and you can add a strain, then check its corresponding "cages" equation and you will see the equation is still the same as the cell above it. Maybe someone has an idea as to why?

Please forgive my code, I just started learning VBA in december and so my code is prob not as efficient as it could be, but for the most part it gets the job I needed done.

Thanks for the help!!!