Resolved: VBA formula not calculating and dynamic references

Option Explicit
Function NewValue()
End Function
Sub CalculateNewValue()

Dim CatPercentage1 As Single
Dim CatPercentage2 As Single
Dim CatPercentage3 As Single
Dim CatPercentage4 As Single
Dim CatPercentage5 As Single
Dim CatPercentage6 As Single
Dim CatValue1 As Single
Dim CatValue2 As Single
Dim CatValue3 As Single
Dim CatValue4 As Single
Dim CatValue5 As Single
Dim CatValue6 As Single
Dim TotalPercentageNA As Single
Dim NumberNA As Integer
Dim RemainingCat As Integer
Dim AddValue As Single
Dim NewValue As Single
Dim lRow As Long

'lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
'Range("AJ2").AutoFill Range("AJ2:AJ" & lRow)

NumberNA = 0
If Range("AD2") = "N/A" Then
CatPercentage1 = 0.3 And NumberNA = NumberNA + 1 And CatValue1 = 0
If Range("AE2") = "N/A" Then
CatPercentage1 = 0.2 And NumberNA = NumberNA + 1 And CatValue2 = 0
If Range("AF2") = "N/A" Then
CatPercentage1 = 0.2 And NumberNA = NumberNA + 1 And CatValue3 = 0
If Range("AG2") = "N/A" Then
CatPercentage1 = 0.1 And NumberNA = NumberNA + 1 And CatValue4 = 0
If Range("AH2") = "N/A" Then
CatPercentage1 = 0.15 And NumberNA = NumberNA + 1 And CatValue5 = 0
If Range("AI2") = "N/A" Then
CatPercentage1 = 0.05 And NumberNA = NumberNA + 1 And CatValue6 = 0
End If
End If
End If
End If
End If
End If

TotalPercentageNA = (CatPercentage1 + CatPercentage2 + CatPercentage3 + CatPercentage4 + CatPercentage5 + CatPercentage6)
RemainingCat = 6 - NumberNA
AddValue = TotalPercentageNA / RemainingCat

'MsgBox CatPercentage1
'MsgBox TotalPercentageNA
'MsgBox RemainingCat
'MsgBox NumberNA
'MsgBox AddValue


Range("AJ2").Value = CatValue1 * Range("AE2").Value * (0.3 + AddValue) + Range("AE2").Value * (0.2 + AddValue) + Range("AF2").Value * (0.2 + AddValue) + Range("AG2").Value * (0.1 + AddValue) + Range("AH2").Value * (0.15 + AddValue) + Range("AI2").Value * (0.05 + AddValue)

End Sub