VBA Cell Formula to Name Range

[OilEconomist]

Thanks in advance for any suggestions for which I will give feedback.

I am trying to set cell to a formula involving name ranges. Where, for example cell D53 [Cell(53,4)] formula would be = V1.Total.NPV10 + V2.Total.NPV10 + V3.Total.NPV10 + V4.Total.NPV10 + V5.Total.NPV10

since the value in cell D1 [cell(1, 4)] = NPV10


Why does the following line of code turn red? Also, have I done the quotes done correctly?

 ActiveSheet.Cells.(53, j).Formula "= V1.Total." & Cells(1, j).Value & "+" _
                    "V2.Total." & Cells(1, j).Value & "+" _
                    "V3.Total." & Cells(1, j).Value & "+" _
                    "V4.Total." & Cells(1, j).Value & "+" _
                    "V5.Total." & Cells(1, j).Value"

Entire code is as follows:

Sub FormulawithNameRanges()


    Dim j As Integer
    
    Worksheets("1").Activate


        For j = 4 To 26
            
                ActiveSheet.Cells.(53, j).Formula "= V1.Total." & Cells(1, j).Value & "+" _
                    "V2.Total." & Cells(1, j).Value & "+" _
                    "V3.TOTAL." & Cells(1, j).Value & "+" _
                    "V4.Total." & Cells(1, j).Value & "+" _
                    "V5.Total." & Cells(1, j).Value"
                
         End If
         
         Next j
         


End Sub

 

[KOKOSEK]

Is red because is incorrectly 'split' in rows.
Try this:

Sub FormulawithNameRanges()
    Dim j As Integer
    Worksheets("1").Activate
        For j = 4 To 26
            ActiveSheet.Cells(53, j).Formula [B]=[/B] "= V1.Total." & Cells(1, 4).Value & _
                "+ V2.Total." & Cells(1, 4).Value & "+ V3.TOTAL." & Cells(1, 4).Value & _
                "+ V4.Total." & Cells(1, 4).Value & "+V5.Total." & Cells(1, 4).Value
         Next j
End Sub

Also you've got End if without If
Missing = after formula

 

[OilEconomist]

Thanks KOKOSEK that fixed the issue and apologies for the late response.

 

[KOKOSEK]

Do not worry.
Glad to help.