Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Formelzuordnung an eine Variable als allgemeines VBA Lösungstool
#1
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.
to top
#2
Hallo,

(28.10.2014, 11:40)sotaros schrieb: 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
Gruß Stefan
Win 7 / Office 2007
to top
#3
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"
Gruß


Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
to top
#4
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®)))-1"
und dann vielleicht
mit
a.value in VBA irgendwie weiter arbeiten könnte.
to top
#5
Hallo,

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

Zitat: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
Gruß Stefan
Win 7 / Office 2007
to top
#6
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.
to top
#7
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
Gruß Stefan
Win 7 / Office 2007
to top
#8
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
to top
#9
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® Wenn der Variablen R keine Zahl zugewiesen ist (was man ja aus dem geposteten Code nicht ersehen kann) gibt es einen Fehler.
Gruß Stefan
Win 7 / Office 2007
to top
#10
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.
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  VBA Solver mit zusätzlicher Variable und Zielwert eränzen tschibolli 2 77 22.11.2016, 08:11
Letzter Beitrag: tschibolli
  Variable in Makro VaterBaum 12 165 12.11.2016, 20:34
Letzter Beitrag: AlterDresdner
Lightbulb VBA Zeile in eine Arbeitsmappe kopieren wenn in der Zeile eine 1 steht Olossos 8 133 11.11.2016, 17:08
Letzter Beitrag: Kuwer
  Inhalt Multiselect-ListBox in Variable auslesen Rabe 3 82 28.10.2016, 12:04
Letzter Beitrag: Rabe
  Variable Nachtzeiten und Zuschläge Chimera 2 111 28.10.2016, 00:28
Letzter Beitrag: Chimera
  Zellenformeln verkürzen durch Namen definieren (variable) Woofer 2 99 27.10.2016, 15:46
Letzter Beitrag: BoskoBiati
Question Variable Höhe /Breite eines Excel VBA-UserForms GuteFrage 2 157 25.09.2016, 11:04
Letzter Beitrag: schauan
  Eine Datei schließen, eine andere Öffnen GustavoWoltmann 1 127 14.09.2016, 14:20
Letzter Beitrag: schauan
  Formelsuche: "Textwert" und variable Zahl frylich 22 735 02.09.2016, 13:45
Letzter Beitrag: Peter
  Variable wird nicht übergeben IchBinIch 4 199 08.08.2016, 18:47
Letzter Beitrag: IchBinIch

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste