Microsoft Excel

Herbers Excel/VBA-Archiv

Probleme mit Namen für Formel und Vba | Herbers Excel-Forum


Betrifft: Probleme mit Namen für Formel und Vba von: Reinhard
Geschrieben am: 24.10.2008 13:28:46

Hallo Wissende,

die Datei: https://www.herber.de/bbs/user/56243.xls hat nach Klick auf den Button die nachfolgende Tabelle:

Die Berechnung in Spalte C ist korrekt, so soll es aussehen, also pro Stunde nur einen Wert, Rest 0.
Da die Originaldatei alle 65536 Zeilen ausgenutzt hat, errgeben sich bei 102 Bytes Formellänge in C dann 6,7 MBytes nur für die Formeln.
Deshalb habe ich in Spalte D probiert, die gleiche Formel im Namen W zu benutzen.
Wie man sieht wird dann aber in D falsch gerechnet. Was muß ich da ändern?

Zweite Frage, es gelingt mir nicht im folgenden Code die Formel mit dem Unterstrich aufzuteilen auf zwei Zeilen, dann kommt Kompilierungsfehler, erwartet Anweisungsende, das "Wenn" wird markiert.

Sub Berechnen()
Dim F As String
Dim Zei As Long, F1 As String, F2 As String
'F = "=WENN(MINUTE(A1)=55;SUMME(INDIREKT(""b""&ZEILE()-11&"":B""&ZEILE()))/12; _
'   WENN(STUNDE(A1)<>STUNDE(A2);B1;0))"
F1 = "=WENN(MINUTE(A1)=55;SUMME(INDIREKT(""b""&ZEILE()-11&"":B""&"
F2 = "ZEILE()))/12;WENN(STUNDE(A1)<>STUNDE(A2);B1;0))"
Columns(3).NumberFormat = "0.00"
Zei = Range("A" & Rows.Count).End(xlUp).Row
Range("C1:C" & Zei).FormulaLocal = F1 & F2
'Range("C1:C" & Zei).Value = Range("C1:C" & Zei).Value
End Sub



Tabellenblatt: H:\[kwStundenliste.xls]!Liste
   |   A  |  B |   C   |   D  |
---|------|----|-------|------|
 1 | 0.00 |  1 |  1,00 | 1,00 |
---|------|----|-------|------|
 2 | 1.00 |  2 |  2,00 | 2,00 |
---|------|----|-------|------|
 3 | 2.00 |  3 |  3,00 | 3,00 |
---|------|----|-------|------|
 4 | 3.00 |  4 |  4,00 | 4,00 |
---|------|----|-------|------|
 5 | 4.00 |  5 |  5,00 | 5,00 |
---|------|----|-------|------|
 6 | 5.00 |  6 |  0,00 | 0,00 |
---|------|----|-------|------|
 7 | 5.05 |  7 |  0,00 | 0,00 |
---|------|----|-------|------|
 8 | 5.10 |  8 |  0,00 | 0,00 |
---|------|----|-------|------|
 9 | 5.15 |  9 |  0,00 | 0,00 |
---|------|----|-------|------|
10 | 5.20 | 10 |  0,00 | 0,00 |
---|------|----|-------|------|
11 | 5.25 | 11 |  0,00 | 0,00 |
---|------|----|-------|------|
12 | 5.30 | 12 |  0,00 | 0,00 |
---|------|----|-------|------|
13 | 5.35 | 13 |  0,00 | 0,00 |
---|------|----|-------|------|
14 | 5.40 | 14 |  0,00 | 0,00 |
---|------|----|-------|------|
15 | 5.45 | 15 |  0,00 | 0,00 |
---|------|----|-------|------|
16 | 5.50 | 16 |  0,00 | 0,00 |
---|------|----|-------|------|
17 | 5.55 | 17 | 11,50 | 1,42 |
---|------|----|-------|------|
18 | 6.00 | 18 |  0,00 | 0,00 |
---|------|----|-------|------|
19 | 6.05 | 19 |  0,00 | 0,00 |
---|------|----|-------|------|
20 | 6.10 | 20 |  0,00 | 0,00 |
---|------|----|-------|------|
21 | 6.15 | 21 |  0,00 | 0,00 |
---|------|----|-------|------|
22 | 6.20 | 22 |  0,00 | 0,00 |
---|------|----|-------|------|
23 | 6.25 | 23 |  0,00 | 0,00 |
---|------|----|-------|------|
24 | 6.30 | 24 |  0,00 | 0,00 |
---|------|----|-------|------|
25 | 6.35 | 25 |  0,00 | 0,00 |
---|------|----|-------|------|
26 | 6.40 | 26 |  0,00 | 0,00 |
---|------|----|-------|------|
27 | 6.45 | 27 |  0,00 | 0,00 |
---|------|----|-------|------|
28 | 6.50 | 28 |  0,00 | 0,00 |
---|------|----|-------|------|
29 | 6.55 | 29 | 23,50 | 2,42 |
---|------|----|-------|------|
30 | 7.00 | 30 |  0,00 | 0,00 |
------------------------------'
Benutzte Formeln:
C1 :  =WENN(MINUTE(A1)=55;SUMME(INDIREKT("b"&ZEILE()-11&":B"&ZEILE()))/12;WENN(STUNDE(A1)<> _
STUNDE(A2);B1;0))
C2 :  =WENN(MINUTE(A2)=55;SUMME(INDIREKT("b"&ZEILE()-11&":B"&ZEILE()))/12;WENN(STUNDE(A2)<> _
STUNDE(A3);B2;0))
C3 :  =WENN(MINUTE(A3)=55;SUMME(INDIREKT("b"&ZEILE()-11&":B"&ZEILE()))/12;WENN(STUNDE(A3)<> _
STUNDE(A4);B3;0))
usw. in Spalte C

D1 :  =W
D2 :  =W
D3 :  =W

Festgelegte Namen:
W: =WENN(MINUTE(A1)=55;SUMME(INDIREKT("b"&ZEILE()-11&":B"&ZEILE()))/12;WENN(STUNDE(A1)<>STUNDE( _
A2);B1;0))    *rel. Name, so gültig in D1

Zahlenformate der Zellen im gewählten Bereich:
A1:A30
haben das Zahlenformat: h.mm
B1:B30
haben das Zahlenformat: Standard
C1:C30,D1:D30
haben das Zahlenformat: 0,00



Danke Gruß
Reinhard

  

Betrifft: AW: Probleme mit Namen für Formel und Vba von: EffHa
Geschrieben am: 24.10.2008 13:46:29

Hallo Reinhard,
zu Frage 2

Wenn ein String getrennt wird, muss er konkatiniert werden z.B. so

F = "=WENN(MINUTE(A1)=55;SUMME(INDIREKT(""b""&ZEILE()-11&"":B""&ZEILE()))/12;" & _
"WENN(STUNDE(A1)<>STUNDE(A2);B1;0))"

Frage 1 habe ich nicht verstanden


  

Betrifft: AW: Probleme mit Namen für Formel und Vba von: Reinhard
Geschrieben am: 25.10.2008 02:01:00

Hallo EffHa,

danke, also muß man da ein & einfügen, wußte ich nicht.

Zu Frage 1, schau dir bitte die Tabelle an, in Spalte C rechnen die dort drin stehenden Formeln korrekt, so soll es sein.
In Spalte D steht nur "=W", dahinter verbirgt sich aber die gleiche Formel wie in C, wieso rechnet die falsch?
Schau in Zeile 17 und 29, dadrum geht es.

Gruß
Reinhard


  

Betrifft: AW: Probleme mit Namen für Formel und Vba von: Erich G.
Geschrieben am: 25.10.2008 08:09:23

Hi Reinhard,
warum die Formel im Namen W nicht den reichtigen Bereich summiert, weiß ich auch nicht.

Aber ich kann dir einen Änderungsvorschlag machen, mit dem es funzt:

 ABCD
175.551711,5011,50
186.00180,000,00
196.05190,000,00
206.10200,000,00
216.15210,000,00
226.20220,000,00
236.25230,000,00
246.30240,000,00
256.35250,000,00
266.40260,000,00
276.45270,000,00
286.50280,000,00
296.552923,5023,50

Formeln der Tabelle
ZelleFormel
C17=WENN(MINUTE(A17)=55;SUMME(INDIREKT("b"&ZEILE()-11&":B"&ZEILE()))/12;WENN(STUNDE(A17)<>STUNDE(A18); B17;0))
D17=W
Namen in Formeln
ZelleNameBezieht sich auf
D17W=WENN(MINUTE(Liste!A17)=55;SUMME(BEREICH.VERSCHIEBEN(Liste!B17;-11;;12))/12; WENN(STUNDE(Liste!A17)<>STUNDE(Liste!A18);Liste!B17;0))

Noch ein Tipp:
"W" ist eine Excel-Spaltenbezeichnung, ein Spaltenname - den solltest du nicht als Namen benutzen.

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


  

Betrifft: AW: Probleme mit Namen für Formel und Vba von: Reinhard
Geschrieben am: 25.10.2008 14:05:55

Hallo Erich,

ich stelle die Frage auf noch offen, denn es interessiert mich schon, unabhängig von dieser einen Problemlösung, warum meine Formel nur in einer Zelle stehend funktioniert und nicht als vergebener Name.

Zum Namen W, ich wollte halt einen ganz kurzen Namen um die dateigröße zu minimieren.

Gruß
Reinahrd


  

Betrifft: AW: Probleme mit Namen für Formel und Vba von: rofu
Geschrieben am: 29.10.2008 13:05:56

Sub Berechnen()
    Dim F As String
    Dim Zei As Long, F1 As String, F2 As String
    'F = "=WENN(MINUTE(A1)=55;SUMME(INDIREKT(""b""&ZEILE()-11&"":B""&ZEILE()))/12; _
    '   WENN(STUNDE(A1)<>STUNDE(A2);B1;0))"
    F1 = "=WENN(MINUTE(A1)=55;SUMME(INDIREKT(""b""&ZEILE()-11&"":B""&"
    F2 = "ZEILE()))/12;WENN(STUNDE(A1)<>STUNDE(A2);B1;0))"
    Columns(3).NumberFormat = "0.00"
    Zei = Range("A" & Rows.Count).End(xlUp).Row
    Range("C1:C" & Zei).FormulaLocal = F1 & F2
    'Range("C1:C" & Zei).Value = Range("C1:C" & Zei).Value
    End Sub



hallo,

so gehts

F = "=WENN(MINUTE(A1)=55;SUMME(INDIREKT(""b""&ZEILE()-11&"":B""&ZEILE()))/12; _
& "WENN(STUNDE(A1)<>STUNDE(A2);B1;0))"

gruß
rofu


Beiträge aus den Excel-Beispielen zum Thema "Probleme mit Namen für Formel und Vba"