Zellenlänge auslesen und Werte hinzufügen

[Darth Sidious]

Hallo,

ich möchte mit VBA folgende Lösung erreichen:

Ich habe in Spalte A ab Zelle 2 immer eine Zeichenkette (z.B. "356894" oder "254").

Diese Zeichen müssen allerdings 15 Stellen lang sein. Der Anfang der Kette soll immer mit einem "INC" beginnen und anschließend soll zwischen INC und der Zeichenkette (wie oben im Beispiel) nullen hinzugefügt werden.

Beispiel:

Alter Zelleinhalt

A2 = "35654"

A3 = "2548964"

Neuer Zelleinhalt

A2 = "INC000000035654"

A3 = "INC000002548964"

Und weis ich leider nicht wie ich die Zellenwerte auf ihre Länge hin überprüfe und Zahlen/Ziffern dem Wert hinzufüge. Hat da jemand eine Idee?

[Amenos]

Hallo,

mit Len(Zelle) bekommst die Länge des Inhalts der Zelle. Den String vervollständigen kannst wohl über auslesen und dann sowas in der art : tmpstring = "INC" + tmpstring.

[Kaeyu]

Für die führenden 0en müsstest du davor allerdings noch den string umformatieren. Sonst bekommst du anstatt INC000000035654, INC35654 raus.

Wenn es immer INC + eine 12 stellige Zahl ist, dann einfach

tmpstring = "INC" + format(tmpstring,"000000000000") 

Macht aus 35654 -> 000000035654

Bearbeitet 27. September 2010 von Kaeyu

[Darth Sidious]

Danke Amenos, Len war der Befehl den ich gesucht habe.

Nun poste ich nochmal den code für alle die von das selbe Problem haben

