Syntaxfehler? - Excel und VBA

Question

Hallo Community,

hier habe ich wohl ein einfaches Problem, was ich trotz Suche und gefundenen Beispielen nicht hinkriege. Wo finde ich  eine gute Anleitung für Syntax Excel und VBA?

Problem:

In meinem Tabellenblatt "DATENEINGABE" benötige ich in Zelle AF6 bis AF 57 folgende Formel

=MAX('DATEN 2016'!$P6:$AD6)

   2016 soll ersetzt werden durch die Variable im Tabellenblatt"Parameter" in Zelle A56  (z. Z. 2016)

und in VBA habe ich es so versucht:

Sub Formel()

Dim myStr As String

myStr = "'DATEN'!" & Sheets("PARAMETER").Range("A56")

Sheets("DATENEINGABE").Range("AF6:AF57").Formula = "=MAX( " & myStr & " P6:AD6)"

End Sub

Ein ähnliches Problem habe ich bei

Sheets("Jahr").Range("QN8:SW57").Value = Sheets("Jahr").Range("NF8:PO57").Value

Funktioniert mit value aber nicht mit Formats oder ValuesAndNumberFormats.

Schöne Grüße

Michael

Answer 1

Hallo Michael,

dann will ich Dir mal das Gegenstück zeigen was als Resultat das gleiche macht.

Andreas.

Sub Test()

  Dim Jahr As Range, Was As Range, Dest As Range

  Dim Formel As String

  'Bei sowas kann eine Menge Murks passieren, daher:

  On Error GoTo Errorhandler

  'Wo ist das Jahr?

  Set Jahr = Worksheets("Parameter").Range("A56")

  'Wo ist der zu berechnende Bereich?

  Set Was = Worksheets("Daten " & Jahr.Value).Range("P6:AD6")

  'Wo soll die Formel hin?

  Set Dest = Worksheets("Dateneingabe").Range("AF6:AF55")

  'Die Referenzen in der normalen Formel...

  '  =MAX('DATEN 2016'!$P6:$AD6)

  'durch einfache Platzhalter ersetzen:

  Formel = "=MAX(@[Was])"

  'Dann die Platzhalter durch die referenzierten Zellen ersetzen

  Formel = Replace(Formel, "@[Was]", Was.Address(0, 0, External:=True))

  'Eintragen (FormulaLocal akzeptiert Formeln der installierten Sprache, Formula nur Englische!)

  Dest.Rows(1).FormulaLocal = Formel

  Dest.FillDown

  Exit Sub

Errorhandler:

  If Err.Source = "" Then Err.Source = Application.Name

  Debug.Print "Source     : " & Err.Source

  Debug.Print "Error      : " & Err.Number

  Debug.Print "Description: " & Err.Description

  If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _

      Err.Description & vbNewLine & vbNewLine & _

      "Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then

    Stop 'Press F8 twice

    Resume

  End If

End Sub

Answer 2

Sheets("Jahr").Range("QN8:SW57").Value = Sheets("Jahr").Range("NF8:PO57").Value

Funktioniert mit value aber nicht mit Formats oder ValuesAndNumberFormats.

Logisch, das Range-Objekt kennt weder "Formats" noch "ValuesAndNumberFormats", sowas gibt es in Excel nicht.

Bitte keine Ratespiele, das macht niemandem Spaß. Ich denke mal Du beziehst Dich auf

xlPasteValuesAndNumberFormats

was ein mögliches Argument für Range.PasteSpecial wäre und in der Hilfe gibt es dazu ein Beispiel.

Zu den Formeln... ich weiß das dieses Gebastel mit ""&&&"&"&" überall vorgeschlagen wird, ich sag nur:

Ganz grausig, das machen nur Amateure.

Außerdem vergisst Du einen Grundsatz den ich Dir schon gezeigt hatte: Immer ordentlich die Range-Objekte referenzieren. Das sieht zwar im erstem Moment umständlich aus und man meint man kann sich schneller was zurechtpfuschen... spätestens wenn man nach ein paar Wochen in den Code kuckt wird einem klar was für Murks man gemacht hat. Naja, Deine Entscheidung.

Die Idee aus einer Tabelle was zu lesen und daraus dann eine Formel zu basteln ist leider auch weit verbreitet, ich würde sowas im Leben nicht machen! Schon beim geringsten Tippfehler bricht das ganze Kartenhaus zusammen.

Schau Dir den Code unten an, ist IMHO viel einfacher, besser nachvollziehbar, leicht zu ändern, man kann Fehler besser eingrenzen...

Andreas.

Sub Test()

  Dim Jahr As Range, Was As Range, Dest As Range

  Dim Formel As String

  'Bei sowas kann eine Menge Murks passieren, daher:

  On Error GoTo Errorhandler

  'Wo ist das Jahr?

  Set Jahr = Worksheets("Parameter").Range("A56")

  'Wo ist der zu berechnende Bereich?

  Set Was = Worksheets("Daten " & Jahr.Value).Range("P6:AD6")

  'Wo soll die Formel hin?

  Set Dest = Worksheets("Dateneingabe").Range("AF6:AF57")

  'Die Referenzen in der normalen Formel...

  '  =MAX('DATEN 2016'!$P6:$AD6)

  'durch einfache Platzhalter ersetzen:

  Formel = "=MAX(@[Was])"

  'Dann die Platzhalter durch die referenzierten Zellen ersetzen

  Formel = Replace(Formel, "@[Was]", Was.Address)

  'Eintragen (FormulaLocal akzeptiert Formeln der installierten Sprache, Formula nur Englische!)

  Dest.FormulaLocal = Formel

  Exit Sub

Errorhandler:

  If Err.Source = "" Then Err.Source = Application.Name

  Debug.Print "Source     : " & Err.Source

  Debug.Print "Error      : " & Err.Number

  Debug.Print "Description: " & Err.Description

  If MsgBox("Error " & Err.Number & ": " & vbNewLine & vbNewLine & _

      Err.Description & vbNewLine & vbNewLine & _

      "Enter debug mode?", vbOKCancel + vbDefaultButton2, Err.Source) = vbOK Then

    Stop 'Press F8 twice

    Resume

  End If

End Sub

Answer 3

Hallo Andreas,

vielen Dank für das ausführliche Gegenstück.

Den Errorhandler muss ich mir in Ruhe näher ansehen; habe verstanden das dies eine Prüfung ist und dann das Programm auch korrigiert, wenn Murks passiert.

Wenn ich meine Anwendung irgendwann später analysieren möchte, ist dies weitaus unübersichtlicher und unverständlicher; habe ich auch verstanden. Dein Gegenstück ist später viel einfacher nachzuvollziehen!

Als Anfänger habe ich mich halt einfach durchgemurkst - ganz schön lange!

Nochmals vielen für die nächste Lektion.

Gruß

Michael

Answer 4

Hallo Andreas,

und wieder mal geholfen. Vielen Dank und alles andere muss ich mir wieder in Ruhe mit deiner prima Erklärung zur Gemüte führen. Da ich am Sonntag noch vorankommen mußte, habe ich solange gemurkst., bis es zumindest funktioniert hat.

Hier meine Lösung:

Dim myStr As String

myStr = "'DATEN " & Sheets("PARAMETER").Range("A56") & "'!"

'Berechnung Formel Max einfügen in "DATENEINGABE" AF6:AF55

Sheets("DATENEINGABE").Range("AF6").Formula = "=MAX(" & myStr & "P6:AD6)"

Sheets("DATENEINGABE").Range("AF6").AutoFill Destination:=Range("AF6:AF55"), Type:=xlFillDefault

Danke und Gruß

Michael

Answer 5

Hallo Michael,

war die Antwort von Andreas Killer hilfreich?

Falls ja, markiere diese bitte als hilfreich - falls nein, lass es uns wissen somit kann dir vielleicht ein weiterer Beitrag der Community helfen oder vlt. sogar das Entwicklerforum MSDN.

Bitte halte uns auf dem Laufenden!