Excel Forum
ALTIN ÜYELİK Hakkında Bilgi


Geri Git   Excel Forum > EXCEL-Soruları > Makro-VBA
Atatürk
Şifremi Unuttum

DUYURU SİSTEMİ / REKLAM PANOSU

Makro-VBA Makro veya VBA ile ilgili sorularınızı buraya gönderebilirsiniz.
Dosya ekleyebilirsiniz

Özel Arama


Yanıtla
 
Paylaş Konu Araçları Görünüm Modları
Eski 02-02-2017, 17:05   #1
SATYON
 
SATYON kullanıcısının avatarı
 
Giriş: 05/05/2006
Şehir: İzmir
Mesaj: 84
Excel Vers. ve Dili:
Microsoft Office Excel 2013 / İngilizce
Varsayılan Compile Error : Procedure Too Large

Merhaba, 17 worksheet içeren bir dosyam var. aşağıdaki kodu ve başka birkaç kodu daha dosyamdaki 15 worksheet için ayrı ayrı yazdım ve şimdi procedure too large hatası alıyorum. bu kodu workbooktaki 17 sayfanın yalnızca 15 için çalışacak şekilde sadece 1 kere yazarak nasıl uygulayabilirim.


Private Sub CommandButton1_Click()
a = 5
Sheet5.Cells(1, 1) = "PAZARLAMA-1"
Sheet5.Cells(2, 1) = "Genel Toplam"
Sheet5.Cells(4, 1) = "CARİ İL"
Sheet5.Cells(4, 2) = "CARİ İLÇE"
Sheet5.Cells(4, 3) = "PLASIYER"
Sheet5.Cells(4, 4) = "SATIŞ DESTEK"
Sheet5.Cells(4, 5) = "2007 TL"
Sheet5.Cells(4, 6) = "2007 USD"
Sheet5.Cells(4, 7) = "2007 EURO"
Sheet5.Cells(4, 8) = "2007-2008 Büyüme Oranı"
Sheet5.Cells(4, 9) = "2008 TL"
Sheet5.Cells(4, 10) = "2008 USD"
Sheet5.Cells(4, 11) = "2008 EURO"
Sheet5.Cells(4, 12) = "2008-2009 Büyüme Oranı"
Sheet5.Cells(4, 13) = "2009 TL"
Sheet5.Cells(4, 14) = "2009 USD"
Sheet5.Cells(4, 15) = "2009 EURO"
Sheet5.Cells(4, 16) = "2009-2010 Büyüme Oranı"
Sheet5.Cells(4, 17) = "2010 TL"
Sheet5.Cells(4, 18) = "2010 USD"
Sheet5.Cells(4, 19) = "2010 EURO"
Sheet5.Cells(4, 20) = "2010-2011 Büyüme Oranı"
Sheet5.Cells(4, 21) = "2011 TL"
Sheet5.Cells(4, 22) = "2011 USD"
Sheet5.Cells(4, 23) = "2011 EURO"
Sheet5.Cells(4, 24) = "2011-2012 Büyüme Oranı"
Sheet5.Cells(4, 25) = "2012 TL"
Sheet5.Cells(4, 26) = "2012 USD"
Sheet5.Cells(4, 27) = "2012 EURO"
Sheet5.Cells(4, 28) = "2012-2013 Büyüme Oranı"
Sheet5.Cells(4, 29) = "2013 TL"
Sheet5.Cells(4, 30) = "2013 USD"
Sheet5.Cells(4, 31) = "2013 EURO"
Sheet5.Cells(4, 32) = "2013-2014 Büyüme Oranı"
Sheet5.Cells(4, 33) = "2014 TL"
Sheet5.Cells(4, 34) = "2014 USD"
Sheet5.Cells(4, 35) = "2014 EURO"
Sheet5.Cells(4, 36) = "2014-2015 Büyüme Oranı"
Sheet5.Cells(4, 37) = "2015 TL"
Sheet5.Cells(4, 38) = "2015 USD"
Sheet5.Cells(4, 39) = "2015 EURO"
Sheet5.Cells(4, 40) = "2015-2016 Büyüme Oranı"
Sheet5.Cells(4, 41) = "2016 TL"
Sheet5.Cells(4, 42) = "2016 USD"
Sheet5.Cells(4, 43) = "2016 EURO"
Sheet5.Cells(4, 44) = "2016-2017 Büyüme Oranı"
Sheet5.Cells(4, 45) = "2017 TL"
Sheet5.Cells(4, 46) = "2017 USD"
Sheet5.Cells(4, 47) = "2017 EURO"
Sheet5.Cells(4, 48) = "2017 TL PROJEKSİYON"
Sheet5.Cells(4, 49) = "2017 USD PROJEKSİYON"
Sheet5.Cells(4, 50) = "2017 EURO PROJEKSİYON"
Sheet5.Cells(2, 5) = "=sum(E5:E1000)"
Sheet5.Cells(2, 6) = "=sum(F5:F1000)"
Sheet5.Cells(2, 7) = "=sum(G5:G1000)"
Sheet5.Cells(2, 8) = "=IFERROR(J2/F2,0)"
Sheet5.Cells(2, 9) = "=sum(I5:I1000)"
Sheet5.Cells(2, 10) = "=sum(J5:J1000)"
Sheet5.Cells(2, 11) = "=sum(K5:K1000)"
Sheet5.Cells(2, 12) = "=IFERROR(N2/J2,0)"
Sheet5.Cells(2, 13) = "=sum(M5:M1000)"
Sheet5.Cells(2, 14) = "=sum(N5:N1000)"
Sheet5.Cells(2, 15) = "=sum(O5:O1000)"
Sheet5.Cells(2, 16) = "=IFERROR(R2/N2,0)"
Sheet5.Cells(2, 17) = "=sum(Q5:Q1000)"
Sheet5.Cells(2, 18) = "=sum(R5:R1000)"
Sheet5.Cells(2, 19) = "=sum(S5:S1000)"
Sheet5.Cells(2, 20) = "=IFERROR(V2/R2,0)"
Sheet5.Cells(2, 21) = "=sum(U5:U1000)"
Sheet5.Cells(2, 22) = "=sum(V5:V1000)"
Sheet5.Cells(2, 23) = "=sum(W5:W1000)"
Sheet5.Cells(2, 24) = "=IFERROR(Z2/V2,0)"
Sheet5.Cells(2, 25) = "=sum(Y5:Y1000)"
Sheet5.Cells(2, 26) = "=sum(Z5:Z1000)"
Sheet5.Cells(2, 27) = "=sum(AA5:AA1000)"
Sheet5.Cells(2, 28) = "=IFERROR(AD2/Z2,0)"
Sheet5.Cells(2, 29) = "=sum(AC5:AC1000)"
Sheet5.Cells(2, 30) = "=sum(AD5:AD1000)"
Sheet5.Cells(2, 31) = "=sum(AE5:AE1000)"
Sheet5.Cells(2, 32) = "=IFERROR(AH2/AD2,0)"
Sheet5.Cells(2, 33) = "=sum(AG5:AG1000)"
Sheet5.Cells(2, 34) = "=sum(AH5:AH1000)"
Sheet5.Cells(2, 35) = "=sum(AI5:AI1000)"
Sheet5.Cells(2, 36) = "=IFERROR(AL2/AH2,0)"
Sheet5.Cells(2, 37) = "=sum(AK5:AK1000)"
Sheet5.Cells(2, 38) = "=sum(AL5:AL1000)"
Sheet5.Cells(2, 39) = "=sum(AM5:AM1000)"
Sheet5.Cells(2, 40) = "=IFERROR(AP2/AL2,0)"
Sheet5.Cells(2, 41) = "=sum(AO5:AO1000)"
Sheet5.Cells(2, 42) = "=sum(AP5:AP1000)"
Sheet5.Cells(2, 43) = "=sum(AQ5:AQ1000)"
Sheet5.Cells(2, 44) = "=IFERROR(AW2/AP2,0)"
Sheet5.Cells(2, 45) = "=sum(AS5:AS1000)"
Sheet5.Cells(2, 46) = "=sum(AT5:AT1000)"
Sheet5.Cells(2, 47) = "=sum(AU5:AU1000)"
Sheet5.Cells(2, 48) = "=IFERROR((AS2/(TODAY()-DATE(2016,12,31)))*365,0)"
Sheet5.Cells(2, 49) = "=IFERROR((AT2/(TODAY()-DATE(2016,12,31)))*365,0)"
Sheet5.Cells(2, 50) = "=IFERROR((AU2/(TODAY()-DATE(2016,12,31)))*365,0)"
Sheet5.Range("E4:G4").Interior.Color = RGB(255, 153, 153)
Sheet5.Range("I4:K4").Interior.Color = RGB(255, 204, 153)
Sheet5.Range("M4:O4").Interior.Color = RGB(255, 255, 153)
Sheet5.Range("Q4:S4").Interior.Color = RGB(204, 255, 153)
Sheet5.Range("U4:W4").Interior.Color = RGB(153, 255, 153)
Sheet5.Range("Y4:AA4").Interior.Color = RGB(153, 255, 204)
Sheet5.Range("AC4:AE4").Interior.Color = RGB(153, 255, 255)
Sheet5.Range("AG4:AI4").Interior.Color = RGB(153, 204, 255)
Sheet5.Range("AK4:AM4").Interior.Color = RGB(153, 153, 255)
Sheet5.Range("AO4:AQ4").Interior.Color = RGB(204, 153, 255)
Sheet5.Range("AS4:AU4").Interior.Color = RGB(255, 153, 255)
Sheet5.Range("AV4:AX4").Interior.Color = RGB(255, 153, 204)
Sheet5.Range("A4:AO500").Borders.LineStyle = xlDash
Sheet5.Range("A4:AO500").Borders.Color = RGB(153, 204, 255)
Sheet5.Rows(1).Font.Bold = True
Sheet5.Rows(2).Font.Bold = True
Sheet5.Rows(4).Font.Bold = True
Sheet5.Rows(2).HorizontalAlignment = xlCenter
Sheet5.Rows(4).HorizontalAlignment = xlCenter
Sheet5.Rows(4).WrapText = True
Sheet5.Range("A:XFD").VerticalAlignment = xlCenter
Sheet5.Range("E:G").NumberFormat = "#,##0.00"
Sheet5.Range("H:H").NumberFormat = "%#,##0.00"
Sheet5.Range("I:K").NumberFormat = "#,##0.00"
Sheet5.Range("L:L").NumberFormat = "%#,##0.00"
Sheet5.Range("M:O").NumberFormat = "#,##0.00"
Sheet5.Range("P:P").NumberFormat = "%#,##0.00"
Sheet5.Range("Q:S").NumberFormat = "#,##0.00"
Sheet5.Range("T:T").NumberFormat = "%#,##0.00"
Sheet5.Range("U:W").NumberFormat = "#,##0.00"
Sheet5.Range("X:X").NumberFormat = "%#,##0.00"
Sheet5.Range("Y:AA").NumberFormat = "#,##0.00"
Sheet5.Range("AB:AB").NumberFormat = "%#,##0.00"
Sheet5.Range("AC:AE").NumberFormat = "#,##0.00"
Sheet5.Range("AF:AF").NumberFormat = "%#,##0.00"
Sheet5.Range("AG:AI").NumberFormat = "#,##0.00"
Sheet5.Range("AJ:AJ").NumberFormat = "%#,##0.00"
Sheet5.Range("AK:AM").NumberFormat = "#,##0.00"
Sheet5.Range("AN:AN").NumberFormat = "%#,##0.00"
Sheet5.Range("AO:AQ").NumberFormat = "#,##0.00"
Sheet5.Range("AR:AR").NumberFormat = "%#,##0.00"
Sheet5.Range("AS:AX").NumberFormat = "#,##0.00"
Sheet5.Rows(4).HorizontalAlignment = xlCenter

