VBA mit Formula bzw FormulaLocal arbeiten

Moderator: ModerationP

VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon Pocherle » 16. Jan 2018, 07:33

Hallo zusammen,

Ich habe einen Code mit der Funktion Makro aufzeichnen erstellt.
Dieser Code wurde in R1C1 erstellt jedoch kenne ich mich damit noch weniger aus wie schon generell in VBA.
Nun möchte ich diesen Code anpassen und die verschiedene SVerweis anpassen so das ich verscheidene Makros habe.
Nur bekomme ich jedes mal eine Fehlemeldung wenn ich den SVerweis mit Formula oder FormulaLocal eintrage.
Fehlermeldung: Laufzeitfehler 13 Typen unverträglich.

Hier der Code:
Code: Alles auswählen
Sub LS_VKB_DE_AT_CH_TEXTIL_PSS()
'
' LS_VKB_DE_AT_CH_TEXTIL_PSS Makro
'

'
    Sheets("LS Gesamt").Select
    Sheets("LS Gesamt").Copy After:=Sheets(1)
    Sheets("LS Gesamt (2)").Select
    Sheets("LS Gesamt (2)").Name = "LS Gesamt (HZA)"
    Sheets("LS Gesamt (HZA)").Select
    Sheets("LS Gesamt (HZA)").Move Before:=Sheets(1)
    Sheets("LS Gesamt").Select
    ActiveWindow.SmallScroll Down:=-21
    Range("A36").Select
    Selection.FormulaArray = _
        "=IF(RC8="""","""",(IF((R[-1]C8=RC8),1,"""")))"
    Range("B36").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(R1C8:R3000C8,'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_APP.xlsx]LBL_APP'!R1C10:R14010C17,8,0)="""",""-"",VLOOKUP(R1C8:R3000C8,'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_APP.xlsx]LBL_APP'!R1C10:R14010C17,8,0))"
    Range("C36").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(R1C8:R3000C8,'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_APP.xlsx]LBL_APP'!R1C10:R14010C18,9,0)="""",""-"",VLOOKUP(R1C8:R3000C8,'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_APP.xlsx]LBL_APP'!R1C10:R14010C18,9,0))"
    Range("D36").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(R1C8:R3000C8,'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_APP.xlsx]LBL_APP'!R1C10:R14010C39,30,0)"
    Range("A36:D36").Select
    ActiveWorkbook.Worksheets("LS Gesamt").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("LS Gesamt").AutoFilter.Sort.SortFields.Add Key:= _
        Range("H35:H2500"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
   With ActiveWorkbook.Worksheets("LS Gesamt").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
Dim letzte As Long
 letzte = Cells(Rows.Count, 4).End(xlUp).Row
Range("A36:D36").Autofill Destination:=Range("A36:D" & letzte - 2)
End With
End Sub

Nun angenommen ich möchte den ersten SVerweis mit der normalen Schreibweise ersetzen.
Wie funktioniert dies?

Danke & Viele Grüße,

Pascal
Pocherle
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Jan 2018, 06:50

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon slowboarder » 16. Jan 2018, 09:48

Hi
was ist normale Schreibweise?

deine VLookUps sind generell falsch.
der erste Parameter muss ein Einzelwert bzw ein Bezug auf eine einzelne Zelle sein, bei dir steht dort aber ein Zellbezug auf einen Zellbereich mit mehreren Zellen: VLOOKUP(R1C8:R3000C8,'W:\Germany...

Die Formel, die du als ArrayFormel in die Zelle A36 schreiben willst, ist eine normale Formel und keine Array-Formel.
"=IF(RC8="""","""",(IF((R[-1]C8=RC8),1,"""")))"
(außerdem sind unnötige Klammern drin, aber die sollten kein Problem sein.)

also insgesamt noch ziemlich wirr das ganze

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 24050
Registriert: 18. Apr 2009, 13:33

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon Pocherle » 16. Jan 2018, 10:05

slowboarder hat geschrieben:Hi
was ist normale Schreibweise?

deine VLookUps sind generell falsch.
der erste Parameter muss ein Einzelwert bzw ein Bezug auf eine einzelne Zelle sein, bei dir steht dort aber ein Zellbezug auf einen Zellbereich mit mehreren Zellen: VLOOKUP(R1C8:R3000C8,'W:\Germany...

Die Formel, die du als ArrayFormel in die Zelle A36 schreiben willst, ist eine normale Formel und keine Array-Formel.
"=IF(RC8="""","""",(IF((R[-1]C8=RC8),1,"""")))"
(außerdem sind unnötige Klammern drin, aber die sollten kein Problem sein.)

also insgesamt noch ziemlich wirr das ganze

Gruß Daniel


Hi Daniel,

Danke für Tipps.
Das mit dem Sverweis hab ich total übersehen.
Die Array Formel funktioniert leider nicht anders.
Ich würde gerne direkte Zellbezüge schreiben wie z.b.:
Code: Alles auswählen
=WENN(SVERWEIS($H36;'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_APP.xlsx]LBL_APP'!$J$1:$Q$14010;8;0)="";"-";SVERWEIS($H36;'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_APP.xlsx]LBL_APP'!$J$1:$Q$14010;8;0))

Würde mir einige Arbeit ersparen.

Danke & Grüße,
Pascal
Pocherle
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Jan 2018, 06:50

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon slowboarder » 16. Jan 2018, 10:25

Hi

wenn du direkte Zellbezüge mit deutscher Schreibweise verwenden willst (Semikolon als Trennzeichen, Komma als Dezimalzeichen, deutsche Funktionsnamen), dann musst du
Selection.FormulaLocal = "=Deine Formel normal in Deutsch"
verwenden.
Die Anführungszeichen, die zur Formel gehören, müssen aber auch hier immer doppelt eingegeben werden, damit der VBA-Editor sie von den Anführungszeichen unterscheiden kann, die den Textstring begrenzen.

wenn du die Array-Formel eingeben willst, musst du aber bei der R1C1-Schreibweise bleiben.

wenns dir zu kompliziert ist, die Formeln im VBA-Editor zu erstellen, dann kannst du sie auch normal von Hand in die Zellen schreiben und dir dann im Direktfenster die Formel in der gewünschten Schreibweise anzeigen lassen.
hierzu selektierst du die Formel und gibst im Direktfenster:
?Selection.Formula
ein. hierbei kannst du dir auch alle 4 möglichen Schreibvarianten anzeigen lassen:
?Selection.Formula
?Selection.FormulaR1C1
?Selection.FormulaLocal
?Selection.FormulaR1C1Local

du kannst hier auch gleich die Verdopplung der Anführungszeichen vornehmen, so dass du den angezeigen Formeltext ohne Änderung aus dem Direktfenster in den Code kopieren kannst:
?Replace(Selection.FormulaLocal, """", """""")
Das funktioniert natürlich auch mit allen 4 Formelschreibweisen.

Gruß Daniel
slowboarder
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 24050
Registriert: 18. Apr 2009, 13:33

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon Klaus-Dieter » 16. Jan 2018, 10:29

Hallo Pascal,

wenn du schon mit VBA arbeitest, kannst du die Berechnungen doch gleich dort durchführen, dann brauchst du keine Formeln in deine Liste schreiben lassen.
Viele Grüße
Klaus-Dieter
Lösungsvorschläge sind, wenn es keinen anders lautenden Hinweis gibt, von mir getestet.
Künstliche Intelligenz ist besser als natürliche Dummheit.
Benutzeravatar
Klaus-Dieter
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 17334
Registriert: 27. Nov 2003, 23:03
Wohnort: Sassenburg

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon Pocherle » 16. Jan 2018, 15:05

slowboarder hat geschrieben:Hi

wenn du direkte Zellbezüge mit deutscher Schreibweise verwenden willst (Semikolon als Trennzeichen, Komma als Dezimalzeichen, deutsche Funktionsnamen), dann musst du
Selection.FormulaLocal = "=Deine Formel normal in Deutsch"
verwenden.
Die Anführungszeichen, die zur Formel gehören, müssen aber auch hier immer doppelt eingegeben werden, damit der VBA-Editor sie von den Anführungszeichen unterscheiden kann, die den Textstring begrenzen.

wenn du die Array-Formel eingeben willst, musst du aber bei der R1C1-Schreibweise bleiben.

wenns dir zu kompliziert ist, die Formeln im VBA-Editor zu erstellen, dann kannst du sie auch normal von Hand in die Zellen schreiben und dir dann im Direktfenster die Formel in der gewünschten Schreibweise anzeigen lassen.
hierzu selektierst du die Formel und gibst im Direktfenster:
?Selection.Formula
ein. hierbei kannst du dir auch alle 4 möglichen Schreibvarianten anzeigen lassen:
?Selection.Formula
?Selection.FormulaR1C1
?Selection.FormulaLocal
?Selection.FormulaR1C1Local

du kannst hier auch gleich die Verdopplung der Anführungszeichen vornehmen, so dass du den angezeigen Formeltext ohne Änderung aus dem Direktfenster in den Code kopieren kannst:
?Replace(Selection.FormulaLocal, """", """""")
Das funktioniert natürlich auch mit allen 4 Formelschreibweisen.

Gruß Daniel


Danke für deine Umfangreiche Antwort.
Jedoch habe ich alle Lösungen durchgespielt und es hat leider nicht funktioniert.
Bei FormulaLocal become ich nach eingabe meiner Formel die Fehlermeldung: Laufzeitfehler 13 Typen unverträglich.
Dann habe ich es mit dem Direktfenster ausprobiert und er hat mir dann auch eine R1C1 Formel gegeben jedoch wenn ich sie einfüge bekomme ich den selben Fehler wie bei FormulaLocal.

Es ist immer die selbe stelle an dem es hängt.
Code: Alles auswählen
    Selection.FormulaLocal = "=WENN(SVERWEIS($H$1:$H$2000;'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_FTW.xlsx]LBL_FTW'!$J$1:$Q$10010;8;0)="";" - ";SVERWEIS($H$1:$H$2000;'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_FTW.xlsx]LBL_FTW'!$J$1:$Q$10010;8;0))"


Danke & Grüße,

Pascal

Hallo Pascal,

wenn du schon mit VBA arbeitest, kannst du die Berechnungen doch gleich dort durchführen, dann brauchst du keine Formeln in deine Liste schreiben lassen.


Hallo Klaus,

wie genau kann ich das verstehen? Ich muss doch eine Formel angeben damit Excel weis was es zu tun hat. Oder liege ich da falsch?

Danke & Grüße,

Pascal
Pocherle
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Jan 2018, 06:50

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon Klaus-Dieter » 16. Jan 2018, 15:08

Hallo Pascal,

wie genau kann ich das verstehen? Ich muss doch eine Formel angeben damit Excel weis was es zu tun hat. Oder liege ich da falsch?


Die Formel muss nicht im Tabellenblatt stehen, man kann auch in VBA rechnen und das Ergebnis in die Tabelle schreibe. So handhabe ich das immer.
Viele Grüße
Klaus-Dieter
Lösungsvorschläge sind, wenn es keinen anders lautenden Hinweis gibt, von mir getestet.
Künstliche Intelligenz ist besser als natürliche Dummheit.
Benutzeravatar
Klaus-Dieter
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 17334
Registriert: 27. Nov 2003, 23:03
Wohnort: Sassenburg

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon Pocherle » 16. Jan 2018, 15:16

Klaus-Dieter hat geschrieben:Hallo Pascal,

wie genau kann ich das verstehen? Ich muss doch eine Formel angeben damit Excel weis was es zu tun hat. Oder liege ich da falsch?


Die Formel muss nicht im Tabellenblatt stehen, man kann auch in VBA rechnen und das Ergebnis in die Tabelle schreibe. So handhabe ich das immer.


Das hört sich gut an. Wenn es auch mit Sverweis funktioniert könntest du mir dann ein beispiel schicken wie sowas denn ausschaut?
Pocherle
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 6
Registriert: 16. Jan 2018, 06:50

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon Klaus-Dieter » 16. Jan 2018, 15:44

Hallo Pocherle,

habe dir mal ein Beispiel erstellt. Wenn du auf die Schaltfläche in Spalte B drückst, werden per Makro die Formeln eingetragen. Beim Klick auf die Schaltfläche in Spalte C bekommst du die Ergebnisse ohne Formel per Makro eingetragen. Die Makros findest du im VBE, aber das weißt du ja.
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
Viele Grüße
Klaus-Dieter
Lösungsvorschläge sind, wenn es keinen anders lautenden Hinweis gibt, von mir getestet.
Künstliche Intelligenz ist besser als natürliche Dummheit.
Benutzeravatar
Klaus-Dieter
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 17334
Registriert: 27. Nov 2003, 23:03
Wohnort: Sassenburg

Re: VBA mit Formula bzw FormulaLocal arbeiten

Beitragvon HKindler » 18. Jan 2018, 10:48

Hi,
Pocherle hat geschrieben:
slowboarder hat geschrieben:...
Die Anführungszeichen, die zur Formel gehören, müssen aber auch hier immer doppelt eingegeben werden, damit der VBA-Editor sie von den Anführungszeichen unterscheiden kann, die den Textstring begrenzen.

...
Es ist immer die selbe stelle an dem es hängt.
Code: Alles auswählen
    Selection.FormulaLocal = "=WENN(SVERWEIS($H$1:$H$2000;'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_FTW.xlsx]LBL_FTW'!$J$1:$Q$10010;8;0)="";" - ";SVERWEIS($H$1:$H$2000;'W:\Germany\Productmarketing\Samples_adidas\Auslieferungen\LBL\CENTRAL\[LBL_adi_CENTRAL_FTW.xlsx]LBL_FTW'!$J$1:$Q$10010;8;0))"


Wenn du den Hinweis von Daniel befolgt hättest, dann würde es auch mit deinen Formeln klappen!
Nochmals: ALLE Anführungszeichen innerhalb einer Formel sind für die Zuweisung an eine der .Formula-Eigenschaften zu verdoppeln.

Ganz ausführlich: sollen innerhalb eines Strings (etwas anderes ist in den .Formula-Eigenschaften nicht enthalten) Anführungszeichen auftauchen, dann muss man diese bei der Zuweisung doppelt eingeben, da sie sonst als Ende des Strings interpretiert werden, was dann zu einem Fehler führt, da der Rest des Strings als (fehlerhafter) Befehl verstanden wird.

Ich selbst verwende auch oft die Methode mit Chr(34) das Anführungszeichen direkt in den String zu schreiben. Vor allem bei mehreren Anführungszeichen hintereinander wird es sonst für meinen Geschmack schnell unübersichtlich:
Code: Alles auswählen
=WENN(A1="";"";1)
wird z.B. zu
Code: Alles auswählen
.FormulaLocal = "=WENN(A1="""";"""";1)"
oder zu
Code: Alles auswählen
.FormulaLocal = "=WENN(A1=" & Chr(34) & Chr(34) & ";" & Chr(34) & Chr(34) & ";1)"
oder zu
Code: Alles auswählen
Dim a As String
a = Chr(34)
.FormulaLocal = "=WENN(A1=" & a & a & ";" & a & a & ";1)"
oder zu
Code: Alles auswählen
Dim a As String, aa As String
a = Chr(34)
aa = a & a
.FormulaLocal = "=WENN(A1=" & aa & ";" & aa & ";1)"
Gruß,
Helmut

----------------------------
Windows 8.1 (64 Bit) / Office 2013 (32 Bit)
HKindler
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 1982
Registriert: 04. Jul 2013, 09:02


Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: Bing [Bot] und 26 Gäste