Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1604to1608
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Wertekopie: Erweitern

Wertekopie: Erweitern
30.01.2018 07:58:51
Mirco
Hallo Expertenteam.
ich benötige eure Hilfe zur Erweiterung/Optimierung. Folgendes Makro funktioniert wunderbar und ich benötige "lediglich" eine Ergänzung:
Sub Wertkopie_DBR_DBS_Formeln()
ActiveSheet.Unprotect "Passwort"
Calculate
Dim Bereich As Range
Range("A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
Endspalte = Selection.Columns(Selection.Columns.Count).Column
EndZeile = Selection.Rows(Selection.Rows.Count).Row
Startspalte = 1
Startzeile = 1
Set Bereich = Range(Cells(Startzeile, Startspalte), Cells(EndZeile, Endspalte))
For Each Z In Bereich
On Error Resume Next
If InStr(Z.Formula, "DBR") Or _
InStr(Z.Formula, "DBS") Or _
InStr(Z.Formula, "SUBNM") Or _
InStr(Z.Formula, "VIEW") Or _
InStr(Z.Formula, "DIMNM") Then
Z.Value = Z.Value
End If
Next Z
Range("A1").Select
Mldg = "Alle Formeln wurden wertkopiert"
MsgBox (Mldg)
Range("A1").Select
ActiveSheet.Protect "Passwort"
End Sub

Um Fehler "abzufangen" gibt es an zahlreichen Stellen in der Arbeitsmappe folgende Formeln: WENN(ISTNV(DBRW…
Diese Formeln werden aktuell nicht wertekopiert. D.h. ich benötige eine Erweiterung der Befehle, damit auch alle o.g. Formeln im Falle Wenn(ISTNV( gleich behandelt werden. Leider habe ich nur ganz marginale Kenntnisse um benötige daher eure Unterstützung! Vielen Dank!
Mirco

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

Betreff
Datum
Anwender
Anzeige
AW: Wertekopie: Erweitern
30.01.2018 08:02:37
Sepp
Hallo Mirco,
wenn du ALLE Formen in Werte umwandeln willst, dann reicht
ActiveSheet.UsedRange = ActiveSheet.UsedRange.Value
Gruß Sepp

AW: Wertekopie: Erweitern
30.01.2018 08:14:17
Mirco
Nein, nicht alle - lediglich die o.g. speziellen Datenbankformeln bzw. wenn diese ebene mit Wenn(istNV in Kombination sind). Alle weiteren Excelformeln wie Summen etc. sollen erhalten bleiben.
AW: Wertekopie: Erweitern
30.01.2018 08:47:12
Sepp
Hallo Mirco,
teste mal.
Sub Wertkopie_DBR_DBS_Formeln()
Dim Bereich As Range, rng As Range, rngValue As Range
Dim varFormula As Variant, lngIndex As Long

varFormula = Array("DBR", "DBS", "SUBNM", "VIEW", "DIMNM")

With ActiveSheet
  .Unprotect "Passwort"
  
  .Calculate
  
  On Error Resume Next
  Set Bereich = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
  On Error GoTo 0
  Err.Clear
  
  If Not Bereich Is Nothing Then
    For Each rng In Bereich.Cells
      On Error Resume Next
      For lngIndex = 0 To UBound(varFormula)
        If rng.Formula Like "*" & varFormula(lngIndex) & "*" Then
          If rngValue Is Nothing Then
            Set rngValue = rng
          Else
            Set rngValue = Union(rngValue, rng)
          End If
        End If
      Next
    Next
    If Not rngValue Is Nothing Then rngValue = rngValue.Value
    MsgBox "Alle Formeln wurden wertkopiert"
  End If
  .Protect "Passwort"
End With

Set Bereich = Nothing
Set rngValue = Nothing
End Sub

Gruß Sepp

Anzeige
AW: Wertekopie: Erweitern
30.01.2018 09:21:13
Mirco
leider wird dann Null in die Zellen geschrieben und nicht die richtigen Werte.
AW: Wertekopie: Erweitern
30.01.2018 09:45:33
Sepp
Hallo Mirco,
kann ich mir nicht vorstellen! Kannst due eine Beispieltabelle hochladen?
Gruß Sepp

AW: Wertekopie: Erweitern
30.01.2018 12:01:30
Mirco
Hallo Sepp,
es geht doch, aber scheinbar nur auf dem "aktiven" Blatt und nicht in der gesamten Arbeitsmappe. Das war vorher nämlich möglich. Kann das so wieder angepasst werden, dass die gesamte Mappe durchläuft?
AW: Wertekopie: Erweitern
30.01.2018 12:10:50
Sepp
Hallo Mirco,
also der von dir gezeigte Code lief bestimmt nicht über alle Blätter!
Ist aber kein Problem.
Sub Wertkopie_DBR_DBS_Formeln()
Dim Bereich As Range, rng As Range, rngValue As Range
Dim varFormula As Variant, lngIndex As Long
Dim objWS As Worksheet

varFormula = Array("DBR", "DBS", "SUBNM", "VIEW", "DIMNM")

For Each objWS In ThisWorkbook.Worksheets
  With objWS
    .Unprotect "Passwort"
    .Calculate
    On Error Resume Next
    Set Bereich = .UsedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    Err.Clear
    If Not Bereich Is Nothing Then
      For Each rng In Bereich.Cells
        On Error Resume Next
        For lngIndex = 0 To UBound(varFormula)
          If rng.Formula Like "*" & varFormula(lngIndex) & "*" Then
            If rngValue Is Nothing Then
              Set rngValue = rng
            Else
              Set rngValue = Union(rngValue, rng)
            End If
          End If
        Next
      Next
      If Not rngValue Is Nothing Then rngValue = rngValue.Value
    End If
    .Protect "Passwort"
  End With
Next

MsgBox "Alle Formeln wurden wertkopiert"

Set Bereich = Nothing
Set rngValue = Nothing
End Sub

Gruß Sepp

Anzeige
AW: Wertekopie: Erweitern
30.01.2018 13:37:24
Mirco
Ich habe nun herausgefunden, warum ich dachte, dass Nullwerte eingefügt wurden (wurden faktisch auch). Es gibt es zentrales Parameterblatt und dort sind an mehrere Stellen =SUBNM-Formeln
=SUBNM("server:dimension";"";25)
Die Zahl am Ende gibt den Parameter eines Elements wieder (z.B. 2018). In diesem Register wird in alle Zellen der "erste" Treffer wertkopiert, obwohl eigentlich verschiedene Parameter angesprochen werden und somit unterschiedliche Jahre oder Produktgruppen ausgewählt sind.

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige