Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Excel VBA ThisWokbook.Name als String in Sverweis
#1
Star 
Hallo,

ich wollte hier kurz nachfragen wie ich einen String in einem Sverweis mit Formulalocal integriere?

Hier mein bisheriger Code:
Code:
Dim Name As String

Name = ThisWorkbook.Name


.FormulaLocal = "=SVERWEIS([TEST.xlsx]APP!J11;'Name'!$I$139:$J$408;2;0)"

Dort wo Name steht sollte eigentlich der DateiName hin.
Antworten Top
#2
Hi,

entweder über das normale Zusammensetzen von Strings:
Code:
Dim Name As String

Name = ThisWorkbook.Name

.FormulaLocal = "=SVERWEIS([TEST.xlsx]APP!J11;'" & Name & "'!$I$139:$J$408;2;0)"

oder über Replace
Code:
Dim Name As String

Name = ThisWorkbook.Name

.FormulaLocal = Replace("=SVERWEIS([TEST.xlsx]APP!J11;'Name'!$I$139:$J$408;2;0)", "Name", Name)
oder besser:
Code:
.FormulaLocal = Replace("=SVERWEIS([TEST.xlsx]APP!J11;'xxx'!$I$139:$J$408;2;0)", "xxx", ThisWorkbook.Name)

Übrigens ist es eine ganz schlecht Idee eine Variable so oder auch nur so ähnlich zu nennen wie Eigenschaften oder Methoden von Objekten. Die Verwechslungsgefahr ist einfach zu groß.

Ich hoffe dir ist bewusst dass das aber nur die halbe Miete ist. Du brauchst nicht nur den Dateinamen, sondern auch noch den Blattnamen. Also fehlt noch:
Code:
Name = "[" & ThisWorkbook.Name & "]" & Worksheets(1).Name
Gruß,
Helmut

Win10 - Office365 / MacOS - Office365
[-] Folgende(r) 1 Nutzer sagt Danke an HKindler für diesen Beitrag:
  • Pascala
Antworten Top
#3
(06.10.2022, 13:56)HKindler schrieb: Hi,

entweder über das normale Zusammensetzen von Strings:
Code:
Dim Name As String

Name = ThisWorkbook.Name

.FormulaLocal = "=SVERWEIS([TEST.xlsx]APP!J11;'" & Name & "'!$I$139:$J$408;2;0)"

oder über Replace
Code:
Dim Name As String

Name = ThisWorkbook.Name

.FormulaLocal = Replace("=SVERWEIS([TEST.xlsx]APP!J11;'Name'!$I$139:$J$408;2;0)", "Name", Name)
oder besser:
Code:
.FormulaLocal = Replace("=SVERWEIS([TEST.xlsx]APP!J11;'xxx'!$I$139:$J$408;2;0)", "xxx", ThisWorkbook.Name)

Übrigens ist es eine ganz schlecht Idee eine Variable so oder auch nur so ähnlich zu nennen wie Eigenschaften oder Methoden von Objekten. Die Verwechslungsgefahr ist einfach zu groß.

Ich hoffe dir ist bewusst dass das aber nur die halbe Miete ist. Du brauchst nicht nur den Dateinamen, sondern auch noch den Blattnamen. Also fehlt noch:
Code:
Name = "[" & ThisWorkbook.Name & "]" & Worksheets(1).Name

Vielen lieb Dank für die Ausführliche Antwort!

Dank dir klappt nun der Sverweis mit der Variable.

Könntest du mir vielleicht noch einmal helfen?

Ich würde nun gerne einen Variablen Bereich identifizieren.
Folgende Code Zeile funktioniert:
Code:
Workbooks("TEST.xlsm").Sheets("LBL_APP").Range("I36:J" & Cells(Rows.Count, "I").End(xlUp).Row).SpecialCells(xlCellTypeVisible)

Ich würde nur gerne das Ergebnis dieser Zeile als Variable Range definieren und den Test Workboos Namen mit unserer Namens Variable.
Hast du hierzu eine Idee?

Danke & Grüße
Antworten Top
#4
Hallo zusammen,

habe jetzt folgenden Code:
Code:
Sub Schaltfläche2_Klicken()

With ActiveWorkbook.Sheets("DN_adi_FR")
Dim Namestrig As String
Dim rng As Range
Set rng = Range("I36:J" & Range("I36").End(xlDown).Row)
Namestrig = "[" & ThisWorkbook.Name & "]" & Worksheets(2).Name

End With
With Workbooks("LBL_adi_FRANCE_APP_TEST.xlsx").Sheets("LBL_APP").Range("A11")


.FormulaLocal = "=SVERWEIS([LBL_adi_FRANCE_APP_TEST.xlsx]LBL_APP!J11;'" & Namestrig & "'" & rng & ";2;0)"

End With

Jedoch bekomme ich leider ein "Typen unverträglich" bei Zeile: .FormulaLocal = "=SVERWEIS([LBL_adi_FRANCE_APP_TEST.xlsx]LBL_APP!J11;'" & Namestrig & "'" & rng & ";2;0)" 

Weis zufällig jemand woran das liegen könnte?=
Antworten Top
#5
Hi,

