Office: Exel Formel über VBA für ganze Spalte anwenden

Hallo zusammen!

Um mal mit einem hier beliebten satz anzufangen: Ich habe nicht viel Ahnung (nahezu null würde ich mal so sagen) von VBA. Im Rahmen eines Projektes habe ich eine Datei angelegt, für die ich -mit viel Hilfe- mal das ein oder andere über VBA eingerichtet habe.
Nun würde ich gerne noch eine längere Formel über VBA einfach für eine ganze Spalte (Spalte A ab Zeile 6 wenn´s geht) gelten lassen.
Bisher habe ich Formel von Hand reinkopiert, aber die Tabelle wächst stetig und das Ende ist nicht absehbar.
Ich habe es mal so probiert:

Tabelle1.Cells(6,1).Formula="=IF((1*LEFT(C304;4)=1*LEFT($A$4;4));"im Zeitraum";IF(C304=0;"Zeitraum unbekannt";"nicht im Zeitraum"))"

Aber 1. bin ich unsicher wie ich das wo in meinen jetzigen Code einbauen kann, der so aussieht:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngBereich As Range
Dim wert_old As Variant
Dim wert_new As Variant
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
On Error GoTo beenden

Set rngBereich = Application.Union(Columns("A"), Columns("D"), Columns("F"), Columns("H:K"), Columns("V"), Columns("AB"), Columns("AK"), Columns("AM"))

If Not Application.Intersect(Target, rngBereich) Is Nothing Then
Application.EnableEvents = False
If Target.Column = 6 Then
Cells(Target.Row, 7).Resize(1, 5).Value = IIf(Target = 0, 0, "")
Cells(Target.Row, 23).Value = IIf(Target = 0, 0, "")
Cells(Target.Row, 34).Resize(1, 5).Value = IIf(Target = 0, 0, "")
Application.EnableEvents = True

If Target.Column = 40 Then
Cells(Target.Row, 41).Resize(1, 3).Value = IIf(Target = 0, 0, "")
Application.EnableEvents = True
End If

Exit Sub
End If
If Not Application.Intersect(Target, Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then
wert_new = Target.Value
Application.Undo
wert_old = Target.Value
Target.Value = wert_new
If wert_old "" Then
Target.Value = wert_old & ", " & wert_new
'---entfernt die Null falls vorhanden
If Left(Target.Value, 1) = 0 Then Target.Value = Mid(Target.Value, 3, 6 ^ 6)
'------
End If
End If
End If
beenden:
Application.EnableEvents = True
End Sub

Und 2. hat er mir bei einem ersten Versuch gesagt er würde das Ende der Anweisung hier erwarten:

Tabelle1.Cells(6,1).Formula="=IF((1*LEFT(C304;4)=1*LEFT($A$4;4));"im Zeitraum";IF(C304=0;"Zeitraum unbekannt";"nicht im Zeitraum"))"


Ich würde mich freuen wenn ihr mir weiterhelfen könnt!

Lieben Gruß,

Papierstudi

:)

 

Zur besseren Erkennbarkeit hier der Code nochmal eingerückt:

Code:

 

Hallo Papierstudi,

genau das fällt mir auch schwer zu raten: Beschreib doch mit Worten, wann die Formel eingetragen werden soll. Bei einem Eintrag in Spalte 6? Habe ich mal so angenommen.
Die Antwort auf Frage 2 ist dann ebenfalls im Code zu ersehen: Die Formel muss us-amerikanisch geschrieben werden: Neben den englischen Formelnamen kommt anstelle des Semikolons als Argumenttrenner ein Komma. Hochkommata sind, wie beim Zusammenfügen von String üblich, zu doppeln, wenn Sie im String enthalten sein sollen.

Ich bin mir allerdings nicht sicher, was mit dem Bezug auf Zelle C304 passieren soll, wenn die Formel eingetragen wird: Bleibt der immer konstant oder soll der sich anpassen? Wenn letzteres: Wie?

Code:

Edit:
Als Alternative wird anstelle C304 die Target-Zeile verwendet:
Code:

Grüße
EarlFred

P.S.: "Zur besseren Erkennbarkeit hier der Code nochmal eingerückt" Prima! Ich war aber schon fertig, selbst dafür zu sorgen, daher nutz ich mal meine gewohnte Art und Weise *wink.gif*

 

Moin
Du musst den Text in der Formel in doppelte Gaensepfoten setzen.
Tabelle1.Cells(6, 1).Formula = "=IF((1*LEFT(C304,4)

 

Guten morgen!

Danke erstmal für eure Antworten!

Ich habe die Formel bisher immer von Hand in Spalte A eingetragen (dann entsprechend mit "wenn" anstatt "if"). Sie nimmt Bezug auf die Werte in Spalte 3 und vergleicht diese mit dem Eintrag im Suchfeld (Spalte A Zeile 4). Sie sollte idealerweise dann greifen /eingetragen werden, wenn ein Wert in der gleichen Zeile in Spalte C hinzugefügt wird. Auf diese Weise würde die Spalte A entsprechend mitwachsen.

-> Sorry! Ich habe den Code einfach aus einer beliebigen Zeile kopiert und nicht bedacht, dass ich das vielleicht besser noch abändern sollte. Der Bezug soll sich anpassen. Los geht´s ab Zeile 6, davor ist Überschriftenkram.

Lieben Gruß,

Papierstudi

 

Hallo Papierstudi,

ich suche erstmal nach einem "?", denn davor verbirgt sich erfahrungsgemäß das Fundament für das Gesuchte: Die Frage, auf die Du Dir eine Antwort erhoffst. Genau die vermisse ich. Stattdessen finde ich nur Information ohne Gehalt.

Heißt das nun, die 2. von mir vorgeschlagene Variante funktioniert? Funktioniert nicht? Du brauchst noch Hilfe? Wenn ja: Wo / wobei / womit / wozu / warum / weshalb / wer / wie / was? Du brauchst keine Hilfe mehr, alles klappt prima?

Grüße
EarlFred

 

Vielleicht so in der Art?
Funzt nur natürlich mal wieder nicht. :/

Code:

 

Moin
Probiers mal so.
Code:

gruss hary

 

Hallo Papierstudi,

heißt was? Fehlermeldung? Dann bitte Fehlerzeile, -nummer und -beschreibung. Ergebnis passt nicht? Warum?

Ich hatte doch Code vorgeschlagen - in 2 Varianten - und die Unterschiede beschrieben - und rot markiert, welche Zeilen alternativ formuliert werden müssen. Warum übernimmst Du den nicht? Was stimmt daran nicht? Was verstehst Du daran nicht?
Warum werden meine Rückfragen nicht beantwortet?

Grüße
EarlFred

 

\@Earl:
Meine Frage ist: Wie muss der Code aussehen und wo muss er hin, damit das, was ich möchte, funktioniert. Und was ich möchte ist, dass die Formel beim wachsen der Tabelle automatisch nach unten fortläuft, ohne dass ich sie reinkopieren muss oder in die noch fast leere Tabelle vorher bis Zeile 10000 einfügen muss.
Es gibt zwar angeblich bei Excel ein automatisches Fortsetzen von Formeln, das funktioniert aber meist nur für ein bis zwei Zeilen, dann tut Excel wieder nichts. Daher möchte ich es nun einfach über VBA als festen Befehl einfügen.

@Hary
Danke! Funzt aber leider noch nicht. Er zickt noch rum. Ich hänge mal die Datei an.
Erfassung Bestand 1 2014.xls
(Jezt musste ich tatsächlich Daten aus der Tabelle löschen, damit sie nicht zu groß zum hochladen ist...)

Gruß,

Studi

 

Moin
Im ersten Code hast du mit with Anweisung gearbeitet.
Code:

gruss hary

 

Hallo Studi,

genau das tut mein Code. Variante 2: Einfach die rot markierte Codezeile durch die unter "Variante" beschriebene ersetzen!

Er geht allerdings davon aus, dass auf eine Änderung in Spalte 6 reagiert werden soll (also dann, wenn Du auch die anderen Eintragungen vornimmst). Das habe ich als Deinen "Willen" interpretiert - eine andere Grundlage hast Du ja nicht geschaffen.
Mit Kenntnis der Mappe wäre aber sinnigerweise auf Eintragungen in Spalte 3 zu reagieren: Hier steht das Jahr, auf das die Formel angewiesen ist.

Code:

Bitte setze Hilfestellungen auch um oder frage präzise nach. Ich seh nur das, was auf meinem Monitor erscheint - in Deinen Kopf schauen kann ich nicht.

Grüße
EarlFred

 

Moin EarlFred

Ging auch ohne Mappe. ;-))

gruss hary

 

\@hary,

dann ersetze eben "Mappe" durch "neuen Code", "erweiterte Beschreibung" oder was auch immer Dir lieber ist.

Wenn jemand mit seinem Code so weit kommt, gehe ich davon aus, dass er eine so kleine Hürde auch selbst überklettern kann.

Grüße
EarlFred

 

\@Earl:
Entschuldige, aber ich habe das Edit nicht gesehen und hatte den Code nicht ausprobiert, da er sich ja eben so auf einen Eintrag in Spalte 6 bezieht - und das sollte er nicht.
deine Fragen habe ich zu beantworten versucht, aber anscheinend reden wir aneinander vorbei. Trotzdem lieben Dank für deine Mühe!
Jetzt läuft aber alles so wie es soll.

@Hary
Super! So hatte ich mir das gewünscht. Du hast mir mal wieder geholfen. Danke! :-)