Excel VBA ThisWokbook.Name als String in Sverweis

[Pascala]

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.

[HKindler]

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

[Pascala]

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

[Pascala]

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?=

[derHoepp]

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

[schauan]

Hallöchen,

ich versuche es mal anders. Ich denke, Du benötigst rng.Address
Vielleicht irgendwo auch noch ein Ausrufezeichen

[HKindler]

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
  

[Pascala]

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?

[snb]

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 ?

[HKindler]

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!