SVERWEIS-Funktion in VBA mit Bezug auf anderes Blatt
Marco Schmid
Ich habe folgendes Problem: Gerne möchte ich mit einer VBA-Formel
(FormulaLocal) eine SVERWEIS-Abfrage machen. Das Problem ist nun aber,
dass die Daten in einem anderen Blatt gespeichert sind und es mir nun
nicht gelingt die Range auf dem benötigten Baltt anzusprechen. Ich
habe es mit folgender Verküpfung versucht:
Sheets("Optimierung").Cells(20, 19).FormulaLocal = "=sverweis(" &
Sheets("Optimierung").Cells(20, 1).Address & ";" & HIER SOLLTE DER
BEREICH A5:C100 des SHEETS "Datenbank" stehen... & ", 3)"
Rückgabewert solle die 3.Spalte der Matrix A5:C100 des Sheets
"Datenbank" sein. Ich habe irgendwie alle Möglichkeiten per "Try and
Error" versucht, komme aber leider nicht auf eine korrekte Lösung :-
( kann mir jemand auf die Sprünge helfen? Besten Dank und lieber Gruss
Marco
Andreas Killer
> Ich habe folgendes Problem: Gerne m�chte ich mit einer VBA-Formel
> (FormulaLocal) eine SVERWEIS-Abfrage machen. Das Problem ist nun aber,
Abfrage? Wieso machst Du das nicht gleich mit VBA?
IMHO ist es wenig effektiv eine Formel in ein Blatt zu schreiben und
dann das Ergebnis zu lesen.
> Sheets("Optimierung").Cells(20, 19).FormulaLocal = "=sverweis(" &
> Sheets("Optimierung").Cells(20, 1).Address & ";" & HIER SOLLTE DER
> BEREICH A5:C100 des SHEETS "Datenbank" stehen... & ", 3)"
Eine gute Methode ist die Formel da hinzuschreiben wo sie hinsoll und
sie dann einfach als String in den VBA-Editor zu kopieren.
Anschlie�end einfach alle Zellbereiche durch
" & Range.Address & "
tauschen und diese sp�ter erg�nzen. Will es trotzdem nicht klappen,
dann ist es hilfreich die erzeugte Formel erstmal als String zu
speichern, den kann man sich gut via Debug.Print ausgeben/ankucken.
Dein Problem war wohl eher das Du ein , statt ; in der Formel hast.
Andreas.
Sub Test()
Dim S As String
With Sheets("Optimierung")
S = "=sverweis(" & .Cells(20, 1).Address & ";" & _
.Range("A5:C100").Address & ";3)"
Debug.Print S
.Cells(20, 19).FormulaLocal = S
End With
End Sub
Marco Schmid
> > (FormulaLocal) eine SVERWEIS-Abfrage machen. Das Problem ist nun aber,
>
> Abfrage? Wieso machst Du das nicht gleich mit VBA?
>
> IMHO ist es wenig effektiv eine Formel in ein Blatt zu schreiben und
> dann das Ergebnis zu lesen.
>
> > Sheets("Optimierung").Cells(20, 19).FormulaLocal = "=sverweis(" &
> > Sheets("Optimierung").Cells(20, 1).Address & ";" & HIER SOLLTE DER
> > BEREICH A5:C100 des SHEETS "Datenbank" stehen... & ", 3)"
>
> Eine gute Methode ist die Formel da hinzuschreiben wo sie hinsoll und
> sie dann einfach als String in den VBA-Editor zu kopieren.
>
> " & Range.Address & "
> tauschen und diese später ergänzen. Will es trotzdem nicht klappen,
> dann ist es hilfreich die erzeugte Formel erstmal als String zu
> speichern, den kann man sich gut via Debug.Print ausgeben/ankucken.
>
> Dein Problem war wohl eher das Du ein , statt ; in der Formel hast.
>
> Andreas.
>
> Sub Test()
> Dim S As String
> With Sheets("Optimierung")
> S = "=sverweis(" & .Cells(20, 1).Address & ";" & _
> .Range("A5:C100").Address & ";3)"
> Debug.Print S
> .Cells(20, 19).FormulaLocal = S
> End With
> End Sub
Hallo Andreas
Danke für Deine Antwort! Ich habe die Abfrage der VBA-Version
vorgezogen, weil der User des Programms anschliessend die Möglichkeit
haben soll in der Tabelle Werte zu ändern und dies soll dann
(sozusagen Realtime) auch wieder Einfluss auf die Inputparameter
dieser Formel haben.
Die Formel in der Tabelle lautet:
=SVERWEIS(A20;Datenbank!$A$5:$C$126;3)
Probleme bereit mir aber eben nun der Umstand, dass sich die
Qullenmatrix des Verweises nicht im aktuellen Shhet, sondern auf dem
Sheet Optimierung befindet. Das kann ich nicht in VBA übersetzen...
(Anfänger eben :-). Deine vorgeschlagene Lösung
> Sub Test()
> Dim S As String
> With Sheets("Optimierung")
> S = "=sverweis(" & .Cells(20, 1).Address & ";" & _
> .Range("A5:C100").Address & ";3)"
> Debug.Print S
> .Cells(20, 19).FormulaLocal = S
> End With
> End Sub
würde meiner Meinung nach aber nur klappen, wenn alles auf dem
gleichen Sheet ist, oder?
Gruss
Marco
Andreas Killer
>> Sub Test()
>> Dim S As String
>> With Sheets("Optimierung")
>> S = "=sverweis(" & .Cells(20, 1).Address & ";" & _
>> .Range("A5:C100").Address & ";3)"
>> Debug.Print S
>> .Cells(20, 19).FormulaLocal = S
>> End With
>> End Sub
>
> w�rde meiner Meinung nach aber nur klappen, wenn alles auf dem
> gleichen Sheet ist, oder?
Ja wie es programmiert ist: Ja.
Ach so, jetzt f�llt der Groschen, Du wolltest den Bereich auf
Sheets("Datenbank") haben, entschuldige, hab ich glatt �berlesen. :-)
Musst Du nur 2 Sachen �ndern:
a.) den Bereich entsprechend referenzieren und
b.) beim Address-Parameter zus�tzlich External:=True setzen, dann
erzeugt es den Namen der Mappe/Tabelle mit.
Andreas.
Sub Test()
Dim S As String
With Sheets("Optimierung")
S = "=sverweis(" & .Cells(20, 1).Address & ";" & _
Sheets("Datenbank").Range("A5:C100").Address( _
External:=True) & ";3)"
Marco Schmid
> Marco Schmid schrieb:
>
> >> Sub Test()
> >> Dim S As String
> >> With Sheets("Optimierung")
> >> S = "=sverweis(" & .Cells(20, 1).Address & ";" & _
> >> .Range("A5:C100").Address & ";3)"
> >> Debug.Print S
> >> .Cells(20, 19).FormulaLocal = S
> >> End With
> >> End Sub
>
> > gleichen Sheet ist, oder?
>
> Ja wie es programmiert ist: Ja.
>
> Sheets("Datenbank") haben, entschuldige, hab ich glatt überlesen. :-)
>
> Musst Du nur 2 Sachen ändern:
>
> a.) den Bereich entsprechend referenzieren und
> erzeugt es den Namen der Mappe/Tabelle mit.
>
> Andreas.
>
> Sub Test()
> Dim S As String
> With Sheets("Optimierung")
> S = "=sverweis(" & .Cells(20, 1).Address & ";" & _
> Sheets("Datenbank").Range("A5:C100").Address( _
> External:=True) & ";3)"
> Debug.Print S
> .Cells(20, 19).FormulaLocal = S
> End With
> End Sub
Hallo Andreas!
Jetzt klappt's! Besten Dank für den Input und freundliche Grüsse
Marco