OffsetZelladresse per UDF ermitteln
21.10.2009 02:47:18
NoNet
Hallo Franz,
per SVERWEIS (VBA : VLOOKUP) kannst Du das nicht ermitteln, aber mit einer eigenen ~FUNCTION~ (UDF), die sich der Funktion VERGLEICH() (VBA : MATCH) bedient. Hier ein Codebeispiel :
Function MatchLinkAdresse(varSuchbegriff, rngBereich, _
Optional lngZeile = 0, Optional lngSpalte = 0)
'Ermittelt die Adresse eines Suchbegriffes innerhalb eines Bereiches
'und gibt das Offset dieser Zelladresse als Ergebnis zurück
'Beispiel (A1:A12 enthält Monatsnamen "Januar" bis "Dezember") :
'Aufruf als Tabellenblattfunktion :
'=MatchLinkAdresse("April";A1:A12) gibt "$A$4" zurück
'=MatchLinkAdresse("April";A1:A12;0;3) gibt "$D$4" zurück
'=MatchLinkAdresse("April";A1:A12;1;3) gibt "$D$5" zurück
'=MatchLinkAdresse("XY";A1:A12) gibt "#NV!" zurück
'Aufruf aus VBA heraus analaog (Komma anstatt Semikolon) :
'MsgBox MatchLinkAdresse("April",A1:A12) 'gibt "$A$4" aus
'21.10.2009, NoNet - www.excelei.de (z.Zt. nicht Online !)
Application.Volatile
If Application.CountIf(rngBereich, varSuchbegriff) = 0 Then
MatchLinkAdresse = CVErr(xlErrNA)
Else
MatchLinkAdresse = rngBereich(Application.Match( _
varSuchbegriff, rngBereich, 0)).Offset(lngZeile, lngSpalte).Address
End If
End Function
Die Hyperlinks (hier: in Spalte H) für Dein Beispiel kannst Du dann z.B. mit diesem Code erstellen :
Sub HyperlinksErstellen()
Dim lngZ As Long
For lngZ = 2 To 13 'Zeile 2 bis 13 der Spalte A :
'Erstellt in Spalte 8 (=H) den HYperlink zur Sequenz aus Spalte A :
ActiveSheet.Hyperlinks.Add anchor:=Cells(lngZ, 8), _
Address:="#Sequenzen!" & MatchLinkAdresse(Cells(lngZ, 6), [A2:A13], , 11)
Next
End Sub
Gruß, NoNet