VBA Excel FormulaR1C1 "Application Defined or Object Defined Error"

Question

ı try to use excel formulas ın vba , use record macro to defıne correct formulas then try to change accordıng to each "0" line to calculate subs, max value but get defıne error . the problem ıs FormulaR1C1 , couldnt wrıte correctly . use excel 2019 pro plus .someone help me

type here

heres the code :

Sub hesapla()
Dim zero_ındex As Integer
Dim lnn As Integer
Dim row As Integer
Dim a As Integer
Dim b As Integer
Dim Rng As Range
a = 2
b = 0
Sheet3.Activate
rown = Sheet3.Cells(Rows.Count, 1).End(xlUp).row


For a = 2 To rown

Do While Sheet3.Cells(a, 1).Value <> ""

If Sheet3.Cells(a, 1).Value = "0" Then
zero_ındex = a
b = a
b = b + 1


    Do While Sheet3.Cells(b, 1).Value <> "0"
    b = b + 1
    
        
    Loop
lnn = b - 1

Sheet3.Range("U" & a).Select
 Selection.FormulaR1C1 = "=SUMPRODUCT(MAX(((RC[-3]:R[ " & lnn & " ]C[-3]=""B"")+(RC[-3]:R[ " & lnn & " ]C[-3]=""Y""))*(RC[-2]:R[ " & lnn & " C[-2])))"

End If
a = a + 1
Loop
Next a
End Sub----

Answer 1

Consider using a fixed template for the formula and Replace() to fill in the values.

Option Explicit

Sub hesapla()
   
    Dim lastrow As Long, n As Long, r As Long
  
    Const F = "=SUMPRODUCT(MAX(((RC[-3]:R[#]C[-3]=""B"")" & _
              "+(RC[-3]:R[#]C[-3]=""Y""))*(RC[-2]:R[#]C[-2])))"
    
    With Sheet3
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).row
        For r = lastrow To 2 Step -1
            If .Cells(r, "A") <> "" Then
                If .Cells(r, "A") = 0 Then
                    .Range("U" & r).FormulaR1C1 = Replace(F, "#", n)
                    n = 0
                Else
                    n = n + 1
                End If
            End If
        Next
    End With
    MsgBox "Done"
End Sub