Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
924to928
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
924to928
924to928
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
SVerweis mit VBA
19.11.2007 20:15:00
lobby007
Hallo Excel-Spezialisten,
ich habe 2 Funktionen:
Die erste macht einen Sverweis über mehrere Bereiche:
Function LJVerweis2B(sBegriff, Spalte, ParamArray C()) Dim i% For i = 0 To UBound(C()) If IsError(Application.VLookup(sBegriff, _ C(i), Spalte, 0)) = False Then LJVerweis2B = Application.VLookup(sBegriff, _ C(i), Spalte, 0) End If Next i End Function


und
die 2te fügt mehrere Fundstellen zusammen:


Function LJVERWEISt(strName As String, rngBereich As Range) As Variant
Dim rngAct As Range
Dim strErgebnis As String
Application.Volatile
For Each rngAct In rngBereich.Columns(1).Cells
If rngAct = strName Then
strErgebnis = strErgebnis & rngAct.Offset(0, 1)
End If
Next rngAct
LJVERWEISt = Left(strErgebnis, Len(strErgebnis))
End Function


Ich brauche aber jetzt eine Funktion die durch mehrere Bereiche geht und alle Fundstellen aneinanderreiht. Geht das?
Kleine Bonusfrage:
Die 2te Funktion sucht nur in der ersten Spalte - wie müßte es aussehen wenn es in allen Spalten suchen sollte?
Gruß lobby007

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SVerweis mit VBA
19.11.2007 21:10:00
ransi
HAllo
Versuchs mal so:
Dies ist dein Datenaufbau:
Tabelle2

 ABCDEFGHI
10M0G92U714BE   5SMXQ5, 345, KV1, 6HLF, W9Y
2O895SM345C49     
38AZ9BM2W7LVU     
45SMXQ5IA01P6     
5AQ9Q3M33E898     
6LDZSPL80FUGR     
7CQOAKHQ2G1RK     
826EYV99CBTIG     
984L5SMKV1YQY     
10MKEV4ZZTFB9HNE1860RG   
11380ZC364N4PL2QNFF5G6   
12YH7ST6OS14SA5SM6HLF   
13    B14ENZ1J   
14    8D37Z2Y7   
15         
16         
17 54H64J      
18 9JO343      
19 5SMW9Y      
20 60GNG9      
21 CZXKZZ      
22 G9PDJ9      
23         

Formeln der Tabelle
ZelleFormel
I1=LJVERWEISt(H1;A1:D14;E10:F14;B17:C22)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Dazu gehört dieser code:
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Public Function LJVERWEISt(strName, ParamArray rngBereich()) As String
Dim I As Integer
Dim Z As Long
Dim S As Integer
Dim arr
Dim a
Dim Dummy
Redim Dummy(0)
For I = 0 To UBound(rngBereich)
    arr = rngBereich(I)
    For S = 1 To UBound(arr, 2) - 1
        For Z = 1 To UBound(arr, 1)
            If arr(Z, S) = strName Then
                Redim Preserve Dummy(a)
                Dummy(a) = arr(Z, S + 1)
                a = a + 1
            End If
        Next
    Next
Next
LJVERWEISt = Join(Dummy, ", ")
End Function

ransi

Anzeige
AW: SVerweis mit VBA
19.11.2007 21:30:00
lobby007
Hallo Ransi,
danke das klappt wie verrückt.
Kannst Du mir bei meinem Bonusproblem auch helfen?
Die Funktionen finden immer nur den ersten Verweis (beide Ljverweist), was müßte man ändern damit die Funktionen in allen Spalten suchen und das Ergebnis wiedergeben?
Vielen Dank und liebe Grüße
lobby007

?
19.11.2007 22:01:00
ransi
Hallo

Die Funktionen finden immer nur den ersten Verweis...


Das verstehe ich nicht.
Die Funktion die ich gepostet habe findet alle Übereinstimmungen und reiht die Zellen rechts neben den Fundstellen aneinander.
Poste doch mal eine Beispieltabelle aus der man erkennen kann was wo angezeigt werden soll.
ransi

Anzeige
AW: Verweis
19.11.2007 22:13:00
lobby007
Hallo Ransi,
siehe Anlage.
Die Funktionen arbeiten so wie ein Sverweis mit Spalte 1 als Wiedergabekriterium - ich brauche aber alle in der Zeile.
Siehe Anlage.
https://www.herber.de/bbs/user/47885.xls
Gruß lobby007

AW: Verweis
20.11.2007 00:02:00
Erich
Hallo ?,
Ransis Steilvorlage lässt sich (hoffentlich) auf deine Bedürfnisse so umstricken:
 ABCDEF
1HansAABBCC  
2FranzDDEEFF  
3AlbertGGHHII  
4LarsJJKKLL  
5HansMMNNOO  
6      
7 HansWWWZZZYYYNNN
8      
9      
10Hans AA, BB, CC, MM, NN, OO, WWW, ZZZ, YYY   

Formeln der Tabelle
ZelleFormel
C10=LJVERWEISt2(A10;A1:D6;B7:E7)

^mit der Funktion

Option Explicit
Public Function LJVERWEISt2(strName, ParamArray rngBereich()) As String
Dim I As Integer, Z As Long, S As Integer, arr, a, Dummy
ReDim Dummy(0)
For I = 0 To UBound(rngBereich)
arr = rngBereich(I)
For Z = 1 To UBound(arr, 1)
If arr(Z, 1) = strName Then
For S = 2 To UBound(arr, 2)
ReDim Preserve Dummy(a)
Dummy(a) = arr(Z, S)
a = a + 1
Next
End If
Next
Next
LJVERWEISt2 = Join(Dummy, ", ")
End Function

Kriterium ist immer die 1. Spalte des Bereichs,
bei einem Treffer sollen die 2. und die folgenden Spalten des Bereichs wiedergegeben werden..
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

Anzeige
AW: Verweis
21.11.2007 19:15:00
lobby007
Hallo Erich,
100% echt super vielen Dank.
Komme leider erst heute dazu das durchzugehen - war krank. Sorry
Vielen, Vielen Dank und noch einen schönen Tag.
Gruß lobby007

304 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige