Düşeyara - Kaçıncı kombinasyonu VBA (başka sayfadan veri ile)

[adainsaat]

Arkadaşlar sorum ve örnek dosya linki aşağıdadır.

Örnek

Sayfa 2'de E4 hücresindeki fonksiyonu vba olarak oluşturmak istiyorum. (Bir commandbox ile E4 hücresine tanımlanacak)
Fakat bu kodu farklı dosyalarda kullanacağımdan ve her dosyanın sayfa 1'indeki satır sayısı değiştiğinden;

- Formüldeki 'Düşeyara' başvuru aralığı (Sayfa1!$A$3:$R$4418) her dosyanın son satırına göre değişecek şekilde dinamik olsun. (Yani başka bir dosyada 4418 değil 5000 satır veri olabilir.)
- Formüldeki 'Kaçıncı' başvuru aralığı ise sabit kalabilir. (Sayfa1!$A$2:$R$2)

Yardımcı olabilecek arkadaşlara şimdiden teşekkürler...

 

[ÖmerFaruk]

Formülü aşağıdaki gibi kullanabilirsiniz. Örnek veriniz tek satır olduğundan ötürü mecburen tek satırda denedim. Siz kendi dosylarınızda deneyebilirsiniz.
Formül Kaçıncı fonksiyonunda bulamaz ya da farklı bir hata ile karşılaşırsa sonucu Boş olarak döndürü ( Boş=""). Arzu ederseniz boşluk yerine farklı bir ifade de verebilirsiniz.

Kısıtlarınız
Sayfa1 ismi aynı olacak
İkinci satırınız başlık ve verileriniz 3.satırdan başlayacak
En az bir satır veriniz olacak
Sütunlarınız A:R arasında olacak

=EĞERHATA(DÜŞEYARA(B4;KAYDIR(Sayfa1!$A$3;;;BAĞ_DEĞ_DOLU_SAY(Sayfa1!A:A)-3;18);KAÇINCI(D4;Sayfa1!$A$2:$R$2;0);YANLIŞ);"")

 

[adainsaat]

Formülü aşağıdaki gibi kullanabilirsiniz. Örnek veriniz tek satır olduğundan ötürü mecburen tek satırda denedim. Siz kendi dosylarınızda deneyebilirsiniz.
Formül Kaçıncı fonksiyonunda bulamaz ya da farklı bir hata ile karşılaşırsa sonucu Boş olarak döndürü ( Boş=""). Arzu ederseniz boşluk yerine farklı bir ifade de verebilirsiniz.

Kısıtlarınız
Sayfa1 ismi aynı olacak
İkinci satırınız başlık ve verileriniz 3.satırdan başlayacak
En az bir satır veriniz olacak
Sütunlarınız A:R arasında olacak

=EĞERHATA(DÜŞEYARA(B4;KAYDIR(Sayfa1!$A$3;;;BAĞ_DEĞ_DOLU_SAY(Sayfa1!A:A)-3;18);KAÇINCI(D4;Sayfa1!$A$2:$R$2;0);YANLIŞ);"")

Hocam elinize sağlık.. ben zaten fonksiyonla çözüm buldum fakat VBA ile çözümü merak ettim.

Düşeyara, Kaçıncı ve dinamik başvuru aralığını bir arada kullanmayı öğrenmek istiyorum.

 

[ÖmerFaruk]

Merhaba tekrar. Buldum dediğiniz fonksiyon satır sayısı değiştikçe kasar ve hatta hatalı üretir.

Şöyle bir isteğiniz var.
Bir commandbox ile E4 hücresine tanımlanacak

Command box henüz icat edilmedi ama sanırım siz Buton istiyorsunuz. Doğru mudur?
Peki bu butona basınca tek bir satırdaki yani D4 hücresindeki değer için E4 hücresine sonuç mu bulacak?
Yoksa sorunun devamında bu satır sayısı artacak mı?

 

[adainsaat]

Merhaba tekrar. Buldum dediğiniz fonksiyon satır sayısı değiştikçe kasar ve hatta hatalı üretir.

Şöyle bir isteğiniz var.
Bir commandbox ile E4 hücresine tanımlanacak

Command box henüz icat edilmedi ama sanırım siz Buton istiyorsunuz. Doğru mudur?
Peki bu butona basınca tek bir satırdaki yani D4 hücresindeki değer için E4 hücresine sonuç mu bulacak?
Yoksa sorunun devamında bu satır sayısı artacak mı?

Hocam pardon command button olacaktı

Aslında Sayfa1 A sütununda yer alan her bir veriye karşılık gelen yine Sayfa 1'de bazı sütunlardan veriyi Sayfa 2'ye taşımak istiyorum. Ama sizden sadece E4 hücresi için gerekli kodu öğrenebilirsem kalanlarını ben yapacağım.

Yani;
Sayfa 2, E4 hücresinde; D4 te yer alan verinin Sayfa1'de olduğu sütundaki veriyi çekiyorum (B4'teki veriyi Sayfa1'de 'düşeyara'tarak)
Fakat E5 için; D5'te yer alan başka bir verinin Sayfa1'de olduğu sütundaki veriyi çekeceğim
Böyle 10 farklı verim olacak (D413 aralığındaki verilere bağlı olarak), ama dediğim gibi 1 tanesini görmem ve öğrenmem yeterli olacak.

 

[ÖmerFaruk]

Deneyin lütfen.

Sub MakroylaBul()
    Dim Bul As Range, AranacakAlan As Range
    
    son = Worksheets("Sayfa1").Range("A" & Rows.Count).End(3).Row
    Set AranacakAlan = Worksheets("Sayfa1").Range("A3:R" & son)
    
    For i = 4 To 13 'Eğer satır sayınız 13den fazla ise değiştirebilirsiniz.
        Kolon = 0
        If Worksheets("Sayfa2").Range("B" & i) = "" Or Worksheets("Sayfa2").Range("D" & i) = "" Then GoTo SONAATLA
        Set Bul = AranacakAlan.Find(Range("B" & i).Value, , xlValues, xlWhole)
        If Not Bul Is Nothing Then
            On Error Resume Next
            Kolon = WorksheetFunction.Match(Worksheets("Sayfa2").Range("D" & i), Worksheets("Sayfa1").Range("A2:R2"), 0)
            If Kolon > 0 Then Worksheets("Sayfa2").Range("E" & i) = Bul.Offset(, Kolon - 1)
            On Error GoTo 0
        End If
    Next i
    
SONAATLA:
    Set Bul = Nothing
    Set AranacakAlan = Nothing
End Sub

 

[adainsaat]

Deneyin lütfen.

Sub MakroylaBul()
    Dim Bul As Range, AranacakAlan As Range
  
    son = Worksheets("Sayfa1").Range("A" & Rows.Count).End(3).Row
    Set AranacakAlan = Worksheets("Sayfa1").Range("A3:R" & son)
  
    For i = 4 To 13 'Eğer satır sayınız 13den fazla ise değiştirebilirsiniz.
        Kolon = 0
        If Worksheets("Sayfa2").Range("B" & i) = "" Or Worksheets("Sayfa2").Range("D" & i) = "" Then GoTo SONAATLA
        Set Bul = AranacakAlan.Find(Range("B" & i).Value, , xlValues, xlWhole)
        If Not Bul Is Nothing Then
            On Error Resume Next
            Kolon = WorksheetFunction.Match(Worksheets("Sayfa2").Range("D" & i), Worksheets("Sayfa1").Range("A2:R2"), 0)
            If Kolon > 0 Then Worksheets("Sayfa2").Range("E" & i) = Bul.Offset(, Kolon - 1)
            On Error GoTo 0
        End If
    Next i
  
SONAATLA:
    Set Bul = Nothing
    Set AranacakAlan = Nothing
End Sub

Tamamdır hocam elinize sağlık.. Çok teşekkür ederim..