...

    INC = Len((Cells(i, 1)))

    zellenwert = Cells(i, 1)

    If INC <= 15 Then

        Select Case INC

            Case 1

                Cells(i, 1) = ("INC00000000000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 2

                Cells(i, 1) = ("INC0000000000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 3

                Cells(i, 1) = ("INC000000000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 4

                Cells(i, 1) = ("INC00000000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 5

                Cells(i, 1) = ("INC0000000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 6

                Cells(i, 1) = ("INC000000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 7

                Cells(i, 1) = ("INC00000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 8

                Cells(i, 1) = ("INC0000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 9

                Cells(i, 1) = ("INC000" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 10

                Cells(i, 1) = ("INC00" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 11

                Cells(i, 1) = ("INC0" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case 12

                Cells(i, 1) = ("INC" & zellenwert)

                speicher2(i) = Cells(i, 1)

            Case Else

                speicher2(i) = Cells(i, 1)

        End Select

...

Danke euch

[MartinSt]

Nur gut dass die gewünschte Länge 15 und nicht 3.567 ist, sonst hättest du ein paar Meter case-Zweige gebaut.

Mit dem Hinweis von Kaeyu bzw. mit einer dynamischen Festlegung des Präfixes anhand der Länge hättest du dir allerdings 95% des Codes und damit auch das unschöne select-case sparen können.

[Schaelle]

Aufbauend auf der Idee von Kaeyu:

Function GetInc(AStr As String) As String

    GetInc = "INC" + Format(AStr, String(12, "0"))

End Function

Eine Beispielnutzung (angelehnt an dem von Darth Sidious):

If Len(GetInc(ActiveCell.Value)) <= 15 Then ActiveCell.Value = GetInc(ActiveCell.Value)

[Klotzkopp]

Nun poste ich nochmal den code für alle die von das selbe Problem haben

Das einzige, was diesen Code von The-Daily-WTF-Material trennt, ist die Anzahl der Cases. Den sollte man allenfalls als Beispiel dafür stehen lassen, wie man es nicht macht.

Zwölfmal fast identischer Code. Das schreit praktisch nach einer besseren Lösung.

Eine Alternative wurde dir hier ja schon aufgezeigt.

[Darth Sidious]

So,

nun hat mich doch der Ehrgeiz gepackt und ich hab nochmal etwas umstrukturiert.

Hier mal der ganze Code:

Private Sub cmdCreate_Click()

'Dekleration der verwendeten Variablen

Dim i As Integer

Dim x As Long

Dim y As Long

Dim q

Dim Code

Dim INC As Long

Dim zellenwert

Dim speicher1

Dim speicher2(0 To 1000)

Dim speicher3

Dim länge


If cboWhat = "SLG & Zeitraum" Then

    Form2.Show

    ElseIf cboWhat = "Incident ID" Then

End If

If cboWhat = "SLG & Zeitraum" Then

    Exit Sub

End If



'___________Bereich festlegen___________

    Set StartBereich = Application.InputBox _

        ("Bitte den Startbereich eingeben " & vbCrLf & _

        "(z.B.: A1) in der sich die erste" & vbCrLf & _

        "Ticketnummer befindet.", "Startbereich festlegen", "A1", Type:=8)

        With Worksheets(1).Range(StartBereich.Address)

            Spalte1 = .Columns(.Columns.Count).Column

            Zeile1 = .Rows(.Rows.Count).Row

        End With


    Set EndBereich = Application.InputBox _

        ("Bitte die letzte Zelle eingeben " & vbCrLf & _

        "(z.B.: A1) in der sich eine" & vbCrLf & _

        "Ticketnummer befindet.", "Startbereich festlegen", "A1", Type:=8)

        With Worksheets(1).Range(EndBereich.Address)

            Spalte2 = .Columns(.Columns.Count).Column

            Zeile2 = .Rows(.Rows.Count).Row

        End With


Schleifenlänge = Zeile2 - Zeile1

q = Spalte1

For i = Zeile1 To Zeile2

    'Abfrage ob die Schleife beendet werden soll (um Zeit und Rechenleistung zu sparen)

    If Cells(i, q) = "" Then

        y = y + 1

            If y = 3 Then

                Exit For

            End If

    End If


    '===================

    'Erstellen des Codes

    INC = Len((Cells(i, 1)))

    zellenwert = Cells(i, 1)

    'Auffüllen der Zahlen mit INC / xx Nullen


    If Left(Cells(i, q), 3) <> "INC" Then

        speicher2(i) = "INC" + Format(Cells(i, q), String(12, "0"))

        Cells(i, q) = speicher2(i)

        Else

            speicher2(i) = Cells(i, q)

    End If


y = 0

        If speicher2(i) = "" Then

            y = y + 1

                If y = 10 Then

                    Exit For

                End If

            Else

    speicher1 = speicher1 & " " & "'Incident ID*+'=" & Chr(34) & speicher2(i) & Chr(34) & " OR "

    End If

Next i

länge = Len(speicher1)

x = länge - 4

speicher1 = Left(speicher1, x)

Code = speicher1



'Ausgabe des Ergebnisses

txtSolution = Code

MsgBox "Ihr Suchcode wurde erfolgreich erstellt!"

End Sub

[MartinSt]

Mal ein paar Anmerkungen:

Aufgrund der fehlenden Formatierung ist der Code und die Logik schwer verständlich.

Warum deklarierst du einzelne Variablen, andere wiederum nicht? Arbeitest du mit Option Explicit oder sind das globale Variablen, wenn ja -warum?

Benenne die Variablen sinnvoll, unter x,y,q kann man sich nichts Sinnvolles vorstellen.

Warum berechnest du Schleifenlänge = Zeile2 - Zeile1 ohne Schleifenlänge jemals zu verwenden?

Die Festlegung von Start/Endbereich über Inputboxen führt höchstwahrscheinlich zum Crash, wenn dort etwas anderes als eine sinnvolle Zelladresse eingegeben wird.

Bist du dir bewußt, welchen impliziten Typ die Variablen haben, die du typlos deklarierst?

Was passiert mit speicher2, wenn die expliziten Arraygrenzen überschritten werden?

mMn ist dieses Array eh überflüssig, da du innerhalb der Schleife sowieso nur auf dem i-ten Element arbeitest.

Gruß Martin

[Darth Sidious]

x y und q ....

Verwende ich immer für Zahlenwerte die ich nicht häufig verwende, wie z.B. (i) für For Next Schleifen, oder y für Zeilen oder Spaltenangaben.

Die Variable ist noch ein überbleipsel aus dem alten Code. Die wird eigentlich nicht mehr verwendet.

Was meinst du genau mit den Inputboxen?

Der AW wird hier ja aufgefordert die erste Zelle und die letzte Zelle des gewünschten Bereiches zu markieren. Was anders als "$B$6" wird da nicht drin stehen.

speicher2() brauch ich für den Code der ausgegeben werden soll. Der Code ändert sich ja Variable mit jeder Zelle (--> Am Code wird nach jeder Zelle etwas hinzugefügt).

Der Array speicher ist bewusst gewählt. Es werden mit sicherhit nie mehr als 1000 benötigt. Der Code der erstellt wird ist für eine bestimmte Anwendung, und diese verkraftet auch nur einen Suchcode bis zu einer gewissen länge (Ich meine es waren 30.000 Zeichen maximal). Wenn dieser Wert überschritten wird, wird wohl ein Laufzeitfehler oder sowas augespuckt. Aber da hast du recht, hier könnte man eventuell noch eine Abfrage mit rein basteln.

Hier muss ich auch erwähnen dass ich schon wesentlich "sauberen" Code erstellt habe. Das war ja nur der Code von ca. 3 Stunden mühseligem Google-Suchen, Nachdenken schreiben des Codes an sich.

Aber danke für die Hinweise.

[MartinSt]

Hallo,

ich bin zwar nicht mehr 100% fit in VB/VBA aber zumindest unter VB deklariert "Dim q" keine Zahl sondern einen Variant oder Object; das hat dann den Effekt, dass der Compiler keine effektive Typprüfung mehr machen kann und zur Laufzeit unnötigerweise gecastet werden muß.

Mit den Inputboxen meinte ich z.B.

Set StartBereich = Application.InputBox _

("Bitte den Startbereich eingeben "...

Wenn der Anwender sich hier vertippt, fliegt im nachfolgenden

With Worksheets(1).Range(StartBereich.Address) ein Fehler.

Ich würde den AW hierfür lieber Spalte/Zeile als Zahl eingeben lassen und diese Eingabe dann validieren.

speicher2() brauch ich für den Code der ausgegeben werden soll.

Da hätte es auch ein String getan, an den du mit & anhängst.

Gruß Martin