Formül kısaltmak

[cerit]

Merhaba,
çoketopla formülündeki tekrarları nasıl kısaltabiliriz.
saygılar.

Private Sub Worksheet_Activate()
Dim Say As Long

Say = Worksheets("GİRİŞ").Cells(Rows.Count, "A").End(xlUp).Row
With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

Range("B4") = 1
Range("B4:B" & Say).DataSeries
Range("C4:C" & Say).FormulaLocal = "=GİRİŞ!A4 & GİRİŞ!B4 & GİRİŞ!F4"
Range("D4" & Say).FormulaLocal = "=IsimMaskele(GİRİŞ!G4)"
Range("E4:E" & Say).FormulaLocal = "=EĞERHATA(DÜŞEYARA(D4;GİRİŞ!G3:U" & Say & ";3;0);"""")"
Range("F4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(f$3&""!D"");DOLAYLI(f$3&""!C:C"");D4)"
Range("G4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(G$3&""!D"");DOLAYLI(G$3&""!C:C"");D4)"
Range("H4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(H$3&""!D"");DOLAYLI(H$3&""!C:C"");D4)"
Range("I4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(I$3&""!D"");DOLAYLI(I$3&""!C:C"");D4)"
Range("J4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(J$3&""!D"");DOLAYLI(J$3&""!C:C"");D4)"
Range("K4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(K$3&""!D"");DOLAYLI(K$3&""!C:C"");D4)"
Range("L4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(L$3&""!D"");DOLAYLI(L$3&""!C:C"");D4)"
Range("M4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(M$3&""!D"");DOLAYLI(M$3&""!C:C"");D4)"
Range("N4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(N$3&""!D"");DOLAYLI(N$3&""!C:C"");D4)"
Range("O4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(O$3&""!D"");DOLAYLI(O$3&""!C:C"");D4)"
Range("P4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(P$3&""!D"");DOLAYLI(P$3&""!C:C"");D4)"
Range("Q4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(Q$3&""!D"");DOLAYLI(Q$3&""!C:C"");D4)"
Range("R4:R" & Say).FormulaLocal = "=TOPLA(F4:Q4)"
'Range("S4:S" & Say).FormulaLocal = "=(T4)*5%"
Range("T4:T" & Say).FormulaLocal = "=TOPLA((GİRİŞ!M4:X4);E4)-R4"
Range("U4:U" & Say).FormulaLocal = "=DEMİRBAŞ!U4"
Range("V4:V" & Say).FormulaLocal = "=S4+T4+U4"

Range("C4:V" & Say).Value = Range("C4:V" & Say).Value
With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub

 

[Korhan Ayhan]

Deneyiniz.

Not: Formda kod paylaşırken CODE tagını kullanırsanız emojilerden kurtulmuş olursunuz.

Option Explicit

Private Sub Worksheet_Activate()
    Dim Say As Long

    On Error GoTo Safe_Exit

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    With Worksheets("GİRİŞ")
        Say = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
        
    Range("B4") = 1
    Range("B4:B" & Say).DataSeries
    
    Range("C4:C" & Say).FormulaLocal = "=GİRİŞ!A4 & GİRİŞ!B4 & GİRİŞ!F4"
    Range("D4:D" & Say).FormulaLocal = "=IsimMaskele(GİRİŞ!G4)"
    Range("E4:E" & Say).FormulaLocal = "=EĞERHATA(DÜŞEYARA(D4;GİRİŞ!G3:U" & Say & ";3;0);"""")"
    Range("F4:Q" & Say).FormulaLocal = "=ÇOKETOPLA(DOLAYLI(F$3&""!D:D"");DOLAYLI(F$3&""!C:C"");$D4)"
    Range("R4:R" & Say).FormulaLocal = "=TOPLA(F4:Q4)"
    'Range("S4:S" & Say).FormulaLocal = "=(T4)*5%"
    Range("T4:T" & Say).FormulaLocal = "=TOPLA((GİRİŞ!M4:X4);E4)-R4"
    Range("U4:U" & Say).FormulaLocal = "=DEMİRBAŞ!U4"
    Range("V4:V" & Say).FormulaLocal = "=S4+T4+U4"
    
    With Range("C4:V" & Say)
        .Value = .Value
    End With
    
Safe_Exit:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

 

[cerit]

Hocam elinize sağlık,teşekkür ederim.