VBA Excel FormulaR1C1 Application Defined or Object Defined Error

Question

Use a recorded macro to define the correct formulas before attempting to update them in accordance with each "0" line to calculate subs and maximum values. If you do this, a definition error will occur. FormulaR1C1 couldn't be written correctly, which is the issue. use excel 2019 pro plus. Please assist me.

Here's 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.

I hope this helps you.