Über VBA ein Wenn Dann Makro erstellen?

[aktivos]

Ist es möglich über VBA ein Makro zu erstellen, welches eine Wenn Dann Formel ausführt? Ich kenne mich leider kaum mit VBA aus, versuche aber gerade mich ein wenig „anzulernen“.

Im konkreten geht es darum, dass ich mir mittels Makro Zahlen aus einer anderen Datei in mein Excel Sheet ziehe. In diesen Zahlen kann es sein, dass vereinzelt eine 0 dabei ist. Diese 0 ist aber nicht gewollt und soll daher ersetzt werden als Mittlewert seiner umliegenden Zahlen (am besten wäre hier die umliegenden 4, da auch zwei 0en nach einander auftreten können). Da ich wie gesagt, die Zahlen als Makro hinzuziehe kann ich ja schlecht die Formel in die einzelnen Zeilen schreiben, sondern wollte über einen weiteren Makro diesen Fehler ausmerzen (hierzu habe ich eine Schaltfläche gebaut).

Kann mir jemand bei dem Code helfen, oder zumindest Anreize geben?

[VBA_Newbie]

Deine Anforderungen sind möglich.

Du kannst ja mal versuchen ein Makro aufzuzeichnen, während Du besagte Formel eintippst.

Dann kannst Du Dir den erzeugtne Code ansehen und daraus Deine Rückschlüsse ziehen.

[Muster_Maxmann]

Hallo aktivos!

VBA in Excel ist nicht schwer, der Anfang jedoch schon. Ich werde dir gern versuchen zu helfen:

Unter meinem Text findest du einen Code mit Erklärung. Den kopierst du am besten zuerst nach Excel. Dann ist es besser zu erkennen, was in der Beschreibung steht. Der Code selbst besteht nur aus wenigen Zeilen. Bei Fragen kannst du dich gern wieder melden.

Für den Fall, dass mehrere 0en nacheinander auftreten ist es doch sicher besser, wenn diese für den Mittelwert der ersten alle ignoriert werden, oder? Eventuell solltest du überlegen, statt des Mittelwertes aus vier Zahlen zu bilden, zwischen der letzten richtigen Zahl und der nächsten richtigen linear zu interpolieren.
Der Code unten nimmt nun einfach beim Auftreten einer 0 den Mittelwert jweils der zwei vorangegangen und nachfolgenden Zahlen. Kommt dort wieder ein 0, wird das Ergebnis verfälscht.

Grüße,
Muster

____________________________

Option Explicit

Sub zahlenPrüfen()

Dim zelleStart As Range, zelle As Range

Set zelleStart = Range(„A2“)
'Startzelle festlegen -> beliebig

For Each zelle In Range(zelleStart, zelleStart.End(xlDown))
'für jede Zelle im Bereich von Startzelle (siehe Zeile drüber) bis
'zur letzten Zeile unter der Startzelle wird nun die Schleife ausgeführt
'dabei ist „zelle“ dynamisch - es ist immer die aktuelle Zelle und
'rutscht mit jedem Schleifendurchgang eine Zeile tiefer

If zelle.Value = 0 Then
'wenn in der Zelle eine „0“ auftritt. Eventuell muss hier noch
'vorher gesichert werden, dass in der Zelle auch wirklich eine
'Zahl steht. Das geht mit Isnumeric()

zelle.Value = (zelle.Offset(-2, 0).Value _

• zelle.Offset(-1, 0).Value _
• zelle.Offset(1, 0).Value _
• zelle.Offset(2, 0).Value) / 4
  'der Mittelwert wird hier einfach als Summe/Anzahl gebildet.
  'Das geht sicher auch schöner, aber so versteht man es besser
  'Der Unterstrich dient dazu, den Code auf der nächsten Zeile
  'fortzusetzen

End If

Next zelle 'Ende der For-Schleife

End Sub

[aktivos]

Hallo Muster,

super vielen Dank! Dein Code bringt mich schon ein ganzes Stück weiter, ist für mich ja schon was besonderes, wenn nicht jedes mal eine neue Fehlermeldung aufkommt
Danke auch für die Erklärungen im Skript, das macht alles sehr verständlich!

Ist es in dem Code Möglich eine Range abzudecken, die von B9:G9 geht? Muss ich hier einfach den Code in

Sub zahlenPrüfen()

