Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1776to1780
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
Inhaltsverzeichnis

Funktion klappt nur im Direktbereich

Funktion klappt nur im Direktbereich
24.08.2020 07:46:35
Peter
Guten Tag
Mit meiner Funktion FindValue will ich in der Tabelle "Abfrage" Daten aus Tabelle "Quelle" ü _
bernehmen. Interessanterweise funktioniert das nur auf Zeile 6 (wo das erste Argument der _ Funktion 1 ist). Bei den Zeilen 7-9 (Argumente 2, 3, 4) klappt das nicht.

Function FindValue(iNr As Integer, strSheet As String, lngCol As Long, _
lngColOut As Long, strFind As String) As Variant
Dim lRow As Long, rngData As Range, oFinde As Object, sErsteAdresse As String, i As Long
With ThisWorkbook.Sheets(strSheet)
lRow = .Cells(.Rows.Count, lngCol).End(xlUp).Row
With .Range(.Cells(1, 5), .Cells(lRow, 5))
Set oFinde = .Find(strFind, LookIn:=xlValues, lookat:=xlWhole)
If Not oFinde Is Nothing Then
sErsteAdresse = oFinde.Address
Do
i = i + 1
With oFinde.Offset(0, lngColOut - lngCol)
If i = iNr Then FindValue = .Value: Exit Function
End With
Set oFinde = .FindNext(oFinde)
Loop While Not oFinde Is Nothing And oFinde.Address  sErsteAdresse
End If
End With
End With
End Function

Wenn ich allerdings die Funktion über einen

Sub via MsgBox aufrufe, erhalte ich überall die richtigen Ergebnisse (vgl. Modul 1 - Sub_Test).
Was muss am Code geändert werden, damit die Funktion auch in der Tabelle aufgerufen werden kann? _
_
_
Leider konnte ich die Beispieldatei nicht auf das Forum hochladen, obschon ich diese unter C:\   _
_
_
Beispiel.xlsm gespeichert habe.
Deshalb habe ich sie hier zur Verfügung gestellt.
https://1drv.ms/x/s! _
Akz9W19mecwNg5VigvreztlDCKAyYQ?e=rPJeKZ
Gruss, Peter



		

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Funktion klappt nur im Direktbereich
24.08.2020 07:49:03
Hajo_Zi
Hallo Peter,
warum nicht Dowload des Forums?
Deiner geht nicht.

AW: Funktion klappt nur im Direktbereich
24.08.2020 07:51:41
Peter
Hallo Hajo
Wie geschrieben - ich kriege die Datei nicht ins Forum - reklamiert immer falsches Dateiformat - ob ich die Datei als *.xlsx oder als *.xls abspeichere, direkt unter C:\
Ich habe den Link ausgetestet - bei mir funktioniert es.
Gruss, Peter
AW: Funktion klappt nur im Direktbereich
24.08.2020 07:54:48
Oberschlumpf
Hi,
ähh, wenn du einen Link zum Testen nutzen konntest, dann muss der Download ja funktioniert haben.
Zeig uns den Link doch mal.
Ciao
AW: Funktion klappt nur im Direktbereich
24.08.2020 07:57:38
Peter
Hi
Der Link ist ja im ersten Eintrag dieses Threads ganz unten
Gruss, Peter
Anzeige
AW: Funktion klappt nur im Direktbereich
24.08.2020 07:59:40
Hajo_Zi
Halo Peter,
benutze ZIP
Der Link im ersten Beitrag ist kein Link zum Forums Dowload.
Gruß Hajo
AW: Funktion klappt nur im Direktbereich
24.08.2020 08:00:09
Oberschlumpf
Hi,
ach so, nich gesehn.
Was für eine Meldung erhältst du denn, wenn du hier versuchst, die Datei downzuloaden?
Ciao
AW: Funktion klappt nur im Direktbereich
24.08.2020 08:53:01
Oberschlumpf
Hi Peter,
ich konnte rausfinden, wieso es ab Zeile 7 nicht funktioniert.
In der Zeile
Loop While Not oFinde Is Nothing And oFinde.Address  sErsteAdresse

ist und bleibt bei Zeile 7 und weitere oFinde.Address = sErsteAdresse.
Deswegen wird ab Zeile 7 die Funktion verlassen.
Wie man das verhindern kann, weiß ich allerdings auch nicht.
Ciao
Thorsten
Anzeige
AW: Funktion klappt nur im Direktbereich
24.08.2020 09:38:01
Peter
Hoi Thorsten
Bin nicht so sicher, dass dem so ist. Dann wenn du den Sub Test in Modul1 aufrufst, werden alle Werte ausgegeben ...
Gruss, Peter
AW: Funktion klappt nur im Direktbereich
24.08.2020 09:38:08
Peter
Hoi Thorsten
Bin nicht so sicher, dass dem so ist. Dann wenn du den Sub Test in Modul1 aufrufst, werden alle Werte ausgegeben ...
Gruss, Peter
AW: Funktion klappt nur im Direktbereich
24.08.2020 09:47:24
Oberschlumpf
na gut, die Werte, die im Einzelschrittmodus gezeigt werden, z Bsp oFinde.Address = sErsteAdresse, sind falsch
dann weiß ich erst recht nicht weiter
AW: Funktion klappt nur im Direktbereich
24.08.2020 10:43:48
peterk
Hallo
FindNext funktioniert in UDF's nicht!

