Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema InputBox
BildScreenshot zu InputBox InputBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Excel Makro signifikante Stellen | Herbers Excel-Forum


Betrifft: Excel Makro signifikante Stellen von: Andreas
Geschrieben am: 15.02.2012 13:12:38

Ich bin gerade am basteln und wollte ein im Archiv gefundenes Makro für meine Zwecke anpassen:

Ziel ist ein Makro, dass im markierten Bereich Zelle für Zelle durchgeht und anhand der enthaltenen Zahl das Zahlenformat entsprechend anpasst.

Sub Format_Nachkommastellen()
  Dim varEingabe As Variant
Eingabe:
  varEingabe = InputBox(Prompt:="Anzahl Nachkommastellen ( 1, 2 oder 3):", _
      Title:="Zellen formatieren - Nachkommastellen", _
      Default:=2)
  Select Case varEingabe
    Case "" 'Abgebrochen
    Case "1", "2", "3"
      Selection.NumberFormat = "#,##0." & String(CLng(varEingabe), "0")
    Case Else
      If MsgBox("unzulässige Eingabe", vbRetryCancel, "") = vbRetry Then GoTo Eingabe
  End Select
End Sub

Dieses Makro geht die einzelnen Zellen durch und setzt jeweils die angegebene Anzahl an Stellen als Nummernformat. So weit so gut.

Ein anderes funktionsfähiges Makro dass ich bereits habe sieht so aus:
Dim row As Integer
        For row = 26 To 33 Step 1
        If Cells(row, 6) = 0 Then Cells(row, 6).NumberFormat = "?"
        If Cells(row, 6).Value < 10 And Cells(row, 6).Value > 0 Then Cells(row, 6).NumberFormat  _
= "?????0.000"
        If Cells(row, 6).Value < 100 And Cells(row, 6).Value >= 10 Then Cells(row, 6). _
NumberFormat = "????00.00"
        If Cells(row, 6).Value < 1000 And Cells(row, 6).Value >= 100 Then Cells(row, 6). _
NumberFormat = "???000.0"
        If Cells(row, 6).Value >= 1000 Then Cells(row, 6).NumberFormat = "??0000"
        Next row

Leider gelingt es mir nicht beide Codes zu vereinen. Über Hilfe würde ich mich freuen.

Vielen Dank

Andreas

  

Betrifft: AW: Excel Makro signifikante Stellen von: Dirk
Geschrieben am: 15.02.2012 21:15:59

Hallo Andreas,

Ich bin mir nicht sicher was das verden soll, wenn man die beiden miteinander verbindet.

wenn du ein Tool suchst, dass die Zeilen durchgeht in einem makiertem bereich kannst du das hier nehmen

Sub test()
Dim i, anzNach, splat, zeil, anzZeil, nZeil, soll As Integer
Dim tmp As String
Dim zustd As String
Range("c5:c9").Select
soll = 5 'die soll-Anzahl der signifikanten stellen
zeil = Selection.Row
spalt = Selection.Column
anzZeil = Selection.Count
For i = 1 To anzZeil
nZeil = zeil + i - 1
tmp = Cells(nZeil, spalt)
If Len(Split(tmp, ",")(0)) >= soll Then
anzNach = 0
Else
anzNach = soll - Len(Split(tmp, ",")(0))
End If

If anzNach = 0 Then
Cells(nZeil, spalt).NumberFormat = "?0"
Else
Cells(nZeil, spalt).NumberFormat = "?0." & String(CLng(anzNach), "0")
End If
Next i

End Sub
Gruß
Dirk


  

Betrifft: AW: Excel Makro signifikante Stellen von: Andreas
Geschrieben am: 16.02.2012 09:03:27

Dank, leider funktioniert das Makro nicht wie gewünscht, es setzt die Stellen von C5:C9 auf 1. Ich habe inzwischen diese Lösung die recht nah an das heran kommt was ich suche. Leider funktioniert es bisgher nicht mit negativem Werten und Werten die unter 0.01 liegen:

