Farklı sayfalardaki dolu satırların tarih sırasına göre listelenmesi

[Ergün Güler]

Selam,
3 farklı sayfadaki DOLU olan satırları, tarih sırasına göre başka bir sayfada listelemek istiyorum. Beceremedim, Fonksiyonlar ile Nasıl yapabilirim?

örnek dosyam ektedir.

Adları metinden oluşan 3 farklı sayfadaki B ve C sütunlarına ait DOLU olan satırları, tarih sırasına göre "liste" sayfasında sıralanmasını ve D sütunlarında ise sayfaların isimlerinin yazmasını istiyorum.

Yardımcı olabilirseniz çok sevinirim.
İyi çalışmalar.

 

[Korhan Ayhan]

Selamlar,

Benim fikrimi soracak olursanız bu işlem için en iyi yöntem makro kullanmaktır.

Option Explicit
 
Sub ÖZET_LİSTE()
    Dim SL As Worksheet, SAYFA As Worksheet, X As Long, SATIR As Long
    
    Set SL = Sheets("liste")
    
    SL.Range("A2:D65536").ClearContents
    SATIR = 2
    
    For Each SAYFA In ThisWorkbook.Worksheets
        If SAYFA.Name <> "liste" Then
            For X = 2 To SAYFA.Range("A65536").End(3).Row
                If SAYFA.Cells(X, 2) <> "" Or SAYFA.Cells(X, 3) <> "" Then
                    SL.Cells(SATIR, 1) = SAYFA.Cells(X, 1)
                    SL.Cells(SATIR, 2) = SAYFA.Cells(X, 2)
                    SL.Cells(SATIR, 3) = SAYFA.Cells(X, 3)
                    SL.Cells(SATIR, 4) = SAYFA.Name
                    SATIR = SATIR + 1
                End If
            Next
        End If
    Next
 
    SL.Select
    Range("A2:D65536").Sort Key1:=Range("A2"), Order1:=xlAscending
 
    Set SL = Nothing
    
    MsgBox "İşleminiz tamamlanmıştır.", vbInformation
End Sub

 

[Ergün Güler]

Selam Sayın Korhan Ayhan,
Süper bir çalışma ellerinize sağlık. Çok teşekkür ederim.
Asıl, fonksiyonlar ile merak ediyorum nasıl yapabiliriz?
Fonksiyonlar bayağı zor olacak biliyorum ancak, mantığını merak ediyorum.
İyi çalışmalar.

 

[Ergün Güler]

Selam Sayın Korhan Ayhan,

SL.Range("A2:D65536").ClearContents
    SATIR = 2

kısmı liste sayfasının bir önceki verilerin silineceği aralıktır doğru mudur?

SL.Select
    Range("A2:D65536").[COLOR="Red"]Sort Key1:=Range("A2"), Order1:=xlAscending[/COLOR]
 
    Set SL = [COLOR="red"]Nothing[/COLOR]

kısmı verilerin yazılmaya başlayacağı aralıktır. doğru mudur?
kırmızı alanlardan anlamıyorum. Mümkünse biraz izah edebilir misiniz?

bir sorum daha olacak sizin yazdığınız kod "liste" sayfası harici tüm sayfaları hesaplamaya dahil ediyor. A,B,C ve liste adlı sayfalar haricinde başka sayfalar olduğunu düşünürsek ve sadece A,B,C sayfalarını dahil etmeyi istersek kodda ne gibi değişiklikler yapmamamız gerekir?
şimdiden çok teşekkürler.

İyi çalışmalar.

 

[Korhan Ayhan]

Selamlar,

Evet tesbitleriniz doğrudur.

Kırmızı renkli ilk bölüm sıralama kod bloğudur. Makro kaydet yöntemi ile elde edilmiştir. Bu yöntemle arada gereksiz kodlar oluşmaktadır. İşimize yarayan kısım alınmıştır.

Sort (Sıralama ifadesidir.)
Key1:=Range("A2") (Sıralama anahtarını ifade eder. Sıralamanın hangi sütuna göre olmasını istiyorsak ilgili hücreyi yazıyoruz.)
Order1:=xlAscending (Sıralama yönteminin küçükten büyüğe olacağını belirtir.)

Sayfa isimleri birkaç farklı yolla kontrol edebilirsiniz. En kolayı döngü ile kontrol etmektir.

Size önerdiğim kod içindeki aşağıdaki satırı bir sonraki şekilde yazarsanız Or ifadesi ile dilediğiniz kadar sayfa adı kontrol edebilirsiniz.

If SAYFA.Name <> "liste" Then

[COLOR=blue]If SAYFA.Name = "A" [COLOR=red]Or[/COLOR] SAYFA.Name = "B" [COLOR=red]Or[/COLOR] SAYFA.Name = "C" Then[/COLOR]

Bunların dışında eğer sayfalarınızın yerleri sabitse yani sayfa sekmelerinizin yeri değişmeyecekse sayfa sıra nolarını (index) kullanarak işlem yapabilirsiniz.

A-B-C sayfaları (3 adet) ilk sırada, "liste" isimli sayfa son sırada ise aşağıdaki şekilde bir döngüde işinizi görebilir.

For X = 1 To [COLOR=red]3[/COLOR]
If Sheets(X).Range("A1") <> "" Then
.....
Next

Gördüğünüz gibi kodlar tamamen dosya yapısına göre şekillenmektedir. İhtiyacınıza hitab eden yöntemi tercih etmenizde her zaman fayda vardır.

Umarım açıklamalar sizin için faydalı olur.

 

[Ergün Güler]

Selam Sayın Korhan Ayhan,
Detaylı ve Net açıklamalarınız için çok çok teşekkür ederim. Çok sevindim ve memnun oldum. Gerçekten çok faydalı oldu benim için.
bir kaç şey daha sorabilir miyim?

Dim SL As Worksheet, SAYFA As Worksheet, [COLOR="Red"]X As Long, SATIR As Long[/COLOR]

kısmındaki SAYFA ve SL bir sayfadır diye tanımlanıyor. peki kırmızı renkli kısımdaki Long uzunluk anlamında ancak X ve SATIR için ne ifade ediyor? Mesela neden ROW denmiyor?

Set SL = [COLOR="Red"]Nothing[/COLOR]

ne anlama geliyor?

Örnek sorumu FONKSİYONLAR altında yeniden soracağım. Sakın yanlış anlaşılmasın, Sizin çözümlerinizden gayet tatmin oldum. Sadece fonksiyonlar ile çözüm prensibini öğrenmek için.

Benzer soruyu forumda bir arkadaş daha sormuştu, çözüm bulamamış. adı "Modestore" olabilir. Tam hatırlamıyorum. sizin kodlar ile muhtemelen çözülebilir. Sayenizde bu arkadaşa da yardımcı olabilirim inşallah.

Herşeyin için çok teşekkürler.

 

[Korhan Ayhan]

Selamlar,

LONG değişken tipidir. "X" döngüsü ile ilgili sayfadaki tüm kayıtlar kontrol ediliyor. Bildiğiniz üzere excelin 2003 ve altı versiyonlarında sayfadaki satır sayısı 65536 dır. İşte bu satırların tümüne yakını dolu olursa ve bu kayıtların tamamını döngüye almak istersek (byte,integer,long gibi) değişken tanımlamaları kullanılmaktadır. Bu tanımlamaların herbirinin alabileceği üst ve alt sınırlar vardır. Bu tanımlamalarla ilgili VBA yardım menüsünden bilgi alabilirsiniz.

Mesela;

Byte - Hafızada 8 bit yer kaplar ve 0 ile 255 arası sayısal sınırı destekler. Aşağıdaki kodu çalıştırırsanız hata verecektir. Çünkü X değişkenini Byte olarak tanımladık. Fakat döngü bitiş değerini 300 olarak aldık.

300>255 olduğu için döngü hata ile sonuçlanacaktır.

Sub Test ()
Dim X As Byte
 
For X = 1 To [COLOR=red]300[/COLOR]
'....
Next
End Sub

Integer - Hafızada 16 bit yer kaplar ve -32,768 ile 32,767 arası sayısal değerleri destekler.


Long - Hafızada 32 bit yer kaplar ve -2,147,483,648 ile 2,147,483,647 arası sayısal değerleri destekler.


Neden ROW ifadesini kullanmıyoruz. Çünkü ROW ifadesi satırı ifade eder. Fakat bu ifade tek başına kullanılmaz. Bir değer alabilmesi için Range yada Cells ile başlayan ifadelerle beraber kullanılması gerekmektedir. Fakat biz Dim ile değişken tanımlaması yaparken bize değişkenin alabileceği maksimum ve minimum sınırlar gereklidir. Bu sebeple tanımlama anında ROW ifadesi bir değer üretmeyeceği için kullanamıyoruz. Zaten tanımlama yaparken aşağıdaki gibi yazıp AS ifadesinden sonra boşluk verdiğinizde VBA size gerekli tanımlamaların (kırmızı bölüm) listesini otomatik olarak açacaktır. Siz bu listeden size uygun olanı ya yazarak yada mouse yardımı ile seçebilirsiniz.

DEĞİŞKEN ADI As Data Type


Aşağıdaki komutta hafızaya alınan SL tanımlamasını hafızadan silmek için kullanılır.

Set SL = Nothing

Aşağıdaki kodu boş bir excel kitabında kullanarak test edebilirsiniz. Kırmızı bölümden önce sayfa tanımalamasını hafızadan sildiğimiz için kod hata ile sonuçlanacaktır.

Sub Test ()
Dim S1 As Worksheet, S2 As Worksheet
 
Set S1 = Sheets("Sayfa1")
Set S2 = Sheets("Sayfa2")
 
S2.Select
S1.Select
 
Set S2 = Nothing
 
[COLOR=red]S2.Select[/COLOR]
 
End Sub

Sorunuza fonksiyonlarla çözüm aramanıza bir sözüm yok tabiki. Biraz karmaşık bir konu gibi görünüyor. Belki yardmıcı sütunlar kullanılarak çözüm üretilebilir. Yada ÖZET TABLO kullanılarak çözümler üretilebilir. Konu güzel bir beyin fırtınası olacak gibi görünüyor. Umarım sorunuza istediğiniz yönde cevap bulursunuz.

 

[Ergün Güler]

Sayın Korhan Ayhan,
Detaylı açıklamalarınız için ve epey bir zaman harcadığınız için çok çok teşekkür ederim. Geçekten size çok minnettarım.
Hayatınızın bütün safhasında başarılar diliyorum.
İyi çalışmalar.