Office: (Office 2007) Text in Zahl umwandeln mit VBA

Hallo Zusammen,

ich bin hier gerade dabei ein größeres Makro zu schreiben und habe dazu auch schon ein paar Mal gepostet.

Folgendes Problem ist aufgetreten:

Ich exportiere einen Report aus einem firmeneigenen Datenbanksystem in Excel. Dort sind dann alle Zellen als Text formatiert. Einige Spalten muss ich aber in Zahlen umwandeln. Die Zellen dieser Spalten besitzen links oben ein grünes Dreieck. Wenn man eine Zelle mit der Maus markiert, erscheint ein Ausrufezeichen mit der Nachricht "Die Zahl in dieser Zelle ist als Text formatiert (...)". Jetzt ist es ja ganz einfach diese in Zahlen umzuwandeln, indem man das Ausrufezeichen anklick und auf "in eine Zahl umwandeln" klickt. Nun brauche ich diesen Vorgang aber unbedingt in meinem Makro als VBA-Code. Ich habe schon versucht diesen Vorgang aufzuzeichnen, aber das funktioniert nicht. Es erscheint im Code höchsten die Zelle, die markiert wurde. Der Vorgan des Änderns von Text auf Zahl wird nicht angezeigt.

Kann mir jemand sagen, woran das liegt. Bitte nicht den Vorschlag bringen es über Zellen formatieren zu machen und dann aufzuzeichen. Dies hat irgendwie nicht den gleichen Effekt. Das Problem ist nämlich, dass ich diese Spalten mit einer anderen Datei über SVerweis verknüpfen muss und das funzt nur, wenn ich die Zellen über dieses Ausrufezeichen umwandle.

Uff....so viel Text für so ein kleines Problem. Hoffe mir kann jemand helfen. :-)

:)

 

Moin Nick,

zeichne mal folgende Aktion auf:

gebe in eine Zelle eine 1 ein und kopiere sie. Markiere die zu bearbeiten Zellen und füge Inhalte ein / Multiplizieren.

 

Hallo Woody,

vielen Dank für Deine Antwort. Dein Tip hat so funktioniert. Ich habe das Ganze jetzt mal in eine Schleife eingebaut. Nun sieht es wie folgt aus:

Sub TextInZahl()

Range("AN1").Activate
ActiveCell.FormulaR1C1 = "1"
Range("AN1").Select
Selection.Copy

Dim i As Integer
For i = 4 To Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(i, 1).Activate
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
Next i

End Sub

Das Makro läuft auch durch. Ist es vielleicht möglich hier noch etwas zu tun, um die Rechenzeit etwas zu verringern bzw. die Geschwindigkeit des Makros zu erhöhen? Vielleicht durch das Verhindern des Screenupdating?

Würde mich freuen, wenn Du nochmal was posten könntest.

 

Hi Nick!
Ich hatte auch einmal ein ähnliches Problem. Das habe ich damals (angepasst) so gelöst:
Code:

Da Du aber im ersten Post geschrieben hast, dass es über das Formatieren der Zellen nicht klappt, bin ich mir nicht sicher, ob Dir der obige Code etwas hilft (evtl. nochmal mit ... .NumberFormat = "0.00" versuchen). Deswegen habe ich unten Deinen Code etwas "gekürzt" durch Weglassen von .activate und .select, was bekanntermaßen Zeit frisst, und Verzicht auf die Schleife:
Code:

Ich freue mich immer über Rückmeldungen!
Grüße, Ulrich

 

PS: Du hast im ersten Post von mehreren Spalten geschrieben. Um den Code weiter zu verfeinern kannst Du auch mehrere Bereiche mit dem Befehl "union()" vereinen.
Grüße, Ulrich

 

Hallo Ulrich,

alle Achtung ich bin begeistert! :-) Das dauert ja jetzt nur noch einen Wimpernschlag lang. Vielen Dank.

Kannst Du mir noch sagen, was ich ändern muss, wenn ich diesen Code auch für andere Spalten verwenden möchte? Beispielsweise für Spalte B. Ich weiß ich muss den Spaltenindex ändern. Leider habe ich jetzt über ein Jahr lang nichts mehr mit VBA gemacht und arbeite erst seit kurzem wieder damit. Man vergißt ja so schnell.

Kann es sein, dass ich dann folgendes ändern müßte:

Range(Cells(4, 2) =>also hier die 1 durch eine 2 ersetzen. Das ist doch dann der Spaltenindex?

 

Hi Nick!
nur für Spalte B:

Code:

="B4:B????"

oder gleich A und B zusammen:
Code:

="A4:B????"

Welchen Code benutzt Du denn?
Viele Grüße, Ulrich

 

Hallo Ulrich,

ich meinte, wenn ich ihn nur für Spalte B benutzen möchte. Also praktisch so, wie Du es jetzt im ersten Beispiel dargestellt hast. Das paßt dann also.

Vielen Dank nochmal.

 

Sorry, hatte nochmal editiert*rolleyes.gif*

Grüße, Ulrich

 

Hallo Ulrich,

ja es sind auch mehrere Spalten. Ich dachte aber, ich lasse sie nacheinander abarbeiten. Deshalb habe ich folgendes probiert:

Sub TextInZahl2()
Range("AN1") = 1
Range("AN1").Copy

Range(Cells(4, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 2), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 2)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 18), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 18)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 19), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 20)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 20), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 20)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 21), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 21)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 22), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 22)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 23), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 23)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 24), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 24)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 25), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 25)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 26), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 26)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 27), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 27)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 28), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 28)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 29), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 29)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 30), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 30)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 31), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 31)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 32), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 32)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 33), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 33)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 34), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 34)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 35), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 35)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Range(Cells(4, 36), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 36)).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False

Application.CutCopyMode = False 'nicht zwingend notwendig
Range("AN1") = "" 'löscht die 1 aus der ersten Zeile
End Sub

Leider hat das nicht so funktioniert, wie ich mir das vorgestellt habe. Der Text soll also in den Spalten 1 und 2 sowie 18 bis 36 in Zahlen umgewandelt werden. Wenn ich es wie hier eingefügt mache, werden nur die ersten beiden Spalten in Zahlen umgewandelt. Der Rest bleibst unberücksichtigt, was mich etwas verwirrt.

 

Hi Nick!
Bei mir läuft es einwandfrei für alle gewünschten Spalten durch.
Kleiner Verbesserungsvorschlag mit einer Schleife (vlt. behebt er ja auch das Problem *wink.gif* ):
Code:

Viele Grüße, Ulrich

 

Hallo Ulrich,

danke für diese kompakte Lösung. Ich habe es gerade getestet, aber ab Spalte 18 wird wieder nicht umgewandelt. Man sieht, dass es durch die Zellen läuft, aber es passiert wohl nichts. In den erste beiden Spalten funktioniert es einwandfrei. Ich verstehe das nicht. Es erscheint auch keine Fehlermeldung.

 

Hast Du die Zellen irgendwie formatiert?

 

Nein sie sind nicht formatiert. Sie sind noch so, wie sie der Report ausgespuckt hat. Also so wie ganz am Anfang beschrieben. => "Diese Zelle ist als Text formatiert (....)"

Die ersten beiden Spalten sind genauso, aber da funktioniert es komischerweise. Der einzige Unterschied besteht daran, dass in den ersten beiden Spalten ganze Zahlen stehen und ab Spalte 18 Zahlen mit Komma, die aber trotzdem vor dem Starten des Makros die gleiche Formatierung haben. Könnte es vielleicht daran liegen?

 

Jupp, bei mir klappt die Methode auch nicht (immer) mit als Text gespeicherten Kommazahlen. Daher gehe ich zurück zu meinem ersten Vorschlag (Post #4):
zwei Codes, probier sie doch mal bitte aus :
Code:

Danke, Ulrich