• DİKKAT

    DOSYA İndirmek/Yüklemek için ÜCRETLİ ALTIN ÜYELİK Gereklidir!
    Altın Üyelik Hakkında Bilgi

Bordro-Puantaj

S

sedat2161

Katılım
1 Ekim 2017
Mesajlar
694
Excel Vers. ve Dili
2019 türkçe
Hayırlı sabahlar. Ekteki bordro sayfasında butona tıkladığımda hesaplama yapıyor. Ancak en sondaki kişi hesaplanmıyor. Bide en son toplamları butona nasıl ekleyebilirim. Yardımcı olursanız sevinirim. İyi çalışmalar.
 

Ekli dosyalar

  • 42.xlsm
    42.xlsm
    936.5 KB · Görüntüleme: 48
kod:

Rich (BB code): 
Sub BORDRO_2YuvarlatılmışDikdörtgen_Tıklat()
Dim son As Long
Application.ScreenUpdating = False
'son = Range("C2", Range("C2").End(xlDown)).Count
son = Worksheets("BORDRO").Cells(Rows.Count, "C").End(3).Row

Set s1 = Sheets("BİLGİ")
On Error Resume Next
MsgBox son
For i = 6 To son
    Cells(i, 6).Value = Round(Range("D4").Value / 30, 2)
    Cells(i, 7).Value = Round(Cells(i, 5) * Cells(i, 6), 2)
    Cells(i, 8).Value = Evaluate("=INT(G" & i & "*0.14)+INT(MOD(G" & i & "*0.14,1)*100)/100")
    Cells(i, 9).Value = Evaluate("=INT(G" & i & "*0.01)+INT(MOD(G" & i & "*0.01,1)*100)/100")
    Cells(i, 9).Value = Round(Cells(i, 7) * 0.01, 2)
    Cells(i, 10).Value = Round(Cells(i, 7) - Cells(i, 8) - Cells(i, 9), 2)
    Cells(i, 11).Value = Round(Cells(i, 10) * 0.15, 2)
    Cells(i, 13).Value = Cells(i, 11) - Cells(i, 12)
    Cells(i, 14).Value = Round(Cells(i, 7) * 0.00759, 2)
    Cells(i, 16).Value = Cells(i, 8) + Cells(i, 9) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15)
    Cells(i, 17).Value = Cells(i, 7) - Cells(i, 16)
    Cells(i, 18).Value = Cells(i, 5) * Cells(i, 6)
    Cells(i, 19).Value = Round(Cells(i, 7) * 0.205, 2)
  
    Cells(i, 20).Value = Evaluate("=INT(G" & i & "*0.02)+INT(MOD(G" & i & "*0.02,1)*100)/100")
    Cells(i, 21).Value = Cells(i, 18) + Cells(i, 19) + Cells(i, 20)
Next i

For k = 5 To 21
Worksheets("BORDRO").Cells(509, k).Value = WorksheetFunction.Sum(Worksheets("BORDRO").Range(Worksheets("BORDRO").Cells(6, k), Worksheets("BORDRO").Cells(507, k)))
Next k


Application.ScreenUpdating = True
End Sub
 
Kod: 
Sub BORDRO_2YuvarlatılmışDikdörtgen_Tıklat()
Dim son As Long
Application.ScreenUpdating = False
son = Range("C1", Range("c6").End(xlDown)).Count
Set s1 = Sheets("BİLGİ")
On Error Resume Next
For i = 6 To son
    Cells(i, 6).Value = Round(Range("D4").Value / 30, 2)
    Cells(i, 7).Value = Round(Cells(i, 5) * Cells(i, 6), 2)
    Cells(i, 8).Value = Evaluate("=INT(G" & i & "*0.14)+INT(MOD(G" & i & "*0.14,1)*100)/100")
    Cells(i, 9).Value = Evaluate("=INT(G" & i & "*0.01)+INT(MOD(G" & i & "*0.01,1)*100)/100")
    Cells(i, 9).Value = Round(Cells(i, 7) * 0.01, 2)
    Cells(i, 10).Value = Round(Cells(i, 7) - Cells(i, 8) - Cells(i, 9), 2)
    Cells(i, 11).Value = Round(Cells(i, 10) * 0.15, 2)
    Cells(i, 13).Value = Cells(i, 11) - Cells(i, 12)
    Cells(i, 14).Value = Round(Cells(i, 7) * 0.00759, 2)
    Cells(i, 16).Value = Cells(i, 8) + Cells(i, 9) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15)
    Cells(i, 17).Value = Cells(i, 7) - Cells(i, 16)
    Cells(i, 18).Value = Cells(i, 5) * Cells(i, 6)
    Cells(i, 19).Value = Round(Cells(i, 7) * 0.205, 2)
   
    Cells(i, 20).Value = Evaluate("=INT(G" & i & "*0.02)+INT(MOD(G" & i & "*0.02,1)*100)/100")
    Cells(i, 21).Value = Cells(i, 18) + Cells(i, 19) + Cells(i, 20)
