Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Makro zur Aktualisierung von Daten





Frage

Hallo zusammen, ich würde gerne ein Makro für folgendes Problem einsetzen: In einer Reportingdatei werden Daten aus unterschiedlichen Quellen per s-verweis "gesammelt". Die Werte werden auf Tagesbasis für ein ganzes Jahr dokumentiert. Um für diese Masse an Daten nicht unnötig Speicherplatz zu verschwenden, möchte ich die Formeln mit den verweisen nur bei Bedarf in die entsprechenden Zellen schreiben lassen. D.h. den Ablauf des Makros stelle ich mir in etwa so vor: 1. In einem Dialogfeld gibt der User an, für welchen Zeitraum er aktualisieren möchte (z.B. vom 01.01.2008 bis 20.01.2008). 2. Die in einem Hilsbereich gespeicherten Formeln werden in die relevanten Tagesspalten kopiert 3. Die Werte werden aus der externen Datenquelle aktualisiert 4. Die soeben aktualisierten Daten werden als feste Werte gespeichert. Kann mir als Makro-Neuling jemand bei dem beschriebenen Problem helfen? Danke! Nicole

Antwort 1 von Beverly

Hi Nicole,

weshalb erst die Formeln eintragen, wenn du die Werte gleich übernehmen kannst? Zeichne den Code dazu mit dem Makrorekorder auf.

Bis später,
Karin

Antwort 2 von Nicole1

Hallo Karin,

die Daten kommen aus einer Vielzahl unterschiedlicher Quellen und müssen unterschiedlichste Bedingungen zur Übernahme erfüllen.
Sollten sich daran irgendwelche Dinge ändern (die Wahrscheinlichkeit hierfür ist relativ hoch), wäre es für den User leichter, die entsprechende Formel in der Hilfszeile zu ändern als Änderungen im Makro vorzunehmen.

Hast du eine Idee für die Umsetzung?

Danke und Gruß,
Nicole

Antwort 3 von Beverly

Hi Nicole,

man kann schon Formeln per Code in Zellen eintragen - z.B. auf diesem Weg

Sub formeln_eintragen()
    Range("C1").FormulaLocal = Range("A1").FormulaLocal
End Sub

In A1 steht die Formel und wird als Formel in C1 eingetragen.

Bis später,
Karin

Antwort 4 von Nicole1

Hi Karin,

danke, das hilft mir schon einmal.

Weißt du, wie ich jetzt die Auswahl des Zeitraums hinbekomme? C1 aus dem Beispiel würde ich gerne als variablen Bereich definieren, d.h. der User gibt in einer Input-Box z.B. vom 01.01.2008 bis 20.01.2008 an, und dann wird die Formel aus A1 in C1:V1 kopiert.


Bis dann,
Nicole

Antwort 5 von Beverly

Hi Nicole,

vielleicht ist anstelle der Inputbox ein UserForm besser, da könntest du mithilfe von 2 ComboBoxen das Start und das Enddatum auswählen lassen. Du müsstest dann die Differenz zwischen den beiden Datumswerten bilden, was ja die Anzahl an Tagen ist
Sub formeln_eintragen()
    Dim inDifferenz As Integer
    inDifferenz = Range("B3") - Range("B2")
    Range(Cells(1, 3), Cells(1, inDifferenz + 2)).FormulaLocal = Range("A1").FormulaLocal
End Sub

Die +2 ergibt sich daraus, dass erst ab Spalte C eingetragenw erden soll.
Ich habe hier mal der Einfachheit halber das Startdatum in B2 und das Enddatum in B3 geschrieben.

Bis später,
Karin

Antwort 6 von Nicole1

Hallo Karin,

UserForm ist auch ok, danke für den Tipp.

Ich hab jetzt soweit nachvollziehen können, was die einzelnen Befehle des Makros bedeuten. Nun ist es so, dass der Zeitraum nicht zwangsläufig mit dem 01.01. anfängt. Ich muss also auch die Startzelle variabel errechnen. Kannst du mir erklären, wie ich das mache?

Danke und Gruß,
Nicole

Antwort 7 von Beverly

Hi Nicole,

mir ist leider dein Tabellenaufbau nicht klar. Ich weiß also nicht, was wann wie wohin kopiert werden soll und kann deshalb nur auf konkrete Fragen antworten.
Sind es mehrere Monate? Sind die Daten Zeilenweise oder Spaltenweise angeordnet? Wodurch ergibt sich also die Startzelle?

Bis später,
Karin

Antwort 8 von Nicole1

Hallo Karin,

in der endgültigen Datei ist der Aufbau wie folgt:

Die Tage sind in den Zeilen angeordnet (von Zelle A7 bis Zelle A372).

In den Spalten stehen ab Zeile 5 die zu kopierenden Formeln (Zelle J5 bis Zelle IG5).

Der mit den Formeln zu füllende Bereich kann also von J7 (01.01.2008; erste Kennzahl) bis zu IG372 (31.12.2008; letzte Kennzahl) reichen.

Für den Bereich, in den die Formeln kopiert werden sollen gilt folgendes: Es werden immer die Spalten J bis IG gefüllt. Die relevanten Zeilen werden über den ausgewählten Zeitraum definiert (Dokumentation von... bis... wie im Beispiel in Zelle B2 und B3).

Zur Verdeutlichung zwei Beispiele:
Zeitraum 1: 01.01.2008 - 20.01. 2008 Formeln aus J5:IG5 werden in J7:IG26 kopiert.
Zeitraum 2: 15.02.2008-22.02.2998: Formeln aus J5:IG5 werden in J52:IG57 kopiert.

Ich hoffe, das ist einigermaßen verständlich beschrieben?!

Grüße,
Nicole

Antwort 9 von Beverly

Hi Nicole,

versuche es so
Sub formeln_eintragen()
    Dim raZelleStart As Range
    Dim raZelleEnde As Range
    With Worksheets("Formeln")
        Set raZelleStart = .Columns(1).Find(.Range("B2"))
        Set raZelleEnde = .Columns(1).Find(.Range("B3"))
        Range(Cells(raZelleStart.Row - 2, 10), Cells(raZelleEnde.Row - 2, 243)).FormulaLocal = .Range(.Cells(raZelleStart.Row - 2, 10), .Cells(raZelleEnde.Row - 2, 243)).FormulaLocal
    End With
End Sub

Ich habe wiederum das Anfangs- und das Enddatum in B2 bzw. B3 eingetragen.

Bis später,
Karin

Antwort 10 von Nicole1

Hallo Karin,

wenn ich das Makro ausführe (ich habe lediglich "fomeln" durch den tatsächlichen Namen des Arbeitsblatts ersetzt) bekomme ich den Laufzeitfehler 91: Objektvariable oder With-Blockvariable nicht festgelegt.
Hast du eine Idee, woran das liegt?

Grüße,
Nicole

Antwort 11 von Beverly

Hi Nicole,

und welche Codezeile bzw. welcher Teil wird dabei markiert?

Bis später,
Karin

Antwort 12 von Nicole1

Hi Karin,

es wird die letzte Zeile

Range(Cells(raZelleStart.Row - 2, 10), Cells(raZelleEnde.Row - 2, 243)).FormulaLocal = .Range(.Cells(raZelleStart.Row - 2, 10), .Cells(raZelleEnde.Row - 2, 243)).FormulaLocal
markiert.

Bis später,
Nicole

Antwort 13 von Beverly

Hi Nicole,

raZelleStart und raZelleEnde kann kein Wert zugewiesen werden. Hast du die Werte auch in B2/B3 eingetragen oder rufst du sie von einem UserForm auf? Wenn es aus dem UserForm ist, musst du CDate(ComboBox1) - Name des Kombinationsfeldes entsprechend anpassen - schreiben, da der Inhalt eines Kombinationsfeldes Text ist.

Bis später,
Karin

Antwort 14 von Nicole1

Hi Karin,

bisher habe ich der Einfachheit halber komplett auf die User-Form verzichtet und das Start- bzw. Enddatum in B2 bzw. B3 fest eingetragen. Hast du noch eine andere Idee, warum kein Wert zugewiesen werden kann?

Wofür steht denn in der letzten Zeile jeweils der Teil .Row - 2. Die 10 bzw. 234 kann ich mir als Start- bzw. Endspalte erklären, aber wofür steht bei den Zeilen die 2?

Danke und Gruß,
Nicole

Antwort 15 von Beverly

Hi Nicole,

-2 deshalb, weil deine Datumswerte in Zeile 7, die Formeln jedoch in Zeile 5 beginnen (s. dein Beitrag vom 03.04. 15:06). Oder beginnen die Formeln ebenfalls in Zeile 7? Dann kannst du die -2 weglassen. Das hat aber nichts mit dem Fehler zu tun.

Kann es sein, dass Spalte A kein Standard-Datumsformat auf weist?

Bis später,
Karin

Antwort 16 von Nicole1

Hi Karin,

stimmt, mit dem Datumsformat gab es noch Probleme.

Hab die endgültige Lösung jetzt gefunden:

Sub Daten_aktualisieren()
Dim raZelleStart As Range
Dim raZelleEnde As Range
With Worksheets("Data input_new")
Set raZelleStart = .Columns(4).Find(.Range("B2"))
Set raZelleEnde = .Columns(4).Find(.Range("B3"))
Range(Cells(raZelleStart.Row, 10), Cells(raZelleEnde.Row, 243)).FormulaLocal = .Range(.Cells(raZelleStart.Row, 10), .Cells(raZelleEnde.Row, 243)).FormulaLocal
End With
Dim zStart As Long
Dim zEnde As Long
Dim sStart As Integer
Dim sEnde As Integer
Dim strFormula As String

zStart = raZelleStart.Row
zEnde = raZelleEnde.Row
sStart = Range("J5").Column
sEnde = Range("II5").Column

'gehe alle Zeilen durch
For i = zStart To zEnde

'gehe alle Spalten in aktueller Zeile durch
For j = sStart To sEnde
'lese Formel aus
strFormula = ActiveSheet.Cells(5, j).Formula
'tausche "5" gegen aktuelle zeile-nr
strFormula = Replace(strFormula, "5", CStr(i))
'schreibe die Formel in die Zelle rein.
ActiveSheet.Cells(i, j).Formula = strFormula
Next
Next

End Sub


Danke für deine Hilfe!!

Nicole