Sub Stellen()
With Selection
For i = 1 To .Cells.Count
sngWert = .Cells(i).Value
Select Case sngWert
Case Is >= 1000
.Cells(i).NumberFormat = "0"
Case Is >= 100
.Cells(i).NumberFormat = "0.0"
Case Is >= 10
.Cells(i).NumberFormat = "0.00"
Case Is >= 1
.Cells(i).NumberFormat = "0.000"
Case Is >= 0.1
.Cells(i).NumberFormat = "0.0000"
Case Is >= 0.01
.Cells(i).NumberFormat = "0.00000"
End Select
Next i
End With
End Sub




  

Betrifft: AW: Excel Makro signifikante Stellen von: Dirk
Geschrieben am: 16.02.2012 12:12:38

dann multiplizier die Zahl doch einfach *-1 um das vorzeichen umzukehren bevor du das Format einstellst.

so

Sub Stellen()
With Selection
For i = 1 To .Cells.Count
sngwert = .Cells(i).Value
If sngwert < 0 Then sngwert = sngwert * (-1)
Select Case sngwert
Case Is >= 1000
.Cells(i).NumberFormat = "0"
Case Is >= 100
.Cells(i).NumberFormat = "0.0"
Case Is >= 10
.Cells(i).NumberFormat = "0.00"
Case Is >= 1
.Cells(i).NumberFormat = "0.000"
Case Is >= 0.1
.Cells(i).NumberFormat = "0.0000"
Case Is >= 0.01
.Cells(i).NumberFormat = "0.00000"
End Select
Next i
End With
End Sub
Gruß
Dirk


  

Betrifft: AW: Excel Makro signifikante Stellen von: Andreas
Geschrieben am: 16.02.2012 13:04:07

Simple aber genial...

Danke!

Hast Du auch eine Idee wie man das ganze dynamischer machen könnte, sprich ohne für jeden Fall ( >1, >0.1, >0.01) etc. einen Case hinzuzufügen


  

Betrifft: AW: Excel Makro signifikante Stellen von: Dirk
Geschrieben am: 16.02.2012 20:54:37

Ich glaube nicht, das man den Code kürzer bekommt.

wenn das Programm von mir (Das "nicht" funktionierte, wei das noch drin stand Range("c5:c9").Select)
, dass die Zahlen Variable abfrage, mit deinem Vergeicht stellt man fest, dass der Code eher länger wird.

er mag vieleicht flexibler sein, da es egal ist wie groß oder klein die Zahl ist.

Hier nochmal eine Korrigierte und überarbeitete Form

Sub test()
   Dim i, anzNach, splat, zeil, anzZeil, nZeil, soll As Integer
   Dim tmp As String
   Dim zustd As String
   soll = 5 'die soll-Anzahl der signifikanten stellen
   zeil = Selection.Row
   spalt = Selection.Column
   anzZeil = Selection.Count
   For i = 1 To anzZeil
   nZeil = zeil + i - 1
   tmp = Cells(nZeil, spalt)
   If tmp < 0 Then tmp = tmp * (-1)
   If tmp < 1 Then
   z = -1
   Do While tmp < 1
   z = z + 1
      tmp = tmp * 10
   Loop
    anzNach = soll + z
    Else
      anzNach = soll - Len(Split(tmp, ",")(0))
   End If
    If anzNach < 1 Then
   Cells(nZeil, spalt).NumberFormat = "?0"
   Else
   Cells(nZeil, spalt).NumberFormat = "?0." & String(CLng(anzNach), "0")
   End If
   Next i
   End Sub
Gruß

Dirk


  

Betrifft: AW: Excel Makro signifikante Stellen von: Dirk
Geschrieben am: 16.02.2012 21:07:04

hier nochmal eine etwas aufgeräumtere version

sie ist aber immernoch 2 Zeilen länger als dein Code *arg*

Die könnte man noch raushohlen würd aber mehr Tipparbeit und korrekturarbeit heißen bei gewünster änderung der Anzahl signifikanter stellen.

Gruß
Dirk

Sub test()
   Dim i, anzNach, anzZeil, soll As Integer, tmp As String
   soll = 5 'die soll-Anzahl der signifikanten stellen
   anzZeil = Selection.Cells.Count
   For i = 1 To anzZeil
   tmp = Selection.Cells(i)
   If tmp < 0 Then tmp = tmp * (-1)
   If tmp < 1 Then
   z = -1
   Do While tmp < 1
   z = z + 1
      tmp = tmp * 10
   Loop
    anzNach = soll + z
    Else
      anzNach = soll - Len(Split(tmp, ",")(0))
   End If
    If anzNach < 1 Then
   Selection.Cells(i).NumberFormat = "?0"
   Else
   Selection.Cells(i).NumberFormat = "?0." & String(CLng(anzNach), "0")
   End If
   Next i
 End Sub



  

Betrifft: AW: Excel Makro signifikante Stellen von: Andreas
Geschrieben am: 17.02.2012 07:16:26

Danke für Deine Mühe! Aber:

Dein Script liefert das "richtige"/gewünschte Ergebnis nur dann wenn die Zahl <1 ist oder >10000 ist.

0.123456789 wird korrekt zu 0.12345
1234567.89012345678 wird korrekt zu 1234568

123.45678 wird jedoch zu 123, sollte jedoch zu 123.46 werden (5 gültige Stellen)

Leider fehlt mir der Durchblick weshalb Cells(nZeil, spalt).NumberFormat = "?0." & String(CLng(anzNach), "0") in diesen Fällen nicht das gewünschte Ergebnis liefert.


  

Betrifft: AW: Excel Makro signifikante Stellen von: Dirk
Geschrieben am: 17.02.2012 08:39:23

kann es sein das du dein Excel auf Amerekanisch stehen hast?
12,025 = 12.025?

wenn das so ist must du eine passage Ändern
Ich hab dir hier den Code mal versucht etwas zu erläutern

Sub test()
    Dim i, anzNach, anzZeil, soll As Integer, tmp As String
    soll = 5 'die soll-Anzahl der signifikanten stellen
    anzZeil = Selection.Cells.Count 'Zellenanzahl ermitteln
    For i = 1 To anzZeil 'Schleifenstart von zelle 1 bis letzte Zelle
    tmp = Selection.Cells(i) 'einlesen neuer Zelle
    If tmp < 0 Then tmp = tmp * (-1) 'wenn zelle negativ mach zu Positiv
    If tmp < 1 Then 'ist zahl kleiner 1
    z = -1
    Do While tmp < 1 'ermitteln an welcher nachkommastelle die erste Zahl kommt
    z = z + 1
       tmp = tmp * 10
    Loop
     anzNach = soll + z 'Rückgabe in als Anzahl der Nachkommastellen
     Else
       anzNach = soll - Len(Split(tmp, ",")(0)) 'wenn zahl >1 dann trenne die Zahl bei , und  _
sag mir wie lang sie ist
       'bei Amerikanischer einstellung muss hier das , duch einen . ersetzt werden
       'aus 152,5648 mach er hier 152 = 3 Stellen vor dem ,
    End If
     If anzNach < 1 Then 'wenn die Zahl mehr stellen (als ganzzahl) hat als die anzahl der  _
Signifikanten
    'dann war der Rückgabewert anzNach <1 (meist sogar negativ)
    Selection.Cells(i).NumberFormat = "?0" 'Formatiere die Zahl ohne Nachkommastellen
    Else
    Selection.Cells(i).NumberFormat = "?0." & String(CLng(anzNach), "0") 'Formatiere die Zahl  _
mit der Geforderten anzahl nachkommastellen
    'wenn anzNach z.b. 2 ist wird aus  String(CLng(anzNach), "0")=000
    'dies wird einfach an die formatierung ?0. angefügt --> ?0.000
    End If
    Next i
  End Sub
Gruß
Dirk


  

Betrifft: AW: Excel Makro signifikante Stellen von: Andreas
Geschrieben am: 17.02.2012 09:18:58

Ich bin schwer begeistert, das Makro tut genau was ich mir vorgestellt habe.

Tausend Dank!

P.S.: Hattest übrigens recht, die Spracheinstellungen meines Excels sorgen dafür, dass ich den Punkt als Dezimaltrennzeichen habe. War mir explizit noch nie aufgefallen ;-)


Beiträge aus den Excel-Beispielen zum Thema "Excel Makro signifikante Stellen"