Dim zelleStart As Range, zelle As Range

Set zelleStart = Range(„B9:G9“)
'Startzelle festlegen -> beliebig

For Each zelle In Range(zelleStart, zelleStart.End(xlDown))

ändern?

Natürlich hast du recht, eine lineare Interpolarisierung würde helfen. Ich habe die Zahlen vorhin einmal alle aktualisiert und im Endeffekt sind viele der Zahlen verfälscht, da mehrmals die 0 vorkam :-/
Bei linearer Interpolarisierung bin ich aber total aufgeschmissen und habe nichtmal eine leiseste Idee, wie ich das in VBA versuchen(und scheitern ) könnte.

[Muster_Maxmann]

Nein, den Bereich musst du anders festlegen:
In der For-Schleife wird das Objekt „zelle“ einmal mit jeder Zelle des Angegebenen Bereiches gleichgesetzt. Du schreibst also dann:

For Each zelle In Range(„B9:G9“)
…
next

Dann ist aber der Bereich fixiert - keine Zahl hinter G9 wird mehr betrachtet. Wenn deine Zahlen horizontal gelistet sind, dann musst den Code folgendermaßen nutzen:

Set zelleStart = Range(„B9“)

For Each zelle In Range(zelleStart, zelleStart.End(xlToRight))
If zelle.Value = 0 Then
zelle.Value = (zelle.Offset(0, -2).Value _

• zelle.Offset(0, -1).Value _
• zelle.Offset(0, 1).Value _
• zelle.Offset(0, 2).Value) / 4
  End If

Next zelle

Kurze Erklärung zu Offset(): Das ist eine verschiebung des Bereiches (vertikal, horizontal)
zelle.Offset(2,3) spricht die Range an, die 2 Reihen unter und 3 Spalten rechts von zelle ist.
Sollen die Zahlen nicht über/untereinander, sondern vor/hintereinander gemittelt werden, dann musst du das natürlich tauschen.

Erwartest du keine lineare Änderung deiner Werte, musst du auf eine andere Interpolation zurückgreifen. Ich habe auch noch Newton-Interpolation da. Hier der Code mit einer linearen Interpolation. Versuche ihn einmal Schritt für Schritt zu verstehen. Das kannst am besten machen, wenn du Haltepunkte in den Code machst und dich mit F8 Zeile für zeile bewegst. Mit der maus kannst du auch mal über die einzelnen Variablen fahren und dir den Wert anzeigen lassen.

Sub zahlenPrüfen()

Dim zelleStart As Range, zelle As Range

Set zelleStart = Range(„B9“)

For Each zelle In Range(zelleStart, zelleStart.End(xlToRight))
If zelle.Value = 0 Then

'hier startet die Interpolation

Dim y1 As Double, y2 As Double, x1 As Double, x2 As Double 'Werte für die Interpolation
Dim i As Integer 'eine Laufvariable

x1 = zelle.Offset(0, -1).Value
'letzter richtige X-Wert
y1 = 0
'Startwert

i = 0
While zelle.Offset(0, i).Value = 0
'hier wird eine Laufvariable i erhöht,
'solange 0en aufeinander folgen. Damit
'wird das Ende der „0en-Reihe“ gesucht
i = i + 1
Wend

x2 = zelle.Offset(0, i).Value
'erste Zelle mit einer richtigen Zahl nach der 0en-Reihe
y2 = i + 1
'Zähler auf die folgende Zahl setzen

i = 0
While zelle.Offset(0, i).Value = 0
'noch einmal alle 0en durchgehen und mit
'dem Ergebnis der Interpolation ersetzen

zelle.Offset(0, i).Value = x1 + ((x2 - x1) / (y2 - y1)) * (i + 1 - y1)
'Interpolation

i = i + 1

Wend

End If
Next zelle 'Ende der For-Schleife
End Sub

[Thomas_1eb1d5]

Hallo!

Erste Möglichkeit:
Du musst in deinem Programmcode des Makros prüfen ob der Wert = 0 ist. Wenn ja, dann musst Du über zusätzlichen Code den Mittelwert aus den umliegenden Zahlen bilden.

Zweite Möglichkeit:
Wenn die Zahlen übertragen wurden, machst Du die „Wenn Dann“ Formel in die Spalte daneben und berechnest über diese Formel den entsprechenden Mittelwert.