Döngüler Hakkında

S

samsun2005

Katılım
8 Mart 2005
Mesajlar
77
Excel Vers. ve Dili
EXCEL 2003 TR
Altın Üyelik Bitiş Tarihi
20-06-2022
Arkadaşlar Herkese Merhaba ,


Ek te göndermiş olduğum bir çalışma var . Çalışmamda For Next Döngüsünü Kullanarak Veri Tabanından Emekli Kesenekleri Tablosuna (Dikkat Edilirse Tablo A-4 ebatında tek sayfadır ) veri aktarımı yapılmakta sayfadaki Tablo dolduğunda yazıcıdan çıktısı alınmakta , otomatik sayfa nosu verilmekte ,nakli yekün ve toplam yekün almaktadır.

Öğrenmek açısından soruyorum. Daha kısa başka bir döngü ile aynı işlem yapılabilirmi ?.

Özellikle Bu konu da Uzman Hocalarımızın yardımlarını bekliyorum.


Teşekkürler...




Kod: 
Sub bordrocu()
On Error Resume Next
Dim satir, satir2, satir3, satir4, satir5, satir6, sayfa
    Sheets("PEM2005").Visible = True
    Sheets("PEM2005").Select
    Sheets("KES2006").Visible = True
    Sheets("KES2006").Select
[sira3].Select
Selection.ClearContents
[alansil2].Select
Selection.ClearContents
[sira].Select
Selection.ClearContents
[sira2].Select
Selection.ClearContents
'[SAYFATOP].Select
'Selection.FormulaR1C1 = "=SUM(R[-20]C:R[-1]C)"
'[GENELTOP].Select
'Selection.FormulaR1C1 = "=R[-1]C+R[-22]C"
'Range("a7").Select
sayfa = 1
Range("V34") = sayfa
satir = 5
satir2 = 6
satir3 = 7
satir4 = 8
satir5 = 9
satir6 = 10
For a = 2 To 65000
    If Sheets("PEM2005").Cells(a, 1) = "" Then
        GoTo son
    End If
 
    If satir >= 29 Then
 
    satir = 4
    satir2 = 6
    satir3 = 7
    satir4 = 8
    satir5 = 9
    satir6 = 10
    Range("V34") = sayfa
    sayfa = sayfa + 1
 
 
 
                        If sayfa >= 2 Then
 
                      ActiveWindow.SelectedSheets.PrintPreview 'PrintOut Copies:=1, Collate:=True
 
                            'ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
                                Sheets("KES2006").Cells(satir, 9) = Sheets("KES2006").Cells(29, 9)
                                Sheets("KES2006").Cells(satir, 10) = Sheets("KES2006").Cells(29, 10)
                                Sheets("KES2006").Cells(satir, 11) = Sheets("KES2006").Cells(29, 11)
                                Sheets("KES2006").Cells(satir, 12) = Sheets("KES2006").Cells(29, 12)
                                Sheets("KES2006").Cells(satir, 13) = Sheets("KES2006").Cells(29, 13)
                                Sheets("KES2006").Cells(satir, 14) = Sheets("KES2006").Cells(29, 14)
                                Sheets("KES2006").Cells(satir, 15) = Sheets("KES2006").Cells(29, 15)
                                Sheets("KES2006").Cells(satir, 16) = Sheets("KES2006").Cells(29, 16)
                                Sheets("KES2006").Cells(satir, 17) = Sheets("KES2006").Cells(29, 17)
                                Sheets("KES2006").Cells(satir, 18) = Sheets("KES2006").Cells(29, 18)
                                Sheets("KES2006").Cells(satir, 19) = Sheets("KES2006").Cells(29, 19)
                                Sheets("KES2006").Cells(satir, 20) = Sheets("KES2006").Cells(29, 20)
                                Sheets("KES2006").Cells(satir, 21) = Sheets("KES2006").Cells(29, 21)
                                Sheets("KES2006").Cells(satir, 22) = Sheets("KES2006").Cells(29, 22)
 
 
 
 
                                satir = satir + 1
                            End If
 
 
    [alansil2].Select
         Selection.ClearContents
   [sira].Select
         Selection.ClearContents
 
    [sira2].Select
         Selection.ClearContents
 
   End If
        personel = personel + 1
        Sheets("KES2006").Cells(satir, 1) = personel
 
        Sheets("KES2006").Cells(satir, 5) = Sheets("PEM2005").Cells(a, 1)
        Sheets("KES2006").Cells(satir, 9) = Sheets("PEM2005").Cells(a, 8)
        Sheets("KES2006").Cells(satir, 10) = Sheets("PEM2005").Cells(a, 9)
        Sheets("KES2006").Cells(satir, 11) = Sheets("PEM2005").Cells(a, 10)
        Sheets("KES2006").Cells(satir, 12) = Sheets("PEM2005").Cells(a, 11)
        Sheets("KES2006").Cells(satir, 13) = Sheets("PEM2005").Cells(a, 12)
        Sheets("KES2006").Cells(satir, 14) = Sheets("PEM2005").Cells(a, 13)
        Sheets("KES2006").Cells(satir, 15) = Sheets("PEM2005").Cells(a, 14)
        Sheets("KES2006").Cells(satir, 16) = Sheets("PEM2005").Cells(a, 15)
        Sheets("KES2006").Cells(satir, 17) = Sheets("PEM2005").Cells(a, 16)
        Sheets("KES2006").Cells(satir, 18) = Sheets("PEM2005").Cells(a, 17)
        Sheets("KES2006").Cells(satir, 19) = Sheets("PEM2005").Cells(a, 18)
        Sheets("KES2006").Cells(satir, 20) = Sheets("PEM2005").Cells(a, 19)
 
 
        satir = satir + 6
 
        Sheets("KES2006").Cells(satir2, 5) = Sheets("PEM2005").Cells(a, 2)
        Sheets("KES2006").Cells(satir2, 9) = Sheets("PEM2005").Cells(a, 21)
        Sheets("KES2006").Cells(satir2, 10) = Sheets("PEM2005").Cells(a, 22)
        Sheets("KES2006").Cells(satir2, 11) = Sheets("PEM2005").Cells(a, 23)
        Sheets("KES2006").Cells(satir2, 12) = Sheets("PEM2005").Cells(a, 24)
        Sheets("KES2006").Cells(satir2, 13) = Sheets("PEM2005").Cells(a, 25)
        Sheets("KES2006").Cells(satir2, 14) = Sheets("PEM2005").Cells(a, 26)
        Sheets("KES2006").Cells(satir2, 15) = Sheets("PEM2005").Cells(a, 27)
        Sheets("KES2006").Cells(satir2, 16) = Sheets("PEM2005").Cells(a, 28)
        Sheets("KES2006").Cells(satir2, 17) = Sheets("PEM2005").Cells(a, 29)
        Sheets("KES2006").Cells(satir2, 18) = Sheets("PEM2005").Cells(a, 30)
        Sheets("KES2006").Cells(satir2, 19) = Sheets("PEM2005").Cells(a, 31)
        Sheets("KES2006").Cells(satir2, 20) = Sheets("PEM2005").Cells(a, 32)
        Sheets("KES2006").Cells(satir2, 22) = Sheets("PEM2005").Cells(a, 107)
        Sheets("KES2006").Cells(satir2, 23) = Sheets("PEM2005").Cells(a, 108)
 
        satir2 = satir2 + 6
 
  Sheets("KES2006").Cells(satir3, 5) = Sheets("PEM2005").Cells(a, 4) + Sheets("PEM2005").Cells(a, 3)
        Sheets("KES2006").Cells(satir3, 9) = Sheets("PEM2005").Cells(a, 34)
        Sheets("KES2006").Cells(satir3, 10) = Sheets("PEM2005").Cells(a, 35)
        Sheets("KES2006").Cells(satir3, 11) = Sheets("PEM2005").Cells(a, 36)
        Sheets("KES2006").Cells(satir3, 12) = Sheets("PEM2005").Cells(a, 37)
        Sheets("KES2006").Cells(satir3, 13) = Sheets("PEM2005").Cells(a, 38)
        Sheets("KES2006").Cells(satir3, 14) = Sheets("PEM2005").Cells(a, 39)
        Sheets("KES2006").Cells(satir3, 15) = Sheets("PEM2005").Cells(a, 40)
        Sheets("KES2006").Cells(satir3, 16) = Sheets("PEM2005").Cells(a, 41)
        Sheets("KES2006").Cells(satir3, 17) = Sheets("PEM2005").Cells(a, 42)
        Sheets("KES2006").Cells(satir3, 18) = Sheets("PEM2005").Cells(a, 43)
        Sheets("KES2006").Cells(satir3, 19) = Sheets("PEM2005").Cells(a, 44)
        Sheets("KES2006").Cells(satir3, 20) = Sheets("PEM2005").Cells(a, 45)
        Sheets("KES2006").Cells(satir3, 22) = Sheets("PEM2005").Cells(a, 73)
 
 
        satir3 = satir3 + 6
Sheets("KES2006").Cells(satir4, 5) = Sheets("PEM2005").Cells(a, 115)
        Sheets("KES2006").Cells(satir4, 9) = Sheets("PEM2005").Cells(a, 47)
        Sheets("KES2006").Cells(satir4, 10) = Sheets("PEM2005").Cells(a, 48)
        Sheets("KES2006").Cells(satir4, 11) = Sheets("PEM2005").Cells(a, 49)
        Sheets("KES2006").Cells(satir4, 12) = Sheets("PEM2005").Cells(a, 50)
        Sheets("KES2006").Cells(satir4, 13) = Sheets("PEM2005").Cells(a, 51)
        Sheets("KES2006").Cells(satir4, 14) = Sheets("PEM2005").Cells(a, 52)
        Sheets("KES2006").Cells(satir4, 15) = Sheets("PEM2005").Cells(a, 53)
        Sheets("KES2006").Cells(satir4, 16) = Sheets("PEM2005").Cells(a, 54)
        Sheets("KES2006").Cells(satir4, 17) = Sheets("PEM2005").Cells(a, 55)
        Sheets("KES2006").Cells(satir4, 18) = Sheets("PEM2005").Cells(a, 56)
        Sheets("KES2006").Cells(satir4, 19) = Sheets("PEM2005").Cells(a, 57)
        Sheets("KES2006").Cells(satir4, 20) = Sheets("PEM2005").Cells(a, 58)
 
 
        satir4 = satir4 + 6
 
        Sheets("KES2006").Cells(satir5, 2) = Sheets("PEM2005").Cells(a, 112)
        Sheets("KES2006").Cells(satir5, 5) = Sheets("PEM2005").Cells(a, 117)
        Sheets("KES2006").Cells(satir5, 7) = Sheets("PEM2005").Cells(a, 116)
        Sheets("KES2006").Cells(satir5, 9) = Sheets("PEM2005").Cells(a, 60)
        Sheets("KES2006").Cells(satir5, 10) = Sheets("PEM2005").Cells(a, 61)
        Sheets("KES2006").Cells(satir5, 11) = Sheets("PEM2005").Cells(a, 62)
        Sheets("KES2006").Cells(satir5, 12) = Sheets("PEM2005").Cells(a, 63)
        Sheets("KES2006").Cells(satir5, 13) = Sheets("PEM2005").Cells(a, 64)
        Sheets("KES2006").Cells(satir5, 14) = Sheets("PEM2005").Cells(a, 65)
        Sheets("KES2006").Cells(satir5, 15) = Sheets("PEM2005").Cells(a, 66)
        Sheets("KES2006").Cells(satir5, 16) = Sheets("PEM2005").Cells(a, 67)
        Sheets("KES2006").Cells(satir5, 17) = Sheets("PEM2005").Cells(a, 68)
        Sheets("KES2006").Cells(satir5, 18) = Sheets("PEM2005").Cells(a, 69)
        Sheets("KES2006").Cells(satir5, 19) = Sheets("PEM2005").Cells(a, 70)
        Sheets("KES2006").Cells(satir5, 20) = Sheets("PEM2005").Cells(a, 71)
        Sheets("KES2006").Cells(satir5, 22) = Sheets("PEM2005").Cells(a, 109)
        Sheets("KES2006").Cells(satir5, 23) = Sheets("PEM2005").Cells(a, 110)
 
        satir5 = satir5 + 6
        Sheets("KES2006").Cells(satir6, 3) = Sheets("PEM2005").Cells(a, 6)
        Sheets("KES2006").Cells(satir6, 5) = Sheets("PEM2005").Cells(a, 7)
        Sheets("KES2006").Cells(satir6, 7) = Sheets("PEM2005").Cells(a, 111)
        Sheets("KES2006").Cells(satir6, 14) = Sheets("PEM2005").Cells(a, 75)
        Sheets("KES2006").Cells(satir6, 22) = Sheets("PEM2005").Cells(a, 74)
 
        satir6 = satir6 + 6
Next a
son:
 
 
 
    Range("V34") = sayfa
    sayfa = sayfa + 1
   ' ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
 
   ActiveWindow.SelectedSheets.PrintPreview 'PrintOut Copies:=1, Collate:=True
 
End Sub
 
Son düzenleme:
Cevap yazmak için giriş yap yada kayıt ol.
Üst