Next i
For i = 5 To 21
    Cells(509, i) = WorksheetFunction.Sum(Range(Cells(6, i), Cells(507, i)))
Next i

Application.ScreenUpdating = True
End Sub
 
Halit Üstadın çözümünü görmemişim. Kodlar aşağı yukarı aynısı oldu.
 
halit3' Alıntı:
kod:

Rich (BB code): 
Sub BORDRO_2YuvarlatılmışDikdörtgen_Tıklat()
Dim son As Long
Application.ScreenUpdating = False
'son = Range("C2", Range("C2").End(xlDown)).Count
son = Worksheets("BORDRO").Cells(Rows.Count, "C").End(3).Row

Set s1 = Sheets("BİLGİ")
On Error Resume Next
MsgBox son
For i = 6 To son
    Cells(i, 6).Value = Round(Range("D4").Value / 30, 2)
    Cells(i, 7).Value = Round(Cells(i, 5) * Cells(i, 6), 2)
    Cells(i, 8).Value = Evaluate("=INT(G" & i & "*0.14)+INT(MOD(G" & i & "*0.14,1)*100)/100")
    Cells(i, 9).Value = Evaluate("=INT(G" & i & "*0.01)+INT(MOD(G" & i & "*0.01,1)*100)/100")
    Cells(i, 9).Value = Round(Cells(i, 7) * 0.01, 2)
    Cells(i, 10).Value = Round(Cells(i, 7) - Cells(i, 8) - Cells(i, 9), 2)
    Cells(i, 11).Value = Round(Cells(i, 10) * 0.15, 2)
    Cells(i, 13).Value = Cells(i, 11) - Cells(i, 12)
    Cells(i, 14).Value = Round(Cells(i, 7) * 0.00759, 2)
    Cells(i, 16).Value = Cells(i, 8) + Cells(i, 9) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15)
    Cells(i, 17).Value = Cells(i, 7) - Cells(i, 16)
    Cells(i, 18).Value = Cells(i, 5) * Cells(i, 6)
    Cells(i, 19).Value = Round(Cells(i, 7) * 0.205, 2)

    Cells(i, 20).Value = Evaluate("=INT(G" & i & "*0.02)+INT(MOD(G" & i & "*0.02,1)*100)/100")
    Cells(i, 21).Value = Cells(i, 18) + Cells(i, 19) + Cells(i, 20)
Next i

For k = 5 To 21
Worksheets("BORDRO").Cells(509, k).Value = WorksheetFunction.Sum(Worksheets("BORDRO").Range(Worksheets("BORDRO").Cells(6, k), Worksheets("BORDRO").Cells(507, k)))
Next k


Application.ScreenUpdating = True
End Sub
Genişletmek için tıkla ...
ilginiz on toplamda küsuratların çıkmaması için ne yapabiliriz.
 
üstte cevap yazarken bir yanlışlık oldu üstadlar. ilginiz için çok teşekkür ederim. son toplamlarda küsuratsız toplama yapma imkanı olursa çok iyi olur. iyi çalışmalar.
 
Hesaplamaları hücreler bazında küsüratları atmanız gerekir toplamlarda küsüratlar atılır yazılırsa kiş bazında heaplandığında eksiklik oldu görülür neyse kod bu:

Rich (BB code): 
Sub BORDRO_2YuvarlatılmışDikdörtgen_Tıklat()
Dim son As Long
Application.ScreenUpdating = False
'son = Range("C2", Range("C2").End(xlDown)).Count
son = Worksheets("BORDRO").Cells(Rows.Count, "C").End(3).Row

Set s1 = Sheets("BİLGİ")
'On Error Resume Next

For i = 6 To son
    Cells(i, 6).Value = Round(Range("D4").Value / 30, 2)
    Cells(i, 7).Value = Round(Cells(i, 5) * Cells(i, 6), 2)
    Cells(i, 8).Value = Evaluate("=INT(G" & i & "*0.14)+INT(MOD(G" & i & "*0.14,1)*100)/100")
    Cells(i, 9).Value = Evaluate("=INT(G" & i & "*0.01)+INT(MOD(G" & i & "*0.01,1)*100)/100")
    Cells(i, 9).Value = Round(Cells(i, 7) * 0.01, 2)
    Cells(i, 10).Value = Round(Cells(i, 7) - Cells(i, 8) - Cells(i, 9), 2)
    Cells(i, 11).Value = Round(Cells(i, 10) * 0.15, 2)
    Cells(i, 13).Value = Cells(i, 11) - Cells(i, 12)
    Cells(i, 14).Value = Round(Cells(i, 7) * 0.00759, 2)
    Cells(i, 16).Value = Cells(i, 8) + Cells(i, 9) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15)
    Cells(i, 17).Value = Cells(i, 7) - Cells(i, 16)
    Cells(i, 18).Value = Cells(i, 5) * Cells(i, 6)
    Cells(i, 19).Value = Round(Cells(i, 7) * 0.205, 2)
  
    Cells(i, 20).Value = Evaluate("=INT(G" & i & "*0.02)+INT(MOD(G" & i & "*0.02,1)*100)/100")
    Cells(i, 21).Value = Cells(i, 18) + Cells(i, 19) + Cells(i, 20)
Next i

For k = 5 To 21
Worksheets("BORDRO").Cells(509, k).Value = Round(WorksheetFunction.Sum(Worksheets("BORDRO").Range(Worksheets("BORDRO").Cells(6, k), Worksheets("BORDRO").Cells(507, k))), 2)
Next k


Application.ScreenUpdating = True
End Sub
 
Bu kodu kullan
Rich (BB code): 
Sub BORDRO_2YuvarlatılmışDikdörtgen_Tıklat()
Dim son As Long
Application.ScreenUpdating = False
'son = Range("C2", Range("C2").End(xlDown)).Count
son = Worksheets("BORDRO").Cells(Rows.Count, "C").End(3).Row

Set s1 = Sheets("BİLGİ")
'On Error Resume Next

For i = 6 To son
    Cells(i, 6).Value = Round(Range("D4").Value / 30, 2)
    Cells(i, 7).Value = Round(Cells(i, 5) * Cells(i, 6), 2)
    Cells(i, 8).Value = Round(Cells(i, 7) * 0.14, 2)
    Cells(i, 9).Value = Round(Cells(i, 7) * 0.01, 2)
    Cells(i, 10).Value = Round(Cells(i, 7) - Cells(i, 8) - Cells(i, 9), 2)
    Cells(i, 11).Value = Round(Cells(i, 10) * 0.15, 2)
    Cells(i, 13).Value = Cells(i, 11) - Cells(i, 12)
    Cells(i, 14).Value = Round(Cells(i, 7) * 0.00759, 2)
    Cells(i, 16).Value = Cells(i, 8) + Cells(i, 9) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15)
    Cells(i, 17).Value = Cells(i, 7) - Cells(i, 16)
    Cells(i, 18).Value = Cells(i, 5) * Cells(i, 6)
    Cells(i, 19).Value = Round(Cells(i, 7) * 0.205, 2)
    Cells(i, 20).Value = Round(Cells(i, 7) * 0.02, 2)
    
    Cells(i, 21).Value = Cells(i, 18) + Cells(i, 19) + Cells(i, 20)
Next i

For k = 5 To 21
Worksheets("BORDRO").Cells(509, k).Value = Round(WorksheetFunction.Sum(Worksheets("BORDRO").Range(Worksheets("BORDRO").Cells(6, k), Worksheets("BORDRO").Cells(363, k))), 2)
Next k


Application.ScreenUpdating = True
End Sub
 
halit3' Alıntı:
Bu kodu kullan
Rich (BB code): 
Sub BORDRO_2YuvarlatılmışDikdörtgen_Tıklat()
Dim son As Long
Application.ScreenUpdating = False
'son = Range("C2", Range("C2").End(xlDown)).Count
son = Worksheets("BORDRO").Cells(Rows.Count, "C").End(3).Row

Set s1 = Sheets("BİLGİ")
'On Error Resume Next

For i = 6 To son
    Cells(i, 6).Value = Round(Range("D4").Value / 30, 2)
    Cells(i, 7).Value = Round(Cells(i, 5) * Cells(i, 6), 2)
    Cells(i, 8).Value = Round(Cells(i, 7) * 0.14, 2)
    Cells(i, 9).Value = Round(Cells(i, 7) * 0.01, 2)
    Cells(i, 10).Value = Round(Cells(i, 7) - Cells(i, 8) - Cells(i, 9), 2)
    Cells(i, 11).Value = Round(Cells(i, 10) * 0.15, 2)
    Cells(i, 13).Value = Cells(i, 11) - Cells(i, 12)
    Cells(i, 14).Value = Round(Cells(i, 7) * 0.00759, 2)
    Cells(i, 16).Value = Cells(i, 8) + Cells(i, 9) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15)
    Cells(i, 17).Value = Cells(i, 7) - Cells(i, 16)
    Cells(i, 18).Value = Cells(i, 5) * Cells(i, 6)
    Cells(i, 19).Value = Round(Cells(i, 7) * 0.205, 2)
    Cells(i, 20).Value = Round(Cells(i, 7) * 0.02, 2)
   
    Cells(i, 21).Value = Cells(i, 18) + Cells(i, 19) + Cells(i, 20)
Next i

For k = 5 To 21
Worksheets("BORDRO").Cells(509, k).Value = Round(WorksheetFunction.Sum(Worksheets("BORDRO").Range(Worksheets("BORDRO").Cells(6, k), Worksheets("BORDRO").Cells(363, k))), 2)
Next k


Application.ScreenUpdating = True
End Sub
Genişletmek için tıkla ...
teşekkür ederim halit bey oldu. iyi çalışmalar
 
Teşekkürler iyi çalışmalar
 
Cevap yazmak için giriş yap yada kayıt ol.
Geri
Üst