Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

VBA: Worksheetfunction.substitute





Frage

Hallo, ich möchte eine Excelfunktion in VBA einbauen. Die Funktion soll mir das drittletzte Wort einer Zelle ausgeben. Leider kommt bei der Substitute funktion der Fehler "1004 - die Substitute-Eigeschaft des WorksheetFunction-Objektes kann nicht zugeordnet werden" hat da vielleicht jemand eine Ahnung was das Problem sein könnte? Unten der Code. Viele Grüße Oli Sub test() Text = Worksheets(2).Range("b11").Value With WorksheetFunction form = Left(Mid(Text, .Find("##", .Substitute(Text, " ", "##", Len(Text) - Len(.Substitute(Text, " ", "")) - 2)) + 1, 100), .Find(" ", Mid(Text, .Find("##", .Substitute(Text, " ", "##", Len(Text) - Len(.Substitute(Text, " ", "")) - 2)) + 1, 100)) - 1) End With

Antwort 1 von Beverly

Hi Oli,

du greifst auf die falsche Tabelle zu und B11 ist leer.

Bis später,
Karin

Antwort 2 von Oli00

Hi Karin,

vielen Dank das war richtig. Ich hatte einfach auf die falsche Excel Datei zugegriffen.

Jetzt habe ich aber plötzlich ein ganz anderes Problem, denn meine Suchfunktion funktioniert leider nicht mehr...

Hier kommt der Fehler 91 "Objektvariable oder With-Blockvariable nicht festgelegt.

Der Code dazu steht unten und der Problempunkt ist gekennzeichnet.

Auch mal zu der Frage davor. Wie bist du so schnell auf die Lösung gekommen?!

Sub spieler_füllen()

no_pl = Worksheets(1).Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For i = 8 To no_pl


spieler = Worksheets(1).Cells(i, 1).Value
Set c = Worksheets(2).Range("A1:AZ6000").Find(spieler, LookIn:=xlValues)
wo = c.Address
wo_line = c.Row
wo_col = c.Column

pl_bew = Worksheets(2).Cells(wo_line + 3, wo_col).Value
pl_pos = Worksheets(2).Cells(wo_line + 4, wo_col).Value
pl_form = Worksheets(2).Cells(wo_line + 1, wo_col).Value
With WorksheetFunction
pl_form_kurz = Left(Mid(pl_form, .Find("##", .Substitute(pl_form, " ", "##", Len(pl_form) - Len(.Substitute(pl_form, " ", "")) - 2)) + 1, 100), .Find(" ", Mid(pl_form, .Find("##", .Substitute(pl_form, " ", "##", Len(pl_form) - Len(.Substitute(pl_form, " ", "")) - 2)) + 1, 100)) - 1)
End With

If pl_pos = "" Then
GoTo leer
Else
inp_pos = Worksheets(1).Range("C4:M4").Find(pl_pos, LookIn:=xlValues).Column
End If

If Worksheets(1).Cells(i, inp_pos).Value <> "" Then
antw = MsgBox("Achtung der Spieler hat bereits den Wert " & Worksheets(1).Cells(i, inp_pos).Value & _
"!! Soll dieser mit dem neuen Wert " & pl_bew & " überschrieben werden?", vbOKCancel)
If antw = 0 Then
Worksheets(1).Cells(i, inp_pos).Value = pl_bew
Worksheets(1).Cells(i, inp_pos + 1).Value = pl_form_kurz
ElseIf antw = 1 Then
End If
Else
Worksheets(1).Cells(i, inp_pos).Value = pl_bew
Worksheets(1).Cells(i, inp_pos + 1).Value = pl_form_kurz
End If

leer:
Next i

End Sub

Antwort 3 von Oli00

Hallo Karin,

vielen Dank erstmal für die Antwort. Wie kamst du so schnell auf die Lösung? Einfach Erfahrung!?

Leider hat sich wieder eni neues Problem ergeben. Vielleicht weisst du auch hier rat.

Es tritt der Fehler 91 Objekt oder With-Block Variable nicht definiert an der unten gekennzeichneten Stelle auf.

Vielen Dank und Grüße

Oli

Sub spieler_füllen()

no_pl = Worksheets(1).Cells.Find(What:="*", After:=Range("A1"), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

For i = 8 To no_pl


spieler = Worksheets(1).Cells(i, 1).Value
Set c = Worksheets(2).Range("A1:AZ6000").Find(spieler, LookIn:=xlValues)
wo = c.Address
wo_line = c.Row
wo_col = c.Column

pl_bew = Worksheets(2).Cells(wo_line + 3, wo_col).Value
pl_pos = Worksheets(2).Cells(wo_line + 4, wo_col).Value
pl_form = Worksheets(2).Cells(wo_line + 1, wo_col).Value
With WorksheetFunction
pl_form_kurz = Left(Mid(pl_form, .Find("##", .Substitute(pl_form, " ", "##", Len(pl_form) - Len(.Substitute(pl_form, " ", "")) - 2)) + 1, 100), .Find(" ", Mid(pl_form, .Find("##", .Substitute(pl_form, " ", "##", Len(pl_form) - Len(.Substitute(pl_form, " ", "")) - 2)) + 1, 100)) - 1)
End With

If pl_pos = "" Then
GoTo leer
Else
inp_pos = Worksheets(1).Range("C4:M4").Find(pl_pos, LookIn:=xlValues).Column
End If

If Worksheets(1).Cells(i, inp_pos).Value <> "" Then
antw = MsgBox("Achtung der Spieler hat bereits den Wert " & Worksheets(1).Cells(i, inp_pos).Value & _
"!! Soll dieser mit dem neuen Wert " & pl_bew & " überschrieben werden?", vbOKCancel)
If antw = 0 Then
Worksheets(1).Cells(i, inp_pos).Value = pl_bew
Worksheets(1).Cells(i, inp_pos + 1).Value = pl_form_kurz
ElseIf antw = 1 Then
End If
Else
Worksheets(1).Cells(i, inp_pos).Value = pl_bew
Worksheets(1).Cells(i, inp_pos + 1).Value = pl_form_kurz
End If

leer:
Next i

End Sub

Antwort 4 von Oli00

Hups, jetzt ist es zweimal drin. Ich dachte die Antwort wäre verloren gegangen, aber das System war wohl nur zu langsam.

Kann man die Nachricht eigentlich wieder löschen!? Hab keine Funktione gefunden... :/

Antwort 5 von Beverly

Hi Oli,

teils Erfahrung wo man suchen muss und teils Testen dessen, wo man den Fehler vermutet. Beim Durchlaufen im Einzeschrittmodus braucht man nur mit dem Cursor auf die Variablen gehen und man bekommt den Inhalt angezeigt - und der war im gegebenen Fall leer.

Ich weiß leider nicht genau, wie deine Arbeitsmappe aufgebaut ist, aber mir scheint, in deinem jetzigen Code liegt der Fehler im Prinzip bei der selben Ursache - es gibt keine Zelle im Bereich C4:M4, in der sich der gesuchte Wert befindet, weshalb auch keine Spaltennummer ausgelesen werden kann und damit der Laufzeitfehler entsteht.

Bis später,
Karin

Antwort 6 von Oli00

Hi Karin,

stimmt genau. Habe auch gerade noch mal das Problem versucht zu isolieren und bin auf dieses Ergebnis gekommen. Komisch, weil nämlich der gesuchte Wert in C4 war.

Wenn ich bei Range C4:M5 angebe (Die Zellen sind verbunden) findet er den Wert nicht. Wenn ich hingegen die ganze Zeile angebe Range(4:5), dann findet er ihn...

Naja jetzt gehts auf jeden Fall.

Vielen Dank für deine Hilfe!

Grüße

Oli

Antwort 7 von Beverly

Hi Oli,

freut mich, dass du die Lösung gefunden hast.
Nur als Tipp: verbundene Zellen machen einen Heidenärger - man sollt nach Möglichkeit die Finger davon lassen und besser nach anderen Lösungen suchen (auch eine Erfahrungssache ;-)).

Bis später,
Karin

PS: Beiträge lassen sich leider nicht mehr editieren - sind sie abgeschickt, dann ist das unwiderruflich.

Antwort 8 von Oli00

Alles klar, danke für die Tipps!

Grüße

Oli