Office: dynamischer Zellbezug in VBA-Formel

Hallo,

vor einiger Zeit konnte ich, auch durch die hilfreiche Unterstützung von Euch, eine recht umfangreiche Excelprogrammierung abschließen und arbeite seitdem damit.

Inzwischen muss ich aber nachbessern, da manche Fehlerquellen erst in der Praxis auftauchen.



Gegeben:

Einträge in meinem Hauptblatt werden mittels einer UserForm (Eingabemaske) gemacht - diese Einträge bilden die Grundlage für alle anderen Blätter und werden auch dorthin übernommen.

in Spalte A (ab Zeile 6) steht immer ein Datum (tt.mm.jjjj)

in Spalte N steht der entsprechende DATWERT


das funktioniert soweit alles.




Da ich auch nur ein Mensch bin und ab und zu Fehler bei der Eingabe mache (die ich dann manuell in der Liste korrigiere), habe ich einen Command-Button (mit entsprechender VBA im Hintergrund) um die komplette Datei (mehrere aufeinander bezogene Blätter) zu aktualisieren.

Nur eins habe ich vergessen (und deshalb jetzt Probleme):

Der DATWERT in Zeile N wird bei der Eingabe automatisch durch ein Makro eingetragen und ändert sich dann nicht mehr.
Wenn ich aber in der Maske ein falsches Datum eingebe, und dieses später manuell in der Liste ändere, ändert sich der DATWERT aber nicht mit.

Also muss ich in das "Aktualisieren-Makro" auch dafür eine Funktion einbauen.

Ich finde nur leider die richtige Schreibweise nicht.

Vom Sinn her brauche ich dieses hier:

Code:

    For loZeile = 6 To loLetzte
        cells(loZeile, 14).FormulaLocal = "=DATWERT(cells(loZeile, 1))"
    Next loZeile

Aber natürlich kann ich "=DATWERT(cells(loZeile, 1))" so nicht schreiben.
Könnt Ihr mir da bitte weiterhelfen?

 

Hallo Achim,

warum arbeitest Du mit Datwert? Warum überhaupt eine zweite Spalte? Schreibe doch in Spalte A Das Datum als Datum und nicht als Text. Range("A1")=Cdate(TExbox1)

Gruß Hajo

 

Hallo Hajo,

danke für Deine Antwort.

Die Spalte mit dem DATWERT benutze ich über mehrere Blätter zum zuordnen.
Möglicherweise ginge das alles auch einfacher...
aber ich habe beim erstellen der Datei keine andere Lösung gefunden, bzw. ans laufen bekommen.
Ist halt mein erstes Mal mit VBA.

Deshalb, wenn es eine Möglichkeit gibt die von mir gewünschte Schleife ans laufen zu bekommen, wäre dies für mich die einfachste Lösung.

Wenn ich von dem DATWERT auf eine andere Lösung umschwenke, müßte ich sehr sehr viel umändern.
Das würde ich gerne vermeiden.

Die Formatierung mit CDATE kannte ich schon. Trotzdem danke für den Tipp.

 

Weiß denn niemand Rat?
Nichtmal ob das überhaupt funktioniert?

Selbst die Aussage:"Kannste vergessen, was Du vorhast geht so nicht.", wäre schon hilfreich.

 

Der... ähm... nunja... "einfachste" Weg wäre es, wenn Du eine kleine Funktion basteln würdest, die Dir die Angabe (x|y) in die Excel-Schreibweise z.B. "A1" umwandeln würde.

Für die Zeilen ist das ja kein Thema, der y-Wert ist es ja direkt. Für die Spalten braucht's aber etwas mehr, die Zahlen in Buchstaben umzuwandeln.

Das folgende in ein Modul:

Code:

Option Explicit

Private Function Num2Col(x&) As String

    Dim r As String
    Dim n1 As Long
    Dim n2 As Long

    r = ""

    'If (x >= 1) And (x <256> 0) Then
            r = r & Chr$(64 + n1)
        End If
        r = r & Chr$(64 + n2)

    'End If

    Num2Col = r

End Function

Public Function XY_zu_A1(x&, y&) As String

    Dim r As String

    r = ""

    If (x >= 1) And (x <256>= 1) And (y <= 65536) Then

        r = Num2Col(x) & y

    End If

    XY_zu_A1 = r

End Function

und dann:
<TABLE border="1" cellspacing="0" cellpadding="3"><TR><TD width="20" bgcolor="#c0c0c0">.</TD><TD width="100" align="center" bgcolor="#c0c0c0">A</TD><TD width="100" align="center" bgcolor="#c0c0c0">B</TD><TD width="100" align="center" bgcolor="#c0c0c0">C</TD><TD width="100" align="center" bgcolor="#c0c0c0">D</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">1</TD><TD bgcolor="#FFFFFF">=xy_zu_a1(1;1)</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">2</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">=xy_zu_a1(26;3)</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">3</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">=xy_zu_a1(27;5)</TD><TD bgcolor="#FFFFFF">.</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">4</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">=xy_zu_a1(256;65536)</TD></TR></TABLE>
sollte ergeben:
<TABLE border="1" cellspacing="0" cellpadding="3"><TR><TD width="20" bgcolor="#c0c0c0">.</TD><TD width="100" align="center" bgcolor="#c0c0c0">A</TD><TD width="100" align="center" bgcolor="#c0c0c0">B</TD><TD width="100" align="center" bgcolor="#c0c0c0">C</TD><TD width="100" align="center" bgcolor="#c0c0c0">D</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">1</TD><TD bgcolor="#FFFFFF">A1</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">2</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">Z3</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">3</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">AA5</TD><TD bgcolor="#FFFFFF">.</TD></TR><TR><TD align="center" bgcolor="#c0c0c0">4</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">.</TD><TD bgcolor="#FFFFFF">IV65536</TD></TR></TABLE>
Beachte dabei bitte: Die Grenz-Abfragen lassen Maximal-Werte von 256 Spalten und 65536 Zeilen zu. Ab x2007 sind die Limits da wohl bedeutend höher, aber ich weiß nicht, wie die Spalten-Bezeichnungen ab 703 lauten. Geht's da mit 3 Buchstaben AAA weiter nach ZZ?

Deine Zeile

Code:

cells(loZeile, 14).FormulaLocal = "=DATWERT(cells(loZeile, 1))"

würde sich dann ändern in

Code:

cells(loZeile, 14).FormulaLocal = "=DATWERT(" & xy_zu_a1(1,lozeile) & ")"

Gruß, Michael

 

gaaaaanz lieben Dank miriki

ich werde Deine Lösung die nächsten Tage (vermutlich am WE) ausprobieren.

Hatte befürchtet das es derart "kompliziert" werden würde (falls überhaupt möglich) und habe mir inzwischen schon andere "Umwege" gesucht.
Dabei habe ich (mal wieder) festgestellt, dass das Leben ja soo einfach sein kann, wenn man nur weiß wie es geht.

Hajo hatte bereits den richtigen Tipp gegeben, ich habe aber erst heute Abend herausgefunden wie das umzusetzen wäre:

In Spalte A wird bei mir immer das Datum gespeichert.
Aber die Zellen waren im Standartformat. (mein Fehler)
Habe das jetzt geändert in: "dd/mm/yyyy"

Dadurch kann ich den DATWERT ganz einfach erhalten indem ich den Zellwert von cells(loZeile, 1) in cells(loZeile, 14) kopiere, wobei Spalte 14 im Zahlenformat ("0") ist.

Und wenn ich das vor ein paar Monaten gewusst/geschnallt hätte, könnte ich mir den Kram vermutlich ganz sparen und mit all den anderen Makros/Zwecken auch direkt auf Spalte A Bezug nehmen und das Datum auswerten.

Das ist halt mein Problem von Anfang an gewesen: totaler VBA-Anfänger soll recht umfangreiche Anwendung entwickeln und hat niemanden (ausser das WWW und Euch) um Fragen zu stellen/Anleitung zu bekommen.

Ich hoffe Du bist jetzt nicht sauer, auf jeden Fall hast Du Dir die Arbeit nicht umsonst gemacht.
Die Lösung kann bestimmt mal jemand brauchen, und ich werde sie mir auch abspeichern, zum Lernen und evtl. mal Anwenden.
Variable Zellbezüge in Formeln via Makro sind praktisch wenn man so eine Lösung parat hat. *zwinker*


Übrigens, ich habe nachgeschaut:
nach Spalte ZZ geht es bei mir mit AAA - AAB - AAC ... weiter.


Nochmal ganz lieben Dank für deine Mühe, wie gesagt, ich werds ausprobieren und sage Bescheid obs klappt.

 

ähm, ist mir bei genauerer Betrachtung gerade aufgefallen:
kann die Private Function so überhaupt funktionieren?
"end if" kommt 2x vor
vor "if" und vor "end if" steht ein " ' ", ich denke die müssten weg, sonst wirds ein Kommentar
und was sind "n1" und "n2"? Beides wird addiert, aber wo wird festgelegt was das ist? Oder ist das in Excel bereits vorgegeben?

 

´
Oh, keine Bange. ;-) Die Routine hab ich mir mal vor 3 oder so Jahren erstellt, weil ich sie auch alle Nase lang in meinen Codes brauchte.

Was mich nur wundert: Irgendwie ist das Ding plötzlich etwas abgeschnitten. Da fehlt was. Hier nochmal der Versuch, es komplett zu posten:

Code:

Option Explicit

Private Function Num2Col(x&) As String

    Dim r As String
    Dim n1 As Long
    Dim n2 As Long

    r = ""

    'If (x >= 1) And (x <= 256) Then

        n1 = (x - 1) \ 26
        n2 = ((x - 1) Mod 26) + 1

        If (n1 > 0) Then
            r = r & Chr$(64 + n1)
        End If
        r = r & Chr$(64 + n2)

    'End If

    Num2Col = r

End Function

Public Function XY_zu_A1(x&, y&) As String

    Dim r As String

    r = ""

    If (x >= 1) And (x <= 256) And (y >= 1) And (y <= 65536) Then

        r = Num2Col(x) & y

    End If

    XY_zu_A1 = r

End Function

Gruß, Michael

 

Ah, ok, HTML war nicht deaktiviert. Aber ich dachte, in "code"-Blöcken wäre das kein Problem. Offensichtlich doch...

(Das 'kleiner gleich' hat ihn gestört, hielt er wohl für ein öffnendes HTML-Tag.)

Gruß, Michael