Updating a formula with a dynamic range

[mdo8105]

I'm running into a wall trying to accomplish updating a static formula in Cell N2. The first piece of my script works due to the start of my range will always be I19. I have a formula in N1 that is based off of range I19:the last cell before a blank (RowNum) and that works great. My issue is I have another range on that same sheet that is dynamic. The first part of this range will always be 9 cells down from my varible (RowNum) and I am naming that varible (RowNum2) and the last cell of the range will be the last cell before a blank from varible (RowNum2). The formula that I am needing updated is this: =IF(COUNTIF(RowNum2:RowNum3,"Pass")= Q1,"Passed",IF(COUNTIF(RowNum2:RowNum3,"Fail")>0,"Failed",IF(COUNTIF(RowNum2:RowNum3,"Untested")= Q1,"Untested",IF(COUNTIF(I19:I48,"Pass")< Q1,"Inprogress"))))

Q1 in that formula is counting the cells in that dynamic range, but honestly if I could replace Q1 in the formual with a varible that counts the cells from RowNum2:RowNum 3, that would be wonderful.

My issue is the FormulaR1C1 is not working properly. When I run the code listed below My formula should look like this: =IF(COUNTIF(I56:I69,"Pass")= Q2,"Passed",IF(COUNTIF(I56:I69,"Fail")>0,"Failed",IF(COUNTIF(I56:I69,"Untested")= Q2,"Untested",IF(COUNTIF(I56:I69,"Pass")< Q2,"Inprogress"))))

and the formula in Q2 should look like this: =COUNTA(I56:INDEX(I58:I6556,MAX(1,MATCH(TRUE,INDEX(I56:I6556="",0,0),0))))

But the formula in N2 is showing up like this: =IF(COUNTIF(I58:I71,"Pass")= Q2,"Passed",IF(COUNTIF(I20:I49,"Fail")>0,"Failed",IF(COUNTIF(I20:I49,"Untested")= Q2,"Untested",IF(COUNTIF(I20:I49,"Pass")< Q2,"Inprogress"))))

And formula in Q2 is looking like this: =COUNTA(I58:INDEX(I58:I6556,MAX(1,MATCH(TRUE,INDEX(I58:I6556="",0,0),0))

I would love any feedback, I am pretty new to VB and I cannot wrap my head around this. Here is my code:

Sub UpdateFormula()Dim ASAP_EDRN As Worksheet
Dim ASAP_EDMGR As Worksheet
Dim EPICCARE_IP As Worksheet
Dim RowNum As Integer
Dim RowNum2 As Integer
Dim RowNum3 As Long








Set ASAP_EDRN = ActiveWorkbook.Sheets("ASAP_EDRN")
Set ASAP_EDMGR = ActiveWorkbook.Sheets("ASAP_EDMGR")
Set EPICCARE_IP = ActiveWorkbook.Sheets("EPICCARE_INPATIENT_IPISOIN")


'Finds the last row before a blank starting at I19 (this will always be fixed)
RowNum = ASAP_EDRN.Range("I19").End(xlDown).Row
'Finds the first cell after header (this cell will always be 9 cells away from varible RowNum)This helps me find the row number for the first cell in the dynamic range
RowNum2 = ASAP_EDRN.Range("I30").End(xlDown).Offset(9).Row
'This finds the first cell with data from the bottom (This helps me find the row number for the last Range in the dynamic Range)
RowNum3 = ASAP_EDRN.Cells(Rows.Count, 9).End(xlUp).Row


    ASAP_EDRN.Range("N1").FormulaR1C1 = _
        "=IF(COUNTIF(R[18]C[-5]:R[" & RowNum & "]C[-5],""Pass"")= R[0]C[3],""Passed"",IF(COUNTIF(R[18]C[-5]:R[" & RowNum & "]C[-5],""Fail"")>0,""Failed"",IF(COUNTIF(R[18]C[-5]:R[" & RowNum & "]C[-5],""Untested"")= R[0]C[3],""Untested"",IF(COUNTIF(R[18]C[-5]:R[" & RowNum & "]C[-5],""Pass"")< R[0]C[3],""Inprogress""))))"


   ASAP_EDRN.Range("Q2").FormulaR1C1 = _
        "=COUNTA(R[" & RowNum2 & "]C[-8]:INDEX(R[" & RowNum2 & "]C[-8]:R[6554]C[-8],MAX(1,MATCH(TRUE,INDEX(R[" & RowNum2 & "]C[-8]:R[6554]C[-8]="""",0,0),0))))"
        
    
    ASAP_EDRN.Range("N2").FormulaR1C1 = _
        "=IF(COUNTIF(R[" & RowNum2 & "]C[-5]:R[" & RowNum3 & "]C[-5],""Pass"")= R[0]C[3],""Passed"",IF(COUNTIF(R[18]C[-5]:R[" & RowNum & "]C[-5],""Fail"")>0,""Failed"",IF(COUNTIF(R[18]C[-5]:R[" & RowNum & "]C[-5],""Untested"")= R[0]C[3],""Untested"",IF(COUNTIF(R[18]C[-5]:R[" & RowNum & "]C[-5],""Pass"")< R[0]C[3],""Inprogress""))))"




End Sub