Excel - Formel raus Wert rein -Kommentar behalten

Asked By Hans Alborg on 18-Jun-11 09:25 AM
[Excel 2007]

Hi Leute,

ich bin auf das alte Thema gesto?en in einem Excel-Blatt die Formeln durch
ihre Werte zu ersetzen.

Im Netz habe ich die 2 Methoden gefunden:

a) einen Bereich markieren und "...Selection.PasteSpecial Paste:=xlValues"

Das w?re prima f?r mich, wenn ich nicht die Kommentare behalten wollte, aber
die werden auch gel?scht und:

b) jede Zelle (in einem Bereich) einzeln abklappern und bearbeiten

For j = 1 To 55   ' Zeile 55 Z
For k = 1 To 29 'Spalte 29 S
If wsnew.Cells(j, k).HasFormula = True Then wsnew.Cells(j, k) =
wsnew.Cells(j, k).Value
Next k 'Spalte
Next j 'Zeile

Das ist mein jetziger Code. Problem: es dauert recht lange. Ich werd noch
eine Zelle des Blatts am Beginn rot f?rben und sp?ter wieder wei?, als

Gibt es was Schnelleres (wie Punkt a) wobei die Kommentare bleiben?
?hm, alles andere (Formate...) will ich auch behalten.

TIA,

Hans




Jörg_Eisenträger replied to Hans Alborg on 18-Jun-11 09:52 AM
Hi,


Versuch mal:

Markiere den Bereich, dann:

Dim z as Range
For Each z in Selection
z.Value = z.Value
Next

Ist vielleicht etwas schneller als b), Formatierungen und Kommentare
bleiben erhalten. [XL2003]


Gru?
J?rg
--
LPs auf CD brennen - so geht's: http://www.joergei.de/
E-Mail-Adresse existiert, wird aber nicht gelesen.
Claus Busch replied to Hans Alborg on 18-Jun-11 10:17 AM
Hallo Hans,

Am Sat, 18 Jun 2011 15:25:15 +0200 schrieb Hans Alborg:


weiterer Vorschlag:
Kommentare auslesen => Bereich markieren => Bereich kopieren und als
Wert wieder einf?gen => Kommentare wieder einf?gen.
Falls deine Tabelle nicht "Tabelle1" ist, bitte im Code anpassen. Code
liest nur Kommentare aus und setzt sie wieder ein. Kopieren und als Wert
einf?gen kannst du von Hand machen oder den Code anpassen:

Sub KommentareAuslesen()
Dim i As Integer
Dim rngZelle As Range
Dim LRow As Long

Worksheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Kommentare"
With Sheets("Kommentare")
.Range("A1") = "Kommentar"
.Range("B1") = "Zelle"

For Each rngZelle In Sheets("Tabelle1").UsedRange
If rngZelle.Comment Is Nothing Then
Else
LRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
.Cells(LRow, 1) = rngZelle.Comment.Text
.Cells(LRow, 2) = rngZelle.Address(0, 0)
End If
Next
End With
End Sub

Sub KommentareEinfuegen()
Dim i As Integer
Dim LRow As Long
Dim strCom As String
Dim strAdr As String

With Sheets("Kommentare")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LRow
strCom = .Cells(i, 1)
strAdr = .Cells(i, 2)
Application.Goto Sheets("Tabelle1").Range(strAdr)
With ActiveCell
.Comment.Delete
.AddComment strCom
End With
Next
End With
End Sub


Mit freundlichen Gr?ssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Claus Busch replied to Claus Busch on 18-Jun-11 10:44 AM
Hallo Hans,

Am Sat, 18 Jun 2011 16:17:39 +0200 schrieb Claus Busch:


es geht noch einfacher, ohne Schleife und Kommentare bleiben erhalten:

Sub Werte()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, 23)
.Value = .Value
End With
End Sub


Mit freundlichen Gr?ssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Hans Alborg replied to Claus Busch on 18-Jun-11 02:37 PM
Hallo Claus,



An sowas wie Hilfstabellen etc. hatte ich auch schon gedacht, war mir aber
zu umst?ndlich.



Dabei kommt in den Formelzellen #NV raus, leider. Die Kommentare sind aber
da.
Trotzdem gef?llt mir der Code. Die Abarbeitung ist sehr schnell.
Erst hatte ich die dritte Zeile als Fehler in Verdacht, weil bei meinem Code
nur rechts vom "=" das ".Value" steht, aber J?rgs Code l?uft, und da ist
diese Zeile genauso.
Darum, und weil ich keine Ahnung habe, was "SpecialCells(xlCellTypeFormulas,
23)" bedeutet, werde ich mich mal mit dieser (2.) Zeile Deines Codes
besch?ftigen.

Hans
Hans Alborg replied to Jörg_Eisenträger on 18-Jun-11 02:28 PM
Hi J?rg,



Mir ist aufgefallen da? Dein Code dem von Claus ?hnelt. Sein Ansatz ohne die
Zellen einzeln durchzugehen ist warscheinlich der richtige Weg, die
Geschwindigkeit ist unschlagbar und die Kommentare ?berleben das auch.
Leider werden dort die Formeln (noch) nicht umgewandelt. Aber solche Sachen
zu knacken ist ja der Spa? an der Sache!

Dein Code dagegen hat komplett funktioniert!
Mit der Zeile "Range("A1:AC56").Select" dazwischen hat es allerdings l?nger
gedauert als mein Code.

Ich werde mich dann mal an die Zeile
Das mit der "23" ist neu f?r mich.

Vielen Dank!

Hans
Claus Busch replied to Hans Alborg on 18-Jun-11 02:48 PM
Hallo Hans,

Am Sat, 18 Jun 2011 20:37:48 +0200 schrieb Hans Alborg:


bei mir ist der Code ohne Probleme gelaufen.
SpecialCells(xlCellTypeFormulas, 23) sind alle Zellen mit Formeln,
sowohl mit Zahlen, mit Text, mit Wahrheitswerten und mit Fehlern. Wenn
du nur Zahlen aus Ausgabe hast, probiere es mal nur mit:
SpecialCells(xlCellTypeFormulas, 1)


Mit freundlichen Gr?ssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Claus Busch replied to Claus Busch on 18-Jun-11 02:51 PM
Hallo Hans,

Am Sat, 18 Jun 2011 20:48:47 +0200 schrieb Claus Busch:


und falls du Zahlen UND Text hast, probiere es mit:
SpecialCells(xlCellTypeFormulas, 3)


Mit freundlichen Gr?ssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Hans Alborg replied to Hans Alborg on 18-Jun-11 03:01 PM
Nachtrag:



Das blinde Huhn und so....

With ActiveSheet.UsedRange
.Value = .Value
End With

Damit geht es in Sekundenbruchteilen, die Formeln werden umgewandelt und die
Kommentare sind noch da.

Goo hat mich gleich auf eine englische Seite
(http://dmcritchie.mvps.org/excel/proper.htm) geschickt wo von etlichen
Problemen bei "SpecialCells..." die Rede ist.
Darum hab ich's mal ohne probiert :-)

Vielen Dank -Euch beiden- f?r die Hilfe!

Hans
Carlos Naplos replied to Hans Alborg on 19-Jun-11 01:48 AM
Hi

Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Selection.PasteSpecial Paste:=xlPasteComments

Gru?
Carlos

schrieb Hans Alborg am 18.06.2011 15:25:
Hans Alborg replied to Claus Busch on 19-Jun-11 04:43 AM
Hi Claus,



mit "xlCellTypeFormulas..." scheint Excel2007 nicht so zurechtzukommen (hier
in meinem Fall). Tats?chlich habe ich in einigen Formeln Text stehen. Bei
angezeigt, bei SpecialCells(xlCellTypeFormulas, 1) sind rund 2/3 der
Formelzellen zu #NV geworden, andere sind leer (sollten es aber nicht sein)
und in ein paar steht "19" drin (warum 19???).
Das Tabellenblatt enth?lt im Wesentlichen einen Kalender. Die Formeln lauten
(meistens) etwa "=B5+1", "=C5+1", sind also wie eine Kette von Zelle zu
Zelle (Tag zu Tag). Eventuell werden die Werte ja nicht in der Reihenfolge
der Tage umgerechnet sondern nach anderen Regeln, so da? der Anfangswert
(1.Januar) verloren ist und dann #NV eingetragen wird.
Es gibt zwar noch andere Bereiche mit Bezugsformeln, dann die
Feiertagsformeln usw., aber so ?hnlich k?nnte es auch da sein.

Immerhin, seit gestern 21:00 l?uft der Code ohne "SpecialCells". Wie ich das
sehe, ist das nur eine Filterung um die Zellen ohne Formel auszulassen. So
l?uft wohl alles ein bischen weniger schnell ab. Aber gegen meinen Eigenbau
ist das immer noch Spitze!

Hans
Claus Busch replied to Hans Alborg on 19-Jun-11 04:48 AM
Hallo Hans,

Am Sun, 19 Jun 2011 10:43:35 +0200 schrieb Hans Alborg:


ich habe den Code in xl2007 getestet und bei mir machte es keine
Schwierigkeiten.
Aber schau dir mal die L?sung von Carlos an. Auf die einfachen Dinge
kommt man meist gar nicht.


Mit freundlichen Gr?ssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Hans Alborg replied to Carlos Naplos on 19-Jun-11 05:04 AM
Hi Carlos,



Wow! Das l?uft auch superschnell durch.

Bei mir sieht Dein Code dann so aus ("wsnew" ist das Blatt):

wsnew.Range("A1:AC56").Select
With Selection
.Copy
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlPasteComments
End With

In der Vergangenheit haben mich die Profis hier aber vom selektieren
abgebracht, daher:

With wsnew.UsedRange
.Copy
.PasteSpecial Paste:=xlValues
.PasteSpecial Paste:=xlPasteComments
End With
wsnew.Range("W43").Select

Letzte Zeile ist aber n?tig, weil sonst alles markiert bleibt.

Ich kann jetzt nicht mehr sagen was schneller ist, obiger Code oder dieser:

With wsnew.UsedRange
.Value = .Value
End With

Immerhin sieht man, da? es mehr als eine Methode gibt.
Ist schon interessant!

Hans
Carlos Naplos replied to Hans Alborg on 03-Jul-11 04:57 AM
Hi Hans

schrieb Hans Alborg am 19.06.2011 11:04:

... schnipp ...

Die Application.CutCopyMode-Eigenschaft gibt den Status des Ausschneide-
oder Kopiermodus zur?ck oder legt diesen fest.

Mit Application.CutCopyMode = False legst Du fest, dass das
Application-Objekt weder im Ausschneide- noch im Kopiermodus ist.

Gru?
CN
Hans Alborg replied to Carlos Naplos on 10-Jul-11 08:02 AM
Prima, das rundet die Sache ab!

Inzwischen bin ich schon an was Anderem dran: ich ?berlege, in jedem Blatt
das ich bearbeite Informationen zu hinterlegen, als VBA- REM- Zeilen. So
eine Art Zeitstempel. Code per VBA l?schen kann ich schon, aber das?
So h?lt man sich fit...

Hans
Robert Feldmann replied to Hans Alborg on 10-Jul-11 04:36 PM
Hallo Hans,

wie w?re es mit einem ausgeblendeten Arbeitsblatt? Als "veryhidden" wird
das Blatt nicht zum Einblenden angeboten. Ist vielleicht etwas
?bersichtlicher.

Am 10.07.2011 14:02, schrieb Hans Alborg:

Robert

--
Nicht was Du sagst ist entscheidend, sondern was verstanden wird.
Hans Alborg replied to Robert Feldmann on 11-Jul-11 03:29 PM
Hi Robert,


Ich hab schon 2 ausgeblendete Bl?tter in der Mappe, w?re nicht das Problem.

Die Sache ist so: Ich bearbeite ein Blatt bis es "voll" ist, und kopiere es
Blattnamen. Damit ist das Blatt "archiviert".
Am Jahresende kommen etwa 150 Bl?tter zusammen. Da m?chte ich bei jedem
Screens) oder -was mein erster Gedanke war, eben im VBA- Code als REM-
Zeilen.
VBA-Text nimmt warscheinlich weniger Speicher in Anspruch, und es macht mehr
Spa?, mal was Neues zu testen (soll ja auch angezeigt und ggf. druckbar sein
per Button).

Schaff ich das nicht, geht halt auch die "Au?er-Screen-Variante".

Alles auf ein (ausgeblendetes) Blatt zu tun wird zu un?bersichtlich, finde
ich. Vielleicht kopiere ich auch mal so ein Blatt, dann w?re es auch
w?nschenswert, das "Blatttagebuch" mitzunehmen.

Naja, werd mal Goo...

Hans
Hans Alborg replied to Hans Alborg on 12-Jul-11 12:21 PM
Inzwischen viel Neues.
Damit
'---------------------------------------------------
Sub VBTagebuch()
' Historie f?r die versch. Bl?tter als REM- Zeilen eintragen
With
Application.VBE.ActiveVBProject.VBComponents(wshist.CodeName).CodeModule
For x = 1 To .CountOfLines
'.DeleteLines (x)
Next
.InsertLines x + 1,
.InsertLines x + 2, "' " & Date & " / " & Time & " " & HisText
End With
End Sub
'--------------------------------------------------

gebe ich dem Ende des Codes auf einem Blatt "wshist" zwei REM-Zeilen dazu.
Die Schleife mit x stammt von Google und dient nur zum herausfinden der
letzten Codezeile.
Das geht sicher besser, klappt aber erstmal.
Die Zeile mit Date, Time und dem String "HisText" ist der eigentliche
Eintrag.

Wo ich ein Ereignis mitschneiden m?chte kommt ungef?hr dieser Code rein:
' --------------------------- Historie erzeugen ------------------------
' Set wshist = ActiveSheet
'    HisText = ActiveSheet.Name & " wurde gefaxt"
'      Call VBTagebuch
' ----------------------------------------------------------------------

Die Ausgabe erledigt eine Userform "F_Tagebuch", die vor allem eine gro?e
Textbox enth?lt:
'--------------------------------------------------
Sub Tagebuch_show()
' Historie des aktiven Blatts anzeigen

Set wsakt = ActiveSheet
F_Tagebuch.Caption = "         Historie f?r Blatt: " & wsakt.Name

With
Application.VBE.ActiveVBProject.VBComponents(ActiveSheet.CodeName).CodeModule

For x = 1 To .CountOfLines
If x > 40 Then
txt2 = Application.VBE.ActiveVBProject.VBComponents _
(ActiveSheet.CodeName).CodeModule.Lines(x, 1)
txt1 = txt1 + Chr(13) + txt2
End If
Next
F_Tagebuch.TextBox1.Value = txt1
End With

F_Tagebuch.TextBox1.SetFocus
F_Tagebuch.Show
txt1 = "": txt2 = ""

End Sub
'---------------------------------------------

Das l?uft erstmal reibungslos. Ich habe aber - wie solls anders sein- ein
paar neue Fragen:

a) Gibts was Einfacheres als o. gezeigte X-Schleife?
nochmal:
'--------------------------------------
For x = 1 To .CountOfLines
'.DeleteLines (x)
Next
'--------------------------------------
Die Codezeilen werden ja durch mein Eingreifen st?ndig vermehrt, deshalb
geht keine feste Zahl. Das 'DeleteLines ist deaktiviert und nur ein
Platzhalter wie man sieht.

b) aus "ActiveCell.Column & "-" & ActiveCell.Row" m?chte ich einen String
z.B. "Range B6" erzeugen. Wie bekomme ich denn aus "ActiveCell.Column" den
Buchstaben? Mir f?llt nur was mit Abz?hlen ein:

c) die Userform. Gibt es eine Eigenschaft um die Fenster- (Userbox-) gr??e
per Maus zu ?ndern (wie ein "normales" Windowsfenster)?

d) und wenn ja, wie passe ich die Textbox (automatisch) an?

e) mein VBA- Codefenster (mh, den Ordnerbaum meine ich) ist vermurkst!
Module, Userformen sind nicht mehr in Ordnern zu sehen und werden mitsamt
denn Tabellenbl?ttern sortiert (landen ganz oben).
Wo/ Wie kann ich das ?ndern?

TIA,

Hans
Robert Feldmann replied to Hans Alborg on 12-Jul-11 01:53 PM
Hallo Hans,

ich habe sowas ?hnliches auch mal als "Logbuch" gehabt. Sollte
dokumentieren "wer", in "welchem Blatt", "was" ge?ndert hat. Das lie?
sich in dem Excelblatt super verfolgen, filtern, sortieren, auswerten
(Pivot) oder drucken.

Am 11.07.2011 21:29, schrieb Hans Alborg:

OK, das macht es schwieriger, wenn der Blattname nach dem Logbucheintrag
ge?ndert wird klappt das nat?rlich nicht.

Wenn es viele Eintr?ge werden ist das Auffinden eines bestimmten
Eintrags schwierig.


Ok, der Spa?faktor ist nat?rlich nicht zu wiederlegen ;-) Drucken k?nnte
man das Sheet auch. Zum Platzbedarf kann ich nichts sagen.

Robert

--
Nicht was Du sagst ist entscheidend, sondern was verstanden wird.
Hans Alborg replied to Robert Feldmann on 12-Jul-11 02:42 PM
Hi Robert,



Nanu "wer"? Evtl. Mappe ?ffnen nur mit pers. Pa?wort?

Inzwischen hast Du bestimmt mein Posting von 18:21 gesehen...



Die erste REM- Zeile des Blatts lautet etwa "Datum/ Zeit/ Blatt wurde
archiviert".
Sp?ter, aber auch vor dem archivieren, geht's immer mit Active.Sheet.

Mal sehen ob das reicht.



Mein Freund ist hier der VBA- Editor mit dem Fernglas oben links :-)
Immerhin stehen die Ereignisse auch in zeitlicher Reihenfolge untereinander.


Hm. Wenns zu dick wird, k?nnte man das Ganze mit Cut/Paste als *.txt
auslagern.

?brigens, was ich (in Bezug auf mein 18:21-Posting) doch noch verzapft habe:


Das klappt ja wirklich!

Hans
Robert Feldmann replied to Hans Alborg on 12-Jul-11 03:54 PM
Hallo Hans,

einiges m?chte ich mir vorher noch "in the wild" ansehen, sprich ich
werde mir eine Versuchsmappe erstellen. Daher erst sp?ter darauf Antworten.

Am 12.07.2011 18:21, schrieb Hans Alborg:

Hier m?sste eigentlich "Activcell.address" reichen.

Robert
--
Nicht was Du sagst ist entscheidend, sondern was verstanden wird.
Bernhard Sander replied to Hans Alborg on 13-Jul-11 06:00 AM
Hallo Hans,

Zumindest bis zur Spalte Z. Ab Spalte AA geht es nicht mehr.

Betreffs der Z?hlschleife:

Den "Platzhalter"-Kommentar kannst Du getrost weg lassen, die Schleife
funktioniert trotzdem.

Du kannst aber auch die ganze Schleife weg lassen.
In der Schleife wird x von 1 an hochgez?hlt. Sobald die obere Grenze
.CountOfLines ?berschritten wird bricht die Schleife ab. Danach steht x bei 1
mehr als .CountOfLines. Also kannst Du gleich statt der Schleife schreiben:

x = .CountOfLines + 1

Gru?
Bernhard Sander
Hans Alborg replied to Bernhard Sander on 13-Jul-11 10:45 AM
Hallo Bernd,




Mpf, daran hatte ich nicht gedacht! Roberts Tip, mit "ActiveCells.Address"
ist wohl die beste Alternative. Leider wird hier die Zelladresse so
ausgegeben: "$AM$28" und nicht "AM28". Das sieht in der Textbox
un?bersichtlich aus. In der Hilfe steht auch nichts ?ber andere Formate (ich
probiere trotzdem mal mit "Format", wer wei??).


Prima! Ich hab solange probiert (und "+1" kann ich auch weglassen).

Vielen Dank,

Hans
Claus Busch replied to Hans Alborg on 13-Jul-11 10:52 AM
Hallo Hans,

Am Wed, 13 Jul 2011 16:45:31 +0200 schrieb Hans Alborg:


wenn du die Adresse relativ haben m?chtest, dann bitte:
ActiveCell.Address(0, 0)


Mit freundlichen Gr?ssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Hans Alborg replied to Claus Busch on 13-Jul-11 05:13 PM
Hallo Claus,




Das ist die L?sung!
Ich glaube, REM- Zeilen f?r diese Historien- Aufgabe zu mi?brauchen war eine
gute Idee. Das ist ja so flexibel...
Naja, hab gelesen, da? eine Textbox nur 255 Zeichen aufnimmt!
Das teste ich aber mal morgen.

Gute Nacht!

Hans