Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

VBA-Formel um nur Berechnungswerte einzutragen

Betrifft: VBA-Formel um nur Berechnungswerte einzutragen von: Matthias
Geschrieben am: 12.08.2008 08:51:59

Hallo Excel und Makrofans.

Ich habe folgende 2 Probleme. Erstmal zum ersten Problem:

Ich habe eine Datei in der ich Daten (Betriebsparameter [Druck, Temperatur usw.]) aus einem externen Programm einlese. Aus diesen Werten möchte ich gerne einige Werte Berechnen.
Zur Zeit mache ich das noch Umständlich über FormulaLocal und danach Copy und Pastespecial - Werte einfügen.
Das sieh zum Beispiel so aus:
Range("I5:I1500").FormulaLocal = "=WENN(C5="""";"""";C5+D5)"
Range("J5:J1500").FormulaLocal = "=WENN(C5="""";"""";I5/3,6*$I$1*(E5-B5))"
Range("K5:K1500").FormulaLocal = "=WENN(C5="""";"""";LMTD(F5;G5;B5;E5))"
Range("L5:L1500").FormulaLocal = "=WENN(C5="""";"""";(J5*1000)/(K5*$L$1))"
Range("M5:M1500").FormulaLocal = "=WENN(C5="""";"""";I5/F5*3,6)"

Range("I5:M1500").Copy
Range("I5").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Ich würde diesen Kopierschritt gerne löschen indem ich nur Werte eintrage und nicht zu allererst die Formel. Könnte mir da bitte jemand weiterhelfen?

Das zweite Problem ist möglicherweise etwas einfacher. Ich Berechne die Steigung einer Kurve über die Excel-Formel "Steigung". Nun möchte ich aber das für die Berechnung aber nur die letzten 200 Datenpunkte genutzt werden. Gibt es dafür eine Formel die nur die letzten 200 Werte nimmt. Also einen dynamischen Bereich oder so etwas.
Vielen Dank im voraus.

Mit freundlichen Grüßen

Matthias

  

Betrifft: AW: VBA-Formel um nur Berechnungswerte einzutragen von: Erich G.
Geschrieben am: 12.08.2008 09:31:56

Hallo Matthias,
statt

Range("I5:M1500").Copy
Range("I5").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

kannst du auch schreiben:
Range("I5:M1500").Value=Range("I5:M1500").Value
oder
Range("I5:M1500").formula=Range("I5:M1500").Value

Vielleicht reicht das ja aus.

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort


  

Betrifft: AW: VBA-Formel um nur Berechnungswerte einzutragen von: Matthias
Geschrieben am: 12.08.2008 10:13:38

Hallo Erich,

danke das klappt super.

MFG Matthias


  

Betrifft: AW: VBA-Formel um nur Berechnungswerte einzutragen von: Luschi
Geschrieben am: 12.08.2008 09:42:05

Hallo Matthias,

zum 1. Problem:
ich würde an dem Copy-Paste nichts ändern, da es wohl auch mit anderem Vba-Code
nicht schneller geht; dafür aber dafür ein paar Exceleinstellungen umschalten:


getMoreSpeed True
Range("I5:I1500").FormulaLocal = "=WENN(C5="""";"""";C5+D5)"
Range("J5:J1500").FormulaLocal = "=WENN(C5="""";"""";I5/3,6*$I$1*(E5-B5))"
Range("K5:K1500").FormulaLocal = "=WENN(C5="""";"""";LMTD(F5;G5;B5;E5))"
Range("L5:L1500").FormulaLocal = "=WENN(C5="""";"""";(J5*1000)/(K5*$L$1))"
Range("M5:M1500").FormulaLocal = "=WENN(C5="""";"""";I5/F5*3,6)"

Range("I5:M1500").Copy
Range("I5").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
getMoreSpeed False

Die Procedure 'getMoreSpeed' schaltet am Anfang einige geschwindigkeitshemmende Einstellungen aus und zum Schluß wieder ein und sieht so aus:



Sub getMoreSpeed(bDoIt As Boolean)

    Application.ScreenUpdating = Not (bDoIt)

    Application.EnableEvents = Not (bDoIt)

    Application.Calculation = IIf(bDoIt, xlCalculationManual, xlCalculationAutomatic)

    Application.Cursor = IIf(bDoIt, 2, -4143)

End Sub

zu Deinem 2. Problem: definiere im Namensmager (Strg+F3) die folgenden Namen und hinterlege dafür folgende Formeln:
1. mein_Bereich_ges =Tabelle1!$A$2:$A$2500
2. mein_Bereich_ben =BEREICH.VERSCHIEBEN(mein_Bereich_ges;;;ANZAHL2(mein_Bereich_ges))
3. mein_Bereich_L200 =BEREICH.VERSCHIEBEN(mein_Bereich_ben;ANZAHL2(mein_Bereich_ben)-200;;200)

Mit dem 1. Namen definierst Du den maximalen Gesamtbereich,
mit dem 2. Namen den davon tatsächlich benutzten Bereich, und
mit dem 3. Namen die letzten 200 Zeilen vom benutzten Bereich.
Den Namen 'mein_Bereich_L200' kann man jetzt als Datenquelle angeben.



  

Betrifft: AW: VBA-Formel um nur Berechnungswerte einzutragen von: Matthias
Geschrieben am: 12.08.2008 10:19:45

Hallo Luschi,

also das mit getmoreSpeed klappt gut, aber beim Zweiten bin ich mir nicht sicher ob es das richtige ist. Was ich vergaß zu sagen ist das ich nicht nur eine Tabelle habe sondern so an die 8. Wenn ich das richtig Verstehe müsste ich ja dann für alle Tabellen die Namen definieren? Des Weiteren kommen jeden Tag Werte hinzu. Die Tabellen werden also länger und würden dann aus dem definierten Bereich raus "wachsen". Wie ist das mit dem Bereich? Wenn ich zum Beispiel sage "nimm Bereich Z5:Z1200" und ich aber nur Werte bis Zeile 800 drin habe, nimmt er dann die Zeilen 1000 bis 1200 (wenn ich L200 annehme) um die Steigung zu Berechnen? Vielen Dank schon mal für deine bisherige Hilfe.

MFG Matthias


  

Betrifft: AW: VBA-Formel um nur Berechnungswerte einzutragen von: Daniel
Geschrieben am: 12.08.2008 11:51:35

Hi

wenn du wie eingangsbeschrieben, immer nur die letzen 200 Zeilen für die Berechnung verwenden willst, (also wenn die Datei 1200 Zeilen hat dann nur die Zeilen 1001 - 1200), dann geht das mit folgendermassen:

anstelle des Zellbereichs "A1001:A1200 " setzt du in die Formel ein:
Bereich.Verschieben(A1;Anzahl2(A:A)-200;0;200;1)

funktioniert unter folgenden Bedingungen:
- es gibt mindestens 200 Zeilen
- die Zeilen sind lückenlosg gefüllt

Gruß, Daniel