rng ist ein Rangeobjekt, das du allerdings wie einen String verwenden willst. Das funktioniert nur, wenn rng aus einer einzelnen Zelle besteht und die .Value-Eigenschaft einen primitiven Wert zurückgibt, der sich in einen String casten lässt. Sobald rng mehr als eine Zelle umfasst, gibt die .Value-Eigenschaft ein Array zurück. Arrays kannst du allerdings nicht einfach mit einem kaufmännischen Und an andere Zeichenketten anhängen.

In einer SVERWEIS-Formel benötigst du allerdings gar keinen zugriff auf das Rangeobjekt. es reicht doch völlig aus, wenn du den Zellbereich in A1-Schreibweise angibst. Verzichte also einfach auf das bilden eines Rangeobjektes und mach mit der gleichen Logik direkt eine Zellbereichsadresse als String daraus.

Viele Grüße
derHöpp
Antworten Top
#6
Hallöchen,

ich versuche es mal anders. Ich denke, Du benötigst rng.Address
Vielleicht irgendwo auch noch ein Ausrufezeichen Smile
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#7
Hi,

man kann den String analog wie zur Bestimmung von rng zusammenbauen oder man kann rng.Address im String verwenden. Oder man überlässt die Arbeit komplett VBA:
Code:
Sub Schaltfläche2_Klicken()
Dim rng As Range
With ThisWorkbook.Sheets("DN_adi_FR").Range("I36")
    Set rng = .Resize(.End(xlDown).Row - .Row + 1, 2)
End With
Workbooks("LBL_adi_FRANCE_APP_TEST.xlsx").Sheets("LBL_APP").Range("A11").FormulaLocal = _
          "=SVERWEIS(J11;" & rng.AddressLocal(RowAbsolute:=True, ColumnAbsolute:=True, External:=True) & ";2;0)"
End Sub

Noch ein paar Tipps:
  • verwende Variablen nur, wenn nötig
  • verwende Einrückungen bei Blöcken
  • verzichte auf (überflüssige) Leerzeilen
  • vergiss innerhalb eines With-Blocks niemals den Punkt vor den entsprechenden Objekten
Gruß,
Helmut

Win10 - Office365 / MacOS - Office365
[-] Folgende(r) 1 Nutzer sagt Danke an HKindler für diesen Beitrag:
  • Pascala
Antworten Top
#8
(10.10.2022, 16:35)HKindler schrieb: Hi,

man kann den String analog wie zur Bestimmung von rng zusammenbauen oder man kann rng.Address im String verwenden. Oder man überlässt die Arbeit komplett VBA:
Code:
Sub Schaltfläche2_Klicken()
Dim rng As Range
With ThisWorkbook.Sheets("DN_adi_FR").Range("I36")
    Set rng = .Resize(.End(xlDown).Row - .Row + 1, 2)
End With
Workbooks("LBL_adi_FRANCE_APP_TEST.xlsx").Sheets("LBL_APP").Range("A11").FormulaLocal = _
          "=SVERWEIS(J11;" & rng.AddressLocal(RowAbsolute:=True, ColumnAbsolute:=True, External:=True) & ";2;0)"
End Sub

Noch ein paar Tipps:
  • verwende Variablen nur, wenn nötig
  • verwende Einrückungen bei Blöcken
  • verzichte auf (überflüssige) Leerzeilen
  • vergiss innerhalb eines With-Blocks niemals den Punkt vor den entsprechenden Objekten

Vielen lieben Dank für die ausführliche Information!

Dein Code funktioniert bei mir tadellos.

Nun ist mir aber aufgefallen das der Filterbereich doch nicht jedes mal bei I36 anfängt.
Sondern er könnte überall in Spalte "I" anfangen.
Wie sage ich nun das er bitte die Erste Zelle im Sichtbaren Filter Bereich nimmt?
Antworten Top
#9
Verzichte auf Excelformeln eintragen in VBA.
Code:
Sub Schaltfläche2_Klicken()
   With Workbooks("LBL_adi_FRANCE_APP_TEST.xlsx").Sheets("LBL_APP")
      .Range("A11") = ThisWorkbook.Sheets("DN_adi_FR").Range("I36").Find(.Range("J11")).Offset(, 1).Value
   End With
End Sub


PS.
Ist Workbooks("LBL_adi_FRANCE_APP_TEST.xlsx") identisch zu ThisWorkbook ?
Zum übersetzen von Excel Formeln:

http://dolf.trieschnigg.nl/excel/index.p...gids=en+de
Antworten Top
#10
Hi,

was für ein Filter? Und woher soll ich wissen, wo dieser Filter los geht?
Vielleicht solltest du mal beschreiben, was du eigentlich machen willst und wieso das per VBA sein muß. Und dann wäre auch eine Beispieldatei sinnvoll.

Wenn es sich um den Autofilter handelt, dann könntest du den With-Block so schreiben:
Code:
With ThisWorkbook.Sheets("DN_adi_FR")
    Set rng = Intersect (.Autofilter.Range, .Range("I:J"))
End With

Und bitte keine Full-Quotes!
Gruß,
Helmut

Win10 - Office365 / MacOS - Office365
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste