Formeln mit VBA als Werte einfügen

Bild

Betrifft: Formeln mit VBA als Werte einfügen von: Michael Engelhardt
Geschrieben am: 07.04.2005 11:39:46

Liebe Excel Gemeinde,

wer kann mir bei folgendem Problem helfen.

Ich habe eine Datei mit 11 Blättern. Jetzt sind verteilt über diese
Blätter Formeln enthalten die auf unser OLAP Tool ALEA zurückgreifen.
Um genau zu sein dürften es von der Art her nur 3 Formeltypen sein.

=DE.NAME
=AT.GET
=DBGET

Ich hätte gerne, das ein Makro in einer Schleife jedes Blatt
durchsucht und wenn es in einer Zelle eine der drei Formeln findet
die Formel in dieser Zelle durch den Wert ersetzt bis alle Formeln die auf ALEA zurückgreifen als Werte in der Zelle stehen.

Ziel ist es, dass ein anderer User der kein ALEA hat diese Datei
normal benutzen kann ohne das er #WERT, #NAME oder sonstige Fehler
bekommt.

Und die Kür wäre dann noch, wenn nach Ende dieses Durchlaufs ein "Save
as" aufgerufen wird damit das Original nicht versehentlich
überschrieben wird.

Beste Grüße
Michael

Bild


Betrifft: AW: Formeln mit VBA als Werte einfügen von: Tobias Marx
Geschrieben am: 07.04.2005 11:41:53

Servus!

Wieso machst du es dir so kompliziert? Markiere alles, kopiere es, geh auf Bearbeiten/Inhalte einfuegen - Werte. Und schon hast du alles, was du brauchst. Wenn dus unbedingt als Makro haben willst, kannst du ja den Recorder mitlaufen lassen.

Gruss

Tobias


Bild


Betrifft: AW: Formeln mit VBA als Werte einfügen von: Michael Engelhardt
Geschrieben am: 07.04.2005 11:56:36

Tja leider ist die Welt nicht so einfach. Die "normalen" Excel Formeln wären dann natürlich auch Werte, und das darf nicht sein.

Es geht hier wirklich darum ganz explizit diese Zelle zu finden, wo diese Formeln vorkommen, und dann auch nur in dieser Zelle den Wert einzufügen.

Die Frage bleibt also bestehen!

Gruß
Michael


Bild


Betrifft: AW: Formeln mit VBA als Werte einfügen von: Ralf (Schwabenland)
Geschrieben am: 07.04.2005 13:24:11

Hallo Michael,

habe dir mal ein Beispiel gebastelt:

https://www.herber.de/bbs/user/20819.xls

Hoffe es hilft.

Gruß
Ralf


Bild


Betrifft: Das wäre es... von: Michael Engelhardt
Geschrieben am: 07.04.2005 14:07:39

Hallo Ralf,

genau das wäre die Lösung. Deine Bsp. Datei funktioniert tadelos. Aber es mangelt wohl an VBA Kenntniss. Ich habe den Code, der bei Dir in Modul 2 gestanden hat in meine Datei in Modul 1 kopiert. Da gab es nichts anders. Dann habe ich über Extras dann Makros den Code ausgeführt. Aber die Formeln wurden nicht ersetzt.

Ich bräuchte nochmal Deine Hilfe. Es wäre ultraklasse, wenn es bei der Speichern abfrage einen Durchsuchen Knopf geben würde, wo man dann den Speicherort wählen könnte.

Aber das nur als Sahnestück. Ich will nicht gleich den ganzen Arm greifen, wenn man nur den Finger hingelhalten bekommen. :-)

Vielen Dank!!!
Michael


Bild


Betrifft: Dann.... von: Ralf (Schwabenland)
Geschrieben am: 07.04.2005 14:12:08

Hallo Michael,

lade doch mal bitte eine Beispieldatei hoch, dann schau ich mir das an.

Gruß
Ralf


Bild


Betrifft: AW: Dann.... von: Michael Engelhardt
Geschrieben am: 07.04.2005 14:22:40

Hi Ralf,

hier die Datei. Ich habe sie auf zwei Blätter gekürzt. Denn mit allen 11 ist sie ca. 5MB groß. Aber das dürfte ja der Sache keinen Abbruch machen.

https://www.herber.de/bbs/user/20824.xls


Bild


Betrifft: Das wars.... von: Ralf (Schwabenland)
Geschrieben am: 07.04.2005 15:25:22

Hallo Michael,

hier das fertige Beispiel:

https://www.herber.de/bbs/user/20830.xls

Habe es an deiner Datei ausprobiert. Geht. Je nach Anzahl der Tabellenblätter kann das natürlich etwas dauern. In meinem Beispiel habe ich in E1 mal eine deiner langen Formeln eingetragen. Dateidialog ist auch drin, aber VORSICHT - im Moment werden vorhandene Dateien kommentarlos überschrieben. Wenn du das nicht willst, musst du die beiden Zeilen Application.DisplayAlerts = False/True auskommentieren oder löschen.

Hoffe es hilft.

Gruß
Ralf


Bild


Betrifft: Laufzeit von: Michael Engelhardt
Geschrieben am: 07.04.2005 15:54:10

Hi,

danke schon mal für Mühe. Ich denke das es funktioniert. Aber nach einer Laufzeit von ca. 8 Minuten habe ich es abgebrochen. Könnte man die Laufzeit verkürzen, indem man dies auf einen Bereich einschränkt?

Völlig ausreichend wäre der Bereich A1:BZ500


Sub Formel_Wert()
Dim wks As Worksheet
Dim Zelle As Range
Dim Bereich As Range
Dim sFile As String
Application.ScreenUpdating = False
  For Each wks In Worksheets
    Set Bereich = wks.UsedRange
      For Each Zelle In Bereich
        If Zelle.FormulaR1C1 Like "=" & "DE.NAME" & "*" Or _
           Zelle.FormulaR1C1 Like "=" & "AT.GET" & "*" Or _
           Zelle.FormulaR1C1 Like "=" & "DBGET" & "*" Then
           Zelle.Copy
           Zelle.PasteSpecial Paste:=xlValues
        End If
      Next
  Next wks
sFile = Application.GetSaveAsFilename(InitialFileName:="Export_", fileFilter:="Excel-Dateien, *.xls")
If sFile = "Falsch" Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=sFile
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub



Bild


Betrifft: Jetzt aber.... von: Ralf (Schwabenland)
Geschrieben am: 07.04.2005 16:06:53

Hallo Michael,

mach aus folgender Zeile:
Set Bereich = wks.UsedRange

diese hier:
Set Bereich = wks.Range("A1:BZ500")

Hoffe es hilft.

Gruß
Ralf


Bild


Betrifft: Funktioniert aber.... von: Michael Engelhardt
Geschrieben am: 07.04.2005 16:15:31

Hi Ralf,

tut mir leid, dass ich so nervig bin. Aber es funktioniert alles prima. Laufzeit ca. 45s. Aber ich habe jetzt gemerkt das ich auch so eine Formel geschrieben habe. Leider muss die auch so sein.

=WENN(ISTFEHLER(DBGET(Control!$A$1;Control!$A$2;$M$10;$R$24;Control!$B$5;$A$12;"sum";"sum";"sum";$B276;"K200")/$N276)=WAHR;0;DBGET(Control!$A$1;Control!$A$2;$M$10;$R$24;Control!$B$5;$A$12;"sum";"sum";"sum";$B276;"K200")/$N276)

Kann man in der Suche mit einer Art Wildcard arbeiten, so das egal wo diese Formel-Ausdrücke stehen der Wert der Zelle geschrieben wird?

Viele Grüße und tausend Danke schön!
Michael


Bild


Betrifft: Ich denke das war es.... von: Ralf (Schwabenland)
Geschrieben am: 07.04.2005 16:36:25

Hallo Michael,

eine schwierige Geburt. Jetzt aber:

Option Explicit


Sub Formel_Wert()
Dim wks As Worksheet
Dim Zelle As Range
Dim Bereich As Range
Dim sFile As String
Application.ScreenUpdating = False
  For Each wks In Worksheets
    Set Bereich = wks.Range("A1:BZ500")
      For Each Zelle In Bereich
        If Zelle.FormulaR1C1 Like "=" & "*" & "DE.NAME" & "*" Or _
           Zelle.FormulaR1C1 Like "=" & "*" & "AT.GET" & "*" Or _
           Zelle.FormulaR1C1 Like "=" & "*" & "DBGET" & "*" Then
           Zelle.Copy
           Zelle.PasteSpecial Paste:=xlValues
        End If
      Next
  Next wks
sFile = Application.GetSaveAsFilename(InitialFileName:="Export_", fileFilter:="Excel-Dateien, *.xls")
If sFile = "Falsch" Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=sFile
Application.DisplayAlerts = True
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Hoffe es hilft.

Gruß
Ralf


Bild


Betrifft: Endlich!!!! Tausend Dank! Es funktioniert alles. von: Michael Engelhardt
Geschrieben am: 07.04.2005 16:59:15




Bild


Betrifft: Danke für die Rückmeldung! O.w.T. von: Ralf (Schwabenland)
Geschrieben am: 07.04.2005 17:22:32

:-)


 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formeln mit VBA als Werte einfügen"