12/01/2017
Makro İle Netten Brüt Maaş Hesaplama
------------------------------------------------------------------------------------
Sub nettenbrutehesapla()
Application.ScreenUpdating = False
Dim s1, s2, s3 As Worksheet
Dim brut, net, gv_mtrh, dv_mtrh, ssk_tv, ssk_m, kgv_mtrh, ssk_prim, isz_prim, kuml As Double
Dim tahay As Date
Dim ssk_orn, isz_orn, dv_orn, yil, isv_ssk_orn, w As Integer
Dim gv As Double
Set s1 = ThisWorkbook.Sheets("Netten_Brut")
Set s2 = ThisWorkbook.Sheets("Parametre")
Set s3 = ThisWorkbook.Sheets("Görev Tanımları")
s1.Range("E" & hesapsat & ":ER" & hesapsat).ClearContents
'M's1.Range("n11:o22").ClearContents
Sd = s1.Cells(hesapsat, 4) 'sigorta durumu%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'SİGORTA DURUMU
If Sd = "Normal" Or Sd = "Emekli" Then
Sd = Sd
Else
ay = 1
For nbsut = 5 To 137 Step 12
s1.Cells(hesapsat, nbsut) = s3.Cells(maassat, ay + 18)
s1.Cells(hesapsat, nbsut + 1) = 0
s1.Cells(hesapsat, nbsut + 2) = 0
s1.Cells(hesapsat, nbsut + 3) = 0
s1.Cells(hesapsat, nbsut + 4) = 0
s1.Cells(hesapsat, nbsut + 5) = 0
s1.Cells(hesapsat, nbsut + 6) = 0
s1.Cells(hesapsat, nbsut + 7) = 0
s1.Cells(hesapsat, nbsut + 8) = 0
s1.Cells(hesapsat, nbsut + 9) = 0
s1.Cells(hesapsat, nbsut + 10) = 0
s1.Cells(hesapsat, nbsut + 11) = s3.Cells(maassat, ay + 18) 'maliyet
ay = ay + 1
Next
Exit Sub
End If
kuml = 0
yil = 2013 '&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& YIL
sonSatir = 3
For ay = 1 To 12
Select Case ay
Case 1: nbsut = 5: kssut = 5
Case 2: nbsut = 17: kssut = 6
Case 3: nbsut = 29: kssut = 7
Case 4: nbsut = 41: kssut = 8
Case 5: nbsut = 53: kssut = 9
Case 6: nbsut = 65: kssut = 10
Case 7: nbsut = 77: kssut = 11
Case 8: nbsut = 89: kssut = 12
Case 9: nbsut = 101: kssut = 13
Case 10: nbsut = 113: kssut = 14
Case 11: nbsut = 125: kssut = 15
Case 12: nbsut = 137: kssut = 16
End Select
If Sheets("Görev Tanımları").Cells(maassat, kssut) = 0 Or Sheets("Görev Tanımları").Cells(maassat, kssut) = "" Then
If ay = 1 Then
s1.Cells(hesapsat, nbsut + 5) = 0
s1.Cells(hesapsat, nbsut + 6) = 0
Else
s1.Cells(hesapsat, nbsut + 5) = s1.Cells(hesapsat, (nbsut - 12) + 5)
s1.Cells(hesapsat, nbsut + 6) = s1.Cells(hesapsat, (nbsut - 12) + 6)
End If
GoTo gec
End If
x = s3.Cells(maassat, ay + 18) 'İstenen net
y = x * 1.3538
brut = y
On Error Resume Next
Do While x Round(s1.Cells(hesapsat, nbsut), 2)
gelirvg = 0
ssk_m = 0
ssk_prim = 0
isz_prim = 0
gvmtr = 0
If ay = 1 Then
kuml = 0
Else
kuml = s1.Cells(hesapsat, (nbsut - 12) + 5) + s1.Cells(hesapsat, (nbsut - 12) + 6)
End If
brut = brut - (s1.Cells(hesapsat, nbsut) - x)
tahay = Format(DateSerial(yil, ay, 1), "dd/mm/yyyy")
For w = 2 To sonSatir
If Year(s2.Cells(w, 1)) = Year(tahay) Then
If Month(s2.Cells(w, 1))
