Eigene Funktion - diverse Fragen | HERBERS Excel Forum

Hallo,
habe eine Frage zu folgendem Code (in dem noch einige "Fehler" enthalten sind):


Function MyFunc(FirstRow As Long, Suchtext As String)
Dim Spalte As String
Application.Volatile
LastRow = WorksheetFunction.Match(Suchtext, _
ActiveSheet.Range("A" & FirstRow & ":A60000"), 0) + FirstRow - 2
Spalte = Split(ActiveCell.Address, "$")(1)
MyFunc = ActiveSheet.Name & "!" & Spalte & FirstRow & ":" & Spalte & LastRow
End Function

Funktionsweise soll sein: Im Worksheet(1) (z.B. "Overview") steht in Spalte A am Ende einer Tabelle der Suchtext, z.B. "Total". In die funktionsaufrufende Zelle (z.B. C2) soll nun ein Bezug als Text geschrieben werden: Overview!$C$15:$C$40 (die erste Zeile, hier z.B. 15, wird im Funktionsaufruf festgelegt).
Es tauchen allerdings folgende Probleme auf:
1) Wie ermittelt man die Zelle und das Worksheet der funktionsaufrufenden Zelle? ActiveSheet und ActiveCell liefern falsche Ergebnisse (verständlicherweise).
2) Wie sucht man am besten den Suchtext, der immer in Spalte A steht? In meinem Beispiel habe ich jetzt einfach mal "A15:A60000" gewählt. Muss man überhaupt eine Range angegeben oder gibt es eine Funktion die oben startet und nach unten "geht"? Das alles sollte mit möglichst wenig Rechenaufwand geschehen, da die Excel-Datei durch z.B. unzählige Summenprodukt-Formeln schon genug überlastet ist. Macht es Sinn, dies über eine Schleife zu lösen? Wie wirkt sich das auf die Rechenpower aus?
Viele Grüße,
Boris

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Zu 1.

06.05.2009 14:38:17

Nepumuk

AW: Zu 1.

06.05.2009 17:04:13

Boris

zu 2.

06.05.2009 16:09:34

Dieter

AW: zu 2.

06.05.2009 17:03:38

Boris

AW: zu 2.

09.05.2009 21:09:41

Boris

AW: zu 2.

10.05.2009 10:10:21

Hajo_Zi

AW: zu 2.

11.05.2009 10:46:30

Boris

Hallo Boris,
ich hab's mal in deine Funktion integriert:

Public Function MyFunc(FirstRow As Long, Suchtext As String) As String
    Dim Spalte As String
    Dim LastRow As Long
    Dim objCell As Range, objWorksheet As Worksheet
    Application.Volatile
    If TypeOf Application.Caller Is Range Then
        Set objCell = Application.Caller
        Set objWorksheet = objCell.Parent
        LastRow = WorksheetFunction.Match(Suchtext, _
            objWorksheet.Range("A" & FirstRow & ":A60000"), 0) + FirstRow - 2
        Spalte = Split(objCell.Address, "$")(1)
        MyFunc = objWorksheet.Name & "!" & Spalte & _
            CStr(FirstRow) & ":" & Spalte & CStr(LastRow)
    End If
End Function

Gruß
Nepumuk

Sub Find_mehrmals() '* H. Ziplies * '* 24.11.08 * '* erstellt von Hajo.Ziplies@WEB.de * '* http://Hajo-Excel.de Dim Found As Range Dim FirstAddress As String Dim Search As String Dim LoLetzte As Long Dim LoI As Long Dim ByMldg As Byte Search = Worksheets("Tabelle2").Range("A1") With Worksheets("Tabelle1") LoLetzte = IIf(IsEmpty(.Range("A65536")), .Range("A65536").End(xlUp).Row, 65536) ' von Peter Haserodt Set Found = .Range("A1:A" & LoLetzte).Find(Search, .Range("A" & LoLetzte), , _ xlWhole, , xlNext) If Found Is Nothing Then Exit Sub 'falls nicht gefunden wird sub verlassen If MsgBox("Gefunden in Zelle " & Found.Address(0, 0) & " ist dies richt richtige _ Zelle !!!" _ , vbYesNo + vbQuestion, "Abfrage") = 6 Then Found.Interior.ColorIndex = 3 Else FirstAddress = Found.Address Do Set Found = .Range("A1:A" & LoLetzte).FindNext(Found) If Found.Address = FirstAddress Then Exit Sub If MsgBox("Gefunden in Zelle " & Found.Address(0, 0) & " ist dies richt _ richtige Zelle !!!" _ , vbYesNo + vbQuestion, "Abfrage") = 6 Then Found.Interior.ColorIndex = 3 Exit Do End If If Found.Row = LoLetzte Then Exit Do LoI = LoI + 1 Loop End If End With Set Found = Nothing End Sub

Beliebteste Forumthreads (12 Monate)

Eigene Funktion - diverse Fragen

7 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread