Office: (Office 2013) Funktion TAG(x) variabel gestallten?

Hallo zusammen,

kurze Frage. Kann man den Bezug bei der Formel =TAG(variabel) gestallten, wobei die variable in der gleichen Zeile 4 Zellen vorher liegend ist(z.B. Zelle Qx =TAG(Nx)? Ich lasse die Formel mit VBA eintragen weiss aber vorher nicht ab welcher Zeile und müsste das iwie einbauen.
Danke
Patrick

 

Hallo Patrick,

befindet sich die aktive Zelle in Zelle $F$32 und gibt man im VBA-Direktbereich-Fenster Folgendes ein:

Code:

?activecell.Address, activecell.Address(False,False,xlR1C1),activecell.Address(True,True,xlR1C1)

erhält man als Ausgabe

Code:

$F$32         R[31]C[5]     R32C6

und hatte man im Arbeitsblatt in dieser aktiven Zelle folgende Formel eingegeben:
=TAG(B32)
erhält man für diese aktive Zelle im Direktbereich-Fenster:

Code:

?activecell.Formula
[COLOR="#0000CD"]=DAY(B32)[/COLOR]
?activecell.FormulaR1C1
[COLOR="#0000CD"]=DAY(RC[-4])[/COLOR]
?activecell.FormulaLocal
[COLOR="#0000CD"]=TAG(B32)[/COLOR]
?activecell.FormulaR1C1Local
[COLOR="#0000CD"]=TAG(ZS(-4))[/COLOR]

Umgekehrt kann man der passenden FORMULA-Eigenschaft die entsprechende Formel zuweisen:

Code:

activecell.Formula = "=DAY(B32)"
activecell.FormulaR1C1 = "=DAY(RC[-4])"
activecell.FormulaLocal = "=TAG(B32)"
activecell.FormulaR1C1Local = "=TAG(ZS(-4))"

Es wird hier jeweils ein deutschsprachiges Excel vorausgesetzt, insbesondere bei den ...local-Versionen. Man sollte aber unter VBA diese ...local-Formelversionen vermeiden. Im Arbeitsblatt werden dann eh die per VBA erzeugten Formeln in der lokalen Version angezeigt.

 

Hallo Anton,

Danke für die Hilfe. An Activecell habe ich wieder gar nicht gedacht... werde mal mit rumexperimentieren vielleicht bekomme ich da so zum laufen.
Gruss Patrick

 

Hallo Patrick,

statt "ActiveCell" kann man jede andere zweckdienliche Bezugsfunktion einsetzen, zB.
will ich in Zelle F32 die geplante Formel speichern, kann ich folgende Formelzuweisung einsetzen:
Range("F32").FormulaR1C1 = "=DAY(R[-2]C[-4])"
Diese Zuweisung erzeugt im aktiven Arbeitsblatt in Zelle F32 folgende Formel:
=TAG(B30)
denn, ausgehend von der Formelzelle, liegt die Zelle B30 zwei Zeilen oberhalb und 4 Spalten links von der Formelzelle F32.
Möchte man diese relative Zugriffsform innerhalb der Formel noch flexibler gestalten, kann man sich für den Zeilenoffset bzw. Spaltenoffset eigene Variablen anlegen, zB.:
Dim ZlOff As Long, SpOff As Long
ZlOff = -2
SpOff = -4
Range("F32").FormulaR1C1 = "=DAY(R[" & ZlOff & "]C[" & SpOff & "])"
Diese Formelzuweisung produziert exakt das gleiche Ergebnis wie die erstgenannte Formelzuweisung, nur ist sie wesentlich flexibler. Denn ich brauche nur die beiden Variablen ZlOff bzw. SpOff mit anderen Werten belegen und schon holt sich die "TAG(..)"-Funktion den Datumswert von einer anderen Zelle in ihrer Umgebung.

Bequem ist die relative Zellangabe insbesondere dann, wenn ich entweder auf die gleiche Zeile oder Spalte Bezug nehmen soll:
Range("F32").FormulaR1C1 = "=DAY(RC[" & SpOff & "])"
Range("F32").FormulaR1C1 = "=DAY(R[" & ZlOff & "]C)"
Die erste Formelzuweisung liefert im aktiven Arbeitsblatt in Zelle F32 die Formel =TAG(B32) und die zweite Formelzuweisung die Formel =TAG(F30).

Ist die fixe Zellangabe für die Formelzelle zu starr, kann man statt "RANGE(...)" auch zB. "CELLS(..)" einsetzen:
statt RANGE("F32")... kann man schreiben:
Cells(32,6).FormulaR1C1 = "=DAY....", wobei ich 32 bzw. 6 wieder in Variable stecken kann:
Dim Zeile As Long, Spalte As Long
Dim ZlOff As Long, SpOff As Long
Zeile = 32: Spalte = 6
ZlOff = -2: SpOff = -4
Cells(Zeile, Spalte).FormulaR1C1 = "=DAY(R[" & ZlOff & "]C[" & SpOff & "])"


In letztgenannter DAY-Formel setzte ich die relative Bezugsangabe ein. Lasse ich jedoch die eckigen Klammern weg, nehme ich eine absolute Bezugsangabe vor - man kann das sogar bezüglich Zeilen und Spalten unterschiedlich durchführen, zB.:
Dim Zeile As Long, Spalte As Long
Dim ZlDt As Long, SpOff As Long
Zeile = 32: Spalte = 6
ZlDt = 2: SpOff = -4
Cells(Zeile, Spalte).FormulaR1C1 = "=DAY(R" & ZlDt & "C[" & SpOff & "])"

was in diesem Fall in der Zelle F32 des aktiven Arbeitsblattes die Formel =TAG(B$2) produzieren würde.

 

Hallo Anton,

Danke vielmal für diese lehrreiche Erklärung. Das wird mein Problem lösen. Mache mich gleich mal ans Werk. Ich wünsche einen
Angenehmen Wochenstart.
Gruss
Patrick

 

Hallo Zusammen, hallo Anton,
Hier mal noch mein Ergebnis:

Code:

Sub FormelZelle()
'''Trägt eine Formel in die Zielzelle ein mit einer anderen Zelle als relativem Bezug


'Variablen bestimmen
Dim aBlatt As Worksheet 'legt aBlatt als Arbeitsmappe fest
Dim varDatum As Variant 'Variable für das Datum
Dim i As Long 'Zählervariable


Set aBlatt = ThisWorkbook.Worksheets("Stoermeldearchiv0_m") 'aBlatt erhält Wert


For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'Zähler zum durchsuchen des Benutzten Bereichs
    varDatum = Cells(i, 14).Value 'varDatum wird Zeile i (variabel) und Spalte N zugewiesen


    If Not varDatum Like "*M" Then 'wenn varDatum nicht PM/AM enthält dann...
        Cells(i, 18).FormulaR1C1 = "=DAY(R[]C[-4])" 'Zellformel mit relativem Bezug einfügen
        Cells(i, 19).FormulaR1C1 = "=MONTH(R[]C[-5])"
        Cells(i, 20).FormulaR1C1 = "=YEAR(R[]C[-6])"
        Cells(i, 21).FormulaR1C1 = "=HOUR(R[]C[-7])"
        Cells(i, 22).FormulaR1C1 = "=MINUTE(R[]C[-8])"
        Cells(i, 23).FormulaR1C1 = "=SECOND(R[]C[-9])"
    End If
Next i

End Sub                                                                                                                                                                                                    

Dann benutze ich das:

HTML:

Sub DatumZusammenbau()
'''Setzt ein Datum aus einzelnen Zahlen zusammen

'Variablen bestimmen
Dim i As Long
Dim arr
Dim L As Long



For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'For Next schleife mit Zähler zumm durchsuchen des Benutzten Bereichs
    arr = Intersect(Range("R2").CurrentRegion, Range("R:T")) 'Bildet einen array aus den einzelnen Zahlen im Bereich R-T
    ReDim Out(1 To UBound(arr), 1 To 1) 'Redimensioniert die Outputzelle variabel
    For L = 2 To UBound(arr)
        Out(L, 1) = Format(DateSerial(arr(L, 3), arr(L, 2), arr(L, 1)), "MM.DD.YYYY") 'Setzt das Datum mit DateSerial aus den Excelwerten zusammen
    Next
    Range("X1").Resize(UBound(Out)) = Out 'Ausgabe in Spalte X
Next i



End Sub

und das:

Code:

Sub ZeitZusammen()
'''Setz die Zeit neu zusammen

'Variablen festlegen
Dim varDatum As Variant
Dim i As Long


For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'For Next schleife mit Zähler zumm durchsuchen des Benutzten Bereichs von unten
    varDatum = Cells(i, 14).Value
    
    If Not varDatum Like "*M" Then 'Sucht mit TimeSerial einzelne Excelwerte und setzt Sie zu einer Uhrzeit zusammen
        Cells(i, 25) = TimeSerial(Cells(i, 21).Value, Cells(i, 22).Value, Cells(i, 23).Value)
        Cells(i, 25).NumberFormat = "hh:mm:ss"
    
    End If

Next

End Sub

und am Ende das um mein Datum wieder zusammenzubauen:

Code:

Sub AllesZusammen()
''' Fügt einzelnen Werte wieder zu Datum und Uhrzeit zusammen und gibt sie als Wert aus

'Variablen zuweisen
Dim varWert As Variant
Dim i As Long


For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row 'For Next Schleife zum durchsuchen belegter Zellen von unten
    varWert = Cells(i, 18).Value
    
    If varWert <= 12 And Not varWert = "" Then 'Wenn Wert kleiner/gleich 12 und Zelle nicht leer dann...
        Cells(i, 26).FormulaR1C1 = "=(R[]C[-2])+(R[]C[-1])" 'Formelergebnis in Zelle zusammenfügen mit +
        Cells(i, 27) = Cells(i, 26).Value 'Extrahieren des Formelwerts
        Cells(i, 27).NumberFormat = "DD.MM.YYYY hh:mm:ss" 'Formatieren der Zelle
    Else
        Cells(i, 27) = " " 'sonst mach Zelle leer
        
    End If
Next i

End Sub

Und das alles nur um den Tag auf Monat und den Monat auf Tag umzustellen. :D

P.S. Wie bekomme ich die Codefenster breiter?