Formelzuordnung an eine Variable als allgemeines VBA Lösungstool

[sotaros]

Hallo Leute,

immer wieder tauchen interessante Formeln zur Lösung von in VBA gestellten Problemen in diesem Forum auf. Mich würde dabei die VBA Einbindung derartiger Formeln interessieren.

Nehmen wir die letzte Formel aus dem Thread "Mehrstellige Zahl vor dem Artikel" gepostet von Wilfried Höttl als Beispiel. Die VBA Einbindung für die Zelle B1 würde demnach lauten :

Code:

Range("B1").FormulaR1C1 = "LÄNGE(VERWEIS(9^9;--LINKS(9&A1;SPALTE(1:1))))-1"

Aber wie würde man die gesamte Formeln zwecks Weiterverarbeitung einer Variablen zuordnen?

Dies würde für sehr viele Leute die ergebnisorientierte Arbeit mit Excel sehr vereinfachen.

[Steffl]

Hallo,

Nehmen wir die letzte Formel aus dem Thread "Mehrstellige Zahl vor dem Artikel" gepostet von Wilfried Höttl als Beispiel. Die VBA Einbindung für die Zelle B1 würde demnach lauten :

Code:

Range("B1").FormulaR1C1 = "LÄNGE(VERWEIS(9^9;--LINKS(9&A1;SPALTE(1:1))))-1"

Da gehört noch ein Local dazu. VBA spricht englisch und mit einer deutschen Formelbezeichnung dürfte es nicht gehen. Mit der Weiterverarbeitung einer Variablen: Meinst Du vielleicht so?

Code:

Sub prcFormel()
   Dim lngC As Long
  
   For lngC = 1 To 10
      Range("B" & lngC).FormulaR1C1Local = "LÄNGE(VERWEIS(9^9;--LINKS(9&A" & lngC & ";SPALTE(1:1))))-1"
   Next lngC
End Sub

[BoskoBiati]

Hallo,

das R1C1 kann entfallen und es fehlt ein Gleichheitzeichen in der Formel:

Code:

Range("B" & lngC).FormulaLocal = "=LÄNGE(VERWEIS(9^9;--LINKS(9&A" & lngC & ";SPALTE(1:1))))-1"

[sotaros]

Hallo Leute,

danke für eure Antworten. Worauf ich hinaus will ist folgendes:

Code:

Sub Makro2()
Dim a As Variant
Range("B1").Select
ActiveCell.FormulaR1C1 = "=LEN(LOOKUP(9^9,--LEFT(9&RC[-1],COLUMN(R))))-1"
a = Range("B1").Value
Range("B1").Value = ""

Debug.Print a
End Sub

Wie ihr seht habe ich über den Umweg der Formelzuweisung (das mit dem "localFormula" könnt ihr mir bitte nochmal gesondert erklären) auf Zelle B1 für die Variable a das gewünschte Ergebnis bekommen und dann die Formel in Zelle B1 wieder gelöscht. Mein Anliegen ist wie mache ich ohne diesen Umweg eine solche Zuweisung. So etwas geht doch schöner, oder?!

Schön wäre es wenn es z.B . so gehen würde
a.FormulaR1C1 = "=LEN(LOOKUP(9^9,--LEFT(9&RC[-1],COLUMN(R))))-1"
und dann vielleicht
mit
a.value in VBA irgendwie weiter arbeiten könnte.

[Steffl]

Hallo,

zu der Formula(R1C1)Local-Eigenschaft: Mal aus der OH

Range.FormulaR1C1Local-Eigenschaft
Gibt die Formel für das Objekt in Z1S1-Schreibweise in der Sprache des Benutzers zurück oder legt diese fest.Variant-Wert mit Lese-/Schreibzugriff.

Das entscheidende habe ich fett markiert. In deinen Startbeitrag hast Du eine Formel in deutscher Schreibweise gepostet und da brauchst Du dann das Local.
Deinen Wunsch habe ich nicht ganz verstanden aber suche mal nach Evaluate.

@Edgar:

Danke für die Berichtigung :17:

[sotaros]

Eigentlich suche ich genau so etwas:

Code:

Sub Zuordnung()
Dim vx As Range

'Formelzuweisung:
vx.FormulaLocal = "=LEN(LOOKUP(9^9,--LEFT(9&RC[-1],COLUMN(R))))-1"
'Ergebnis: (Weiterverarbeitung möglich)
Debug.Print vx.Value
End Sub

(Funktioniert natürlich leider nicht)
Was ich nicht möchte ist das ich irgendwie über eine bestimmte Zelle in meinem Excel-Tabellenblatt
gehen muss.

[Steffl]

Hallo,

das fehlt das Set vor dem vx.Formula..

Code:

Sub Zuordnung()
Dim vx As Range

'Formelzuweisung:
Set vx.Formula = "=LEN(LOOKUP(9^9,--LEFT(9&RC[-1],COLUMN(R))))-1"   'da du die englische Schreibweise verwendest, geht es ohne local
'Ergebnis: (Weiterverarbeitung möglich)
Debug.Print vx.Value
End Sub

[Castor]

Hallo, Stefan;
das ist nicht richtig! Nicht das Set fehlt, sondern die Zuweisung eines Objekts an die Objekt­variable vx! Die Zuweisung des Formeltextes bzw einer diesen enthaltenden Variablen an die Formula-Eigenschaft des Range-Objekts kann ohne Set erfolgen. Außerdem muss die Zuweisung eines R1C1(/Z1S1)-Formel­textes an .FormulaR1C1(Local) erfolgen, damit nicht intern umgerechnet werden muss, was entweder nur zu Zeit­verzö­gerungen oder schlimmsten­falls zu Fehler­meldungen führt.
Gruß, Castor

[Steffl]

Hallo Castor,

Du hast Recht! Ich hatte die Formel von sotaros nicht angeschaut und nur kopiert. Erst jetzt habe ich mir die Formel angeschaut und eventuell ein weiteres Problem entdeckt und zwar bei COLUMN(R) Wenn der Variablen R keine Zahl zugewiesen ist (was man ja aus dem geposteten Code nicht ersehen kann) gibt es einen Fehler.

[sotaros]

Hallo Leute,
ja der Formelfehler hat sich irgendwie eingeschlichen. Nehmen wir wieder die Formel
vom Anfang dieses Threads:

LÄNGE(VERWEIS(9^9;--LINKS(9&A1;SPALTE(1:1))))-1

der Bezug zu einer konreten Zelle und Spalte muss selbstverständlich gegeben sein.

Bleibt noch der Code.