Function FindValue(iNr As Integer, strSheet As String, lngCol As Long, _
lngColOut As Long, strFind As String) As Variant
Dim lRow As Long, rngData As Range, oFinde As Range, sErsteAdresse As String, i As Long
With ThisWorkbook.Sheets(strSheet)
lRow = .Cells(.Rows.Count, lngCol).End(xlUp).Row
Set rngData = ThisWorkbook.Sheets(strSheet).Range(.Cells(1, 5), .Cells(lRow, 5))
End With
Set oFinde = rngData.Find(What:=strFind, LookIn:=xlValues, Lookat:=xlWhole)
If Not oFinde Is Nothing Then
sErsteAdresse = oFinde.Address
i = 0
Do
i = i + 1
With oFinde.Offset(0, lngColOut - lngCol)
If i = iNr Then
FindValue = .Value
Exit Function
End If
End With
Set oFinde = rngData.Find(What:=strFind, LookIn:=xlValues, Lookat:=xlWhole, after:= _
oFinde)
Loop While Not oFinde Is Nothing And oFinde.Address  sErsteAdresse
End If
End Function

Anzeige
AW: Funktion klappt nur im Direktbereich
24.08.2020 10:14:11
Daniel
Hi
wo jetzt dein Fehler steck, weiß ich auch nicht.
aber ich würde das so programmieren, das funktioniert besser:
Function FindValue(iNr As Integer, strSheet As String, lngCol As Long, _
lngColOut As Long, strFind As String) As Variant
Dim arr
Dim Zeile As Long
Dim Zähler As Long
arr = ThisWorkbook.Sheets(strSheet).Range("A3:E22")
For Zeile = 1 To UBound(arr, 1)
If arr(Zeile, lngCol) = strFind Then
Zähler = Zähler + 1
If Zähler = iNr Then
FindValue = arr(Zeile, lngColOut)
Exit Function
End If
End If
Next
End Function
wobei du dir überlegen solltest, statt mit strString nur das Tabellenblatt hier gleich den ganzen Zellbereich der Quelle zu übergeben.
Das hätte den Vorteil, dass auch bei Änderungen in der Quelle die Formeln sofort automatisch neu berechnet werden.
Weiterhin würde sich dann die Formel automatisch anpassen, wenn du den Namen "Quelle" änderst, im jetzigen Zustand musst du selbst die Formeln anpassen.
Gruß Daniel
Anzeige
AW: Funktion klappt nur im Direktbereich
24.08.2020 20:23:27
Peter
Hallo Daniel
Vielen Dank. So klappt es. Den Zellbereich kann ich nicht übergeben, da er variabel ist (es werden sporadisch andere Daten in die Tabelle "Quelle" kopiert - die abgefüllten Zeilen ändern sich). Ich habe ihn jetzt abgefragt und der Variable rngQuelle übergeben.
Dabei habe ich herausgefunden, dass ich die Variable arr durch rngQuelle ersetzen könnte, obwohl es sich um einen Range handelt und nicht um einen Variant - hätte das anderweitig Nachteile?
Vielen Dank und Gruss, Peter
Function FindValue(iNr As Integer, strSheet As String, lngCol As Long, _
lngColOut As Long, strFind As String) As Variant
Dim arr, Zeile As Long, Zähler As Long
Dim rngQuelle As Range, lRow As Long
With Sheets(strSheet)
lRow = .Cells(.Rows.Count, lngCol).End(xlUp).Row
Set rngQuelle = .Range(.Cells(1, 1), .Cells(lRow, 5))
End With
arr = rngQuelle
For Zeile = 1 To UBound(arr, 1)
If arr(Zeile, lngCol) = strFind Then
Zähler = Zähler + 1
If Zähler = iNr Then
FindValue = arr(Zeile, lngColOut)
Exit Function
End If
End If
Next
End Function

Anzeige
AW: Funktion klappt nur im Direktbereich
24.08.2020 20:30:47
Daniel
Es kann Unterschiede in der Berechnungszeit geben.
Diese sollten aber erst bei größeren Datenmenge bemerkbar werden.
Was jetzt schneller ist, kann ich dir so aus dem Stand nicht sagen.
Der in der Schleife sollte der Zugriff auf Zellen langsamer sein als der Zugriff auf ein Array, allerdings braucht das Einlesen des Arrays auch seine Zeit.
Gruß Daniel

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige