Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long, satirbasveFazlasi As Long
Const satr As Byte = 16 'K sütunundaki verilerin son satir numarasi arananinkilerin
Const SatirBaslangic As Byte = 2 '3.cü sütundan baslandigi icin 2 yazildi
Dim sonDoluSatrEtopla As Long
sonDoluSatrEtopla = Cells.Find("*", , , , , xlPrevious).Row + 1
If Intersect(Target, Union(Range("A3" & Rows.Count), Range("F3:i" & Rows.Count))) Is Nothing Then Exit Sub
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect "123321"
Range("L3:N" & Rows.Count).ClearContents
On Error GoTo son
satirbasveFazlasi = satr + SatirBaslangic
Range("L3:L" & satirbasveFazlasi).Value = Evaluate("=SUMIF($A$3:$A$" & sonDoluSatrEtopla & ",K3:K" & satirbasveFazlasi & ",$D$3:$D$" & sonDoluSatrEtopla & ")")
Range("M3:M" & satirbasveFazlasi).Value = Evaluate("=SUMIF($F$3:$F$" & sonDoluSatrEtopla & ",K3:K" & satirbasveFazlasi & ",$I$3:$I$" & sonDoluSatrEtopla & ")")
ReDim arr(1 To satr, 1 To 1)
For i = 1 To satr
arr(i, 1) = Cells(i + SatirBaslangic, "L").Value - Cells(i + SatirBaslangic, "M").Value
Next
Range("N3:N" & satirbasveFazlasi).Value = arr
Range("L20").Value = WorksheetFunction.Sum(Range("D3" & sonDoluSatrEtopla))
Range("L21").Value = WorksheetFunction.Sum(Range("I3:I" & sonDoluSatrEtopla))
Range("L22").Value = Range("L20").Value - Range("L21").Value
Erase arr
ActiveSheet.Protect "123321"
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub
son:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "hata", vbCritical
End Sub
Birde Evaluate yötemii kulladım.Bununla formülleri değer yapmaktansa direk değer yapıyor.