Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Beträge in Formeln umwandeln möglich?
Pet

Guten Tag an Alle,
ich habe eine Tabelle mit Beträgen in € und Belegnummern.
Nun möchte ich aus den Beträgen die Mehrwertsteuer abziehen.
Natürlich geht das, wenn ich jeweils eine Spalte hinzufüge in der die MwSt abgezogen wird.
Da die Tabelle jedoch sehr umfangreich ist, wird sie noch unübersichtlicher.
Gibt es eine Möglichkeit die Beträge in Formeln umzuwandeln?
Zum besseren Verständnis habe ich eine kleine Bsp.-Datei beigefügt.
https://www.herber.de/bbs/user/65324.xls
Ich bedanke mich im voraus für Eure Hilfe.
Gruß, Pet

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
hier zwei Varianten...
25.10.2009 11:56:00
Tino
Hallo,
, einmal zum erstellen und einmal zum Löschen der Formel.
Sub FormelErstellen()
Dim Bereich As Range
Dim meAr, A As Long
Const sFormel$ = "/1,19"

Set Bereich = Range("A3", Cells(Rows.Count, 2).End(xlUp))
meAr = Bereich.Value2


For A = 1 To Ubound(meAr)
    If meAr(A, 1) <> "" Then
        meAr(A, 1) = "=" & meAr(A, 1) & sFormel
    End If
    If meAr(A, 2) <> "" Then
        meAr(A, 2) = "=" & meAr(A, 2) & sFormel
    End If
Next A

Bereich.FormulaLocal = meAr

End Sub

Sub FormelLoeschen()
Dim Bereich As Range
Dim meAr, A As Long
Const sFormel$ = "/1,19"

Set Bereich = Range("A3", Cells(Rows.Count, 2).End(xlUp))

meAr = Bereich.FormulaLocal

For A = 1 To Ubound(meAr)
    If InStr(meAr(A, 1), sFormel) > 0 Then
        meAr(A, 1) = Replace(meAr(A, 1), sFormel, "")
    End If
    If InStr(meAr(A, 2), sFormel) > 0 Then
        meAr(A, 2) = Replace(meAr(A, 2), sFormel, "")
    End If
Next A

Bereich.FormulaLocal = meAr
Bereich.Value = Bereich.Value

End Sub
Gruß Tino
Anzeige
Zusatzanmerkung
25.10.2009 12:18:22
Tino
Hallo,
wenn Du mit einem deutschen Excel arbeitest muss
Const sFormel$ = "/1,19"
mit einem Komma geschrieben werden.
Wenn Du mit anderer Ländereinstellung arbeitest, muss dies mit einem Punkt geschrieben werden.
Const sFormel$ = "/1.19"
Bin durch den Beitrag von NoNet darauf aufmerksam geworden.
Gruß Tino
AW: Beträge in Formeln umwandeln möglich?
25.10.2009 12:00:05
Daniel
Hi
um die Beträge in Formeln umzuwandeln, kannst du dieses Makro verwenden:
Sub Umwandeln()
Dim Zelle As Range
For Each Zelle In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 1)
If Zelle.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)" Then
Zelle.FormulaLocal = "=" & Zelle.Value & "/1,19"
End If
Next
End Sub

Gruß, Daniel
Anzeige
Numerische Werte in Formeln umwandeln
25.10.2009 12:08:32
NoNet
Hallo Pet,
ich hoffe mal, ich habe es richtig verstanden : Alle (numerischen) Werte der Spalte A sollen mit dem Zusatz "/1,19" ergänzt werden ?
Per VBA ist das möglich (Bitte zuerst an einer KOPIE Deiner Tabelle testen !) :
Sub WerteInFormelnUmwandeln()
Dim rngZelle As Range
'Alle Zellen der Spalte A (ab A3), die keine Formel enthalten,
'nicht leer sind und numerische Werte enthalten mit Zusatz
' "/1,19" als Formel umwandeln :
For Each rngZelle In Range("A3:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If Not rngZelle.HasFormula And _
IsNumeric(rngZelle.Value) And _
rngZelle  "" Then
rngZelle.Formula = "=" & rngZelle.Formula & "/1.19"
End If
Next
End Sub
Gruß, NoNet
Anzeige
Na, das wäre doch mal ein Ansatzpkt für...
26.10.2009 00:15:08
Luc:-?
...eine allgemeine Lösung dieses quasi Ersetzen-Problems gewesen, NoNet... ;-)
Als ich so ein Problem vor Jahren hatte, habe ich eine Subroutine geschrieben, die alles Mögliche vor und/oder nach jeden Zellinhalt in einem markierten Bereich setzen kann (InputBox!). Hat wie bei Dir und Daniel den Vorteil, dass die Originaldaten erhalten bleiben und so ggf rekonstruiert wdn können. (Die HptAnwendung war übrigens nachträgliches Runden.)
Grüße Luc :-?
Danke !!!
25.10.2009 12:44:32
Pet
vielen Dank für Eure Antworten.
Die Lösung von Tino hat mir die Spalte A + B umgewandelt, die von NoNet die Spalte A.
Leider kann sich die Spaltenanzahl bis AM fortsetzen, mit jeweils einer Zwischenspalte in der die Belegnummer hinterlegt sind.
Daniels Lösung ist hier für mich perfekt!
Das Makro wandelt alle € Beträge in die Formel um.
Nochmals danke für die tolle Unterstützung !
Schönen Sonntag, Pet
Anzeige
Dennoch hier meine Version...
25.10.2009 13:26:09
Tino
Hallo,
für die Spalten die als Überschrift Differenz, Ergebnis oder Summe enthalten,
auch wenn Du schon eine Lösung hast.
Const sngMWST As Single = 1.19 'Berechnungsfaktor 

Sub MWST_Abziehen()
Dim Bereich As Range
Dim meAr, A As Long, AA As Long

Set Bereich = Tabelle1.UsedRange
meAr = Bereich.FormulaLocal

For AA = 1 To Ubound(meAr, 2)
    If InStr("Differenz;Ergebnis;Summe", meAr(1, AA)) > 0 And meAr(1, AA) <> "" Then
       For A = 2 To Ubound(meAr)
           If meAr(A, AA) <> "" And IsNumeric(meAr(A, AA)) Then
               meAr(A, AA) = meAr(A, AA) / sngMWST
           End If
       Next A
    End If
Next AA

Bereich.FormulaLocal = meAr
End Sub

Sub MWST_Dazu()
Dim Bereich As Range
Dim meAr, A As Long, AA As Long

Set Bereich = Tabelle1.UsedRange

meAr = Bereich.FormulaLocal

For AA = 1 To Ubound(meAr, 2)
    If InStr("Differenz;Ergebnis;Summe", meAr(1, AA)) > 0 And meAr(1, AA) <> "" Then
       For A = 2 To Ubound(meAr)
           If meAr(A, AA) <> "" And IsNumeric(meAr(A, AA)) Then
               meAr(A, AA) = meAr(A, AA) * sngMWST
           End If
       Next A
    End If
Next AA

Bereich.FormulaLocal = meAr
End Sub
Gruß Tino
Anzeige
@Tino
25.10.2009 14:45:49
Pet
Hallo Tino,
ich habe gerade deine Lösung in einer anderen Tabelle ausprobiert.
Dazu habe ich die Überschriften abgeändert. Sie funktioniert !
Vielen Dank !!
Gruß, Pet
PS. Ich finde es einfach riesig, wie einem hier im Forum geholfen wird!!!

312 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige