Sumif kodunda sorun.

[talebeyim]

Aşağıdaki kodu bir sayfadan diğer bir sayfaya veri almak icin kullanıyorum,
Aynı ve farklı sayfalarda birkaç defa kullanıp, tek commandbutton'a atadığımda çokçok yavaş çalışıyor.

Yapabileceğim alternatif önerileriniz varmıdır ?

Not: Vba'da bilgi seviyem henüz başlangıç seviyesidir.

Private Sub CommandButton1_Click()

Set S1 = Sheets("ha")
Set S2 = Sheets("1a")
sons1 = S1.[a65536].End(3).Row
sons2 = S2.[a65536].End(3).Row
For j = 1 To sons2
S2.Range("R" & j) = WorksheetFunction.SumIf(S1.Range("A1:A" & sons1), S2.Range("A" & j), S1.Range("D1:D" & sons1))
Next
End Sub

 

[Korhan Ayhan]

Merhaba,

Birden fazla hücreye formülle veri getirme yönteminde döngüler çok satırlı alanlarda yavaşlamaya sebep olabilir. Bunun yerine "With-End With" kod bloğu ile hücrelerin tamamına tek seferde formülü uygulayıp değere çevirirseniz hız kazanabilirsiniz.

Örnek kod;

Private Sub CommandButton1_Click()
    Dim S1, S2, Son_Satir
 
    Set S1 = Sheets("ha")
    Set S2 = Sheets("1a")
 
    Son_Satir = S2.Cells(Rows.Count, "A").End(3).Row
 
    Application.ScreenUpdating = False
 
    With S2.Range("R1:R" & Son_Satir)
        .Formula = "=SUMIF(" & S1.Name & "!A:A" & ",A1," & S1.Name & "!D:D" & ")"
        .Value = .Value
    End With
 
    Application.ScreenUpdating = True
End Sub

 

[talebeyim]

Merhaba,

Birden fazla hücreye formülle veri getirme yönteminde döngüler çok satırlı alanlarda yavaşlamaya sebep olabilir. Bunun yerine "With-End With" kod bloğu ile hücrelerin tamamına tek seferde formülü uygulayıp değere çevirirseniz hız kazanabilirsiniz.

Örnek kod;

Private Sub CommandButton1_Click()
    Dim S1, S2, Son_Satir
 
    Set S1 = Sheets("ha")
    Set S2 = Sheets("1a")
 
    Son_Satir = S2.Cells(Rows.Count, "A").End(3).Row
 
    Application.ScreenUpdating = False
 
    With S2.Range("R1:R" & Son_Satir)
        .Formula = "=SUMIF(" & S1.Name & "!A:A" & ",A1," & S1.Name & "!D:D" & ")"
        .Value = .Value
    End With
 
    Application.ScreenUpdating = True
End Sub

Yardımınız için teşekkür ederim Korhan bey, Notumdada belirttiğim gibi, henüz VBA'da başlangıç seviyesindeyim, o yüzden aşağıda verdiğim örnek gibi kodların yanına notlar alıyorum, Sizin verdiğiniz kod bloğundada bu şekilde yardımcı olabilmeniz mümkünmüdür.?

Birde ayrıca mesela aldığım veriyi "A" sütununa yazacağım, Tüme değilde A5'den başlamasını istesem ne yaparım. ve metin getirmek icinde bu kodu kullanabilirmiyim (füşeyara yerine)
Teşekkürederim.

Private Sub CommandButton1_Click()

Set S1 = Sheets("a") 'Kaynak sayfa
Set S2 = Sheets("b") 'Hedef sayfa
sons1 = S1.[a65536].End(3).Row 'Kaynak atama
sons2 = S2.[a65536].End(3).Row 'Hedef atama
For j = 1 To sons2
S2.Range("R" & j)[Hedefin neresine yazsın  ] = WorksheetFunction.SumIf(S1.Range("A1:A" & sons1)[Kaynağın neresine baksın], S2.Range("A" & j)[Hedefin neresine baksın], S1.Range("D1:D" & sons1))[Kaynağın neresinden alsın]
                           

Next
End Sub

 

[talebeyim]

Bu konu katılıp başka öneride bulunmak isteyen varmı?

 

[Korhan Ayhan]

Merhaba,

Önerdiğim yöntemde hücreye uygulanan formülün makro ile hücreye yazdırılıp değere çevirilmesi sağlanmıştır. Tüm fonksiyonları bu şekilde değere çevirebilirsiniz.

Kodun açıklaması;

Private Sub CommandButton1_Click()

Prosedür başlangıcı

Dim S1, S2, Son_Satir

Değişkenleri tanımlıyoruz.

Set S1 = Sheets("ha")
Set S2 = Sheets("1a")

Sayfa isimlerini kısaltarak hafızaya alıyoruz.

Son_Satir = S2.Cells(Rows.Count, "A").End(3).Row

Son dolu satırın satır numarasını tesbit ediyoruz.

Application.ScreenUpdating = False

Ekran hareklerini pasif hale getiriyoruz. Genel olarak kullanılır. Kodların biraz daha hızlı çalışmasını sağlar.

With S2.Range("R1:R" & Son_Satir)

With bloğuna başlıyoruz. Burada formülün uygulanacağı hücre aralığını tanımlıyoruz.

.Formula = "=SUMIF(" & S1.Name & "!A:A" & ",A1," & S1.Name & "!D" & ")"

Belirlenen hücre aralığına formülü yazdırıyoruz.

.Value = .Value

Uygulanan formülleri değere çeviriyoruz.

End With

With bloğunu sonlandırıyoruz.

Application.ScreenUpdating = True

Ekran hareklerini tekrar aktif hale getiriyoruz. Genel olarak kullanılır. Kodların biraz daha hızlı çalışmasını sağlar.

End Sub

Prosedürü sonlandırıyoruz.


Formülü uygulayacağınız hücreyi aşağıdaki satıra müdahale ederek değiştirebilirsiniz.

With S2.Range("R1:R" & Son_Satir)

Formüldeki hücre aralığını değiştirmek isterseniz aşağıdaki satırı düzenleyebilirsiniz.

.Formula = "=SUMIF(" & S1.Name & "!A5:A1000" & ",A1," & S1.Name & "!D51000" & ")"

 

[talebeyim]

Korhan bey çok teşekkür ederim, bu kadar açıklayıcı bir cevap verebileceğinizi beklemiyordum.

Çok sağolun.

 

[talebeyim]

Private Sub CommandButton1_Click()
    Dim S1, S2, Son_Satir
 
    Set S1 = Sheets("ha")
    Set S2 = Sheets("1a")
 
    Son_Satir = S2.Cells(Rows.Count, "A").End(3).Row
 
    Application.ScreenUpdating = False
 
    With S2.Range("R1:R" & Son_Satir)
        .Formula = "=SUMIF(" & S1.Name & "!A:A" & ",A1," & S1.Name & "!D:D" & ")"
        .Value = .Value
    End With
 
    Application.ScreenUpdating = True
End Sub

Getirdiğim değeri metin olmasını istiyorum bunu nasıl yapabilirim ?

 

[Korhan Ayhan]

Merhaba,

Formüllerinizi makroya çevirmenin en kolay yolu;

Hücreye formülünüzü yazın. Daha sonra makro kaydet olayını çalıştırın.
Formüllü hücreyi seçip F2-ENTER yapın. Son olarak makro kaydını durdurun. Kod editörüne geçip oluşan kodu inceleyin. Biraz üzerinde çalışın beceremezseniz yardımcı olurum.

 

[talebeyim]

İnşallah korhan bey dediğiniz gibi yaptım Ama benimle alakası olmayan satırlar çıktı, yukarıdaki kot 7 satırsa dediğin yöntemde çıkan sadece bir satır.
Neyse dediğiniz gibi akşam evde bir devam edeyim Debugta Watch seçenğiyle adım adım bir gidiyim umarım yaparım.
Teşekkür ederim.

 

[talebeyim]

Yapamadım verdiğiniz kodu, Text verisi getirecek şekilde uyarlayamadım

 

[Korhan Ayhan]

Merhaba,

Aşağıdaki kod yapısını kullanabilirsiniz.

Daha anlaşılır olması için örnek dosyada ekliyorum.

Private Sub CommandButton1_Click()
    Dim S1, S2, Son_Satir
 
    Set S1 = Sheets("Sayfa1")
    Set S2 = Sheets("Sayfa2")
 
    Son_Satir = S1.Cells(Rows.Count, "A").End(3).Row
 
    Application.ScreenUpdating = False
 
    With S1.Range("B2:B" & Son_Satir)
        .Formula = "=VLOOKUP(A2," & S2.Name & "!A:B,2,0)"
        .Value = .Value
    End With
 
    Application.ScreenUpdating = True
End Sub

 

[talebeyim]

Tekrar teşekkür ederim, Ayrıca ramazanınızıda tebrik ederim.