sqlcumlepaz1 = " select * from VW_AKSELDOVIZEXFULL_OZET WHERE PLASIYER='HO' ORDER BY CARI_IL "
Set Sirket = Kernel.yeniSirket(vtMSSQL, Sheet1.Cells(3, 2), "TEMELSET", "", TextBox1.Text, TextBox2.Text, 0)

Set paz1 = Kernel.yeniNetRS(Sirket)
paz1.Ac (sqlcumlepaz1)

While Not paz1.EOF

Sheet5.Cells(a, 1) = paz1.Fields(1).AsString
Sheet5.Cells(a, 2) = paz1.Fields(2).AsString
Sheet5.Cells(a, 3) = paz1.Fields(3).AsString
Sheet5.Cells(a, 4) = paz1.Fields(4).AsString
Sheet5.Cells(a, 5).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 5) = paz1.Fields(5).AsFloat
Sheet5.Cells(a, 6).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 6) = paz1.Fields(6).AsFloat
Sheet5.Cells(a, 7).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 7) = paz1.Fields(7).AsFloat
Sheet5.Cells(a, 9).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 9) = paz1.Fields(8).AsFloat
Sheet5.Cells(a, 10).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 10) = paz1.Fields(9).AsFloat
Sheet5.Cells(a, 11).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 11) = paz1.Fields(10).AsFloat
Sheet5.Cells(a, 13).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 13) = paz1.Fields(11).AsFloat
Sheet5.Cells(a, 14).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 14) = paz1.Fields(12).AsFloat
Sheet5.Cells(a, 15).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 15) = paz1.Fields(13).AsFloat
Sheet5.Cells(a, 17).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 17) = paz1.Fields(14).AsFloat
Sheet5.Cells(a, 18).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 18) = paz1.Fields(15).AsFloat
Sheet5.Cells(a, 19).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 19) = paz1.Fields(16).AsFloat
Sheet5.Cells(a, 21).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 21) = paz1.Fields(17).AsFloat
Sheet5.Cells(a, 22).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 22) = paz1.Fields(18).AsFloat
Sheet5.Cells(a, 23).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 23) = paz1.Fields(19).AsFloat
Sheet5.Cells(a, 25).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 25) = paz1.Fields(20).AsFloat
Sheet5.Cells(a, 26).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 26) = paz1.Fields(21).AsFloat
Sheet5.Cells(a, 27).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 27) = paz1.Fields(22).AsFloat
Sheet5.Cells(a, 29).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 29) = paz1.Fields(23).AsFloat
Sheet5.Cells(a, 30).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 30) = paz1.Fields(24).AsFloat
Sheet5.Cells(a, 31).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 31) = paz1.Fields(25).AsFloat
Sheet5.Cells(a, 33).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 33) = paz1.Fields(26).AsFloat
Sheet5.Cells(a, 34).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 34) = paz1.Fields(27).AsFloat
Sheet5.Cells(a, 35).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 35) = paz1.Fields(28).AsFloat
Sheet5.Cells(a, 37).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 37) = paz1.Fields(29).AsFloat
Sheet5.Cells(a, 38).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 38) = paz1.Fields(30).AsFloat
Sheet5.Cells(a, 39).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 39) = paz1.Fields(31).AsFloat
Sheet5.Cells(a, 41).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 41) = paz1.Fields(32).AsFloat
Sheet5.Cells(a, 42).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 42) = paz1.Fields(33).AsFloat
Sheet5.Cells(a, 43).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 43) = paz1.Fields(34).AsFloat
Sheet5.Cells(a, 45).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 45) = paz1.Fields(35).AsFloat
Sheet5.Cells(a, 46).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 46) = paz1.Fields(36).AsFloat
Sheet5.Cells(a, 47).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 47) = paz1.Fields(37).AsFloat
Sheet5.Cells(a, 48).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 49).NumberFormat = "#,##0.00"
Sheet5.Cells(a, 50).NumberFormat = "#,##0.00"

paz1.SonrakiK
a = a + 1
Wend
SATYON Çevrimdışı   Alıntı Yaparak Cevapla
Eski 04-02-2017, 09:53   #2
SATYON
 
SATYON kullanıcısının avatarı
 
Giriş: 05/05/2006
Şehir: İzmir
Mesaj: 84
Excel Vers. ve Dili:
Microsoft Office Excel 2013 / İngilizce
Varsayılan

Merhaba bana bu konu ile ilgili yardımcı olabilecek kimse yok mu acaba?
SATYON Çevrimdışı   Alıntı Yaparak Cevapla
Yanıtla


Konu Araçları
Görünüm Modları

Gönderme Kuralları
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is Açık
SimgelerAçık
[IMG] kodu Açık
HTML kodu Kapalı


Saat 11:32


Bu forum Elit NET - www.elitnet.com.tr tarafından sunulmaktadır.

Excel Eğitimi - Mobil Uygulama - Çorlu - Çorlu Web Tasarım - Tarot Falı - invest in turkey - Lingerie - Dyeing Machine - Karton Bardak- Çorlu Özel Eğitim- Site Yönetimi- Led Aydınlatma- Pronet Tekirdağ- Çorlu Kamera- Pronet Edirne- Pronet Kırklareli- Pronet Çerkezköy- Pronet Çorlu- Pronet Lüleburgaz- Pronet Keşan- Çorlu Araç Takip- Çorlu Su Arıtma- Boru Profil- Gebze Emlak- Beylikdüzü Temizlik- İstanbul Burun Estetiği- Bakır Sülfat- Rampa- Rotary- Çorlu İnternet Sitesi- youngblood- Çorlu Palet- Çerkezköy Palet- Çorlu Prefabrik- Çorlu Sürücü Kursu- Çorlu Sandviç Panel- Şişli Avukat- Korona Test Kalemi- Çorlu Vinç- Çorlu Pimapen Tamiri-
Powered by vBulletin Version 3.7.2
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Advertisement System V2.6 By   Branden