dynamischer Zellbezug Formular Local

Forumthread: dynamischer Zellbezug Formular Local

27.02.2017 17:43:10

Kliewe

Hallo zusammen,
ich habe folgendes Problem: ich weise über Formular local per VBA Excel eine Formel zu, das klappt soweit.
Jetzt ist es jedoch so, das sich der Bereich der Zellen in der Formel ändert. Gestartet wird immer bei C, das Ende jedoch kann bis Spalte EP variabel sein.
Das heißt: Ich brauche eine Funktion, die immer die letzte Spalte ausliest. Das habe ich schon, das Funktioniert.
Jetzt muss aber noch die Zelte Spalte, es ist ja eine Nummer, wieder in einen Buchstaben umgewandelt und dann noch bei Formular Local eingebaut werden
Jemand eine Idee wie das gehen könnte?!

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: dynamischer Zellbezug Formular Local

27.02.2017 18:07:22

firmus
AW: dynamischer Zellbezug Formular Local

28.02.2017 10:16:32

Kliewe
FormulaLocal = String

28.02.2017 12:46:26

Michael (der Alte)
AW: FormulaLocal = String

02.03.2017 09:52:04

Kliewe
AW: FormulaLocal = String

02.03.2017 13:02:05

Michael (der Alte)
AW: FormulaLocal = String

02.03.2017 16:06:38

Kliewe
gerne, viel Erfolg & Gruß owT

04.03.2017 12:12:54

Michael (der Alte)
Fehler gesehen

04.03.2017 19:38:56

Michael (der Alte)
AW: dynamischer Zellbezug Formular Local

28.02.2017 15:14:30

firmus

AW: dynamischer Zellbezug Formular Local

27.02.2017 18:07:22

firmus

Hi Kliewe,
wenn Du auf die letzte Spalte positioniert hast, dann kannst Du ActiveCell.Address auslesen.
In ActiveCell.Address steht die Adresse in der Form "$xx$nn"
Mit Split kannst Du das xx ausfiltern. Im zweiten Element [xoff(1)] stehen dann die Buchstaben.
Dim Xoff
Dim XoffC as String
Xoff = Split(ActiveCell.Address, "$")
XoffC = XoffACCT(1) 'column in CHAR, 2. Eintrag aus ARRAY nehmen
Gruß
Firmus

AW: dynamischer Zellbezug Formular Local

28.02.2017 10:16:32

Kliewe

Hallo Firmus,
soweit verstanden, das ist mir klar. Aber: Wie bekomm ich diese Variable in FormularLocal unter?!
Alles was nach FormularLocal in den " " steht wird doch einfach in Excel geschrieben, ohne das die Angabe von Variablen möglich ist, oder?!

FormulaLocal = String

28.02.2017 12:46:26

Michael (der Alte)

Hi,
...und einen String kannst Du mit & beliebig zusammensetzen.
Gerade bei umfangreicheren Formeln bietet es sich an, die Funktionalität direkt im Tabellenblatt zu entwickeln, die Formel dann als Text einzulesen, und zwar im Direktfenster (Aufruf in VBA mit Strg+G):

?range("D2").FormulaLocal
=SUMME(B2:B11)

Die Formel erscheint, sobald die vorhergehende Zeile mit dem ? mit Enter abgeschlossen wurde.
Von hier kann man sie direkt in den Code übernehmen und den *variablen* Teil anpassen, z.B. so:

Sub formel()
Dim formeltext As String
Dim bis As String
bis = "B11"
formeltext = "=SUMME(B2:" & bis & ")"
Range("D2").FormulaLocal = formeltext
End Sub

Das ist die Variante mit *einer* Formel über einen variablen Bereich.
Die andere Variante schreibt eine Reihe "gleicher" Formel (mit zeilenweise geändertem Bezug) in einen vertikalen (Spalten-) Bereich, also etwa so:

Sub formelBereich()
Dim formeltext As String
Dim anzahl As Long
Dim Sp1 As String, Sp2 As String
Sp1 = "B": Sp2 = "D"
' Addition von B2 und D2
' nächste Zeile B3 und D3 usw.
anzahl = 10  ' Anzahl der Zeilen, wie auch immer ermittelt
formeltext = "=" & Sp1 & "2+" & Sp2 & "2"
Range("E2").Resize(anzahl, 1) = formeltext
Debug.Print formeltext
' Ausgabe im Direktfenster: =B2+D2 wie gewünscht
End Sub

Schne Grüße,
Michael

AW: FormulaLocal = String

02.03.2017 09:52:04

Kliewe

Hallo zusammen,
also ich finde die Idee von Michael mit dem String super! Also ich bin jetzt kein VBA Profie...und diese Variante erscheint mir als relativ simpel, so dass auch ich sie "nachprogrammieren" kann.
Ich habe jetzt mal die Formel, die in die Zelle muss (noch ohne variablen Teil) versucht so wie von Michael vorgeschlagen abzubilden. Bekomme jedoch den Fehler 1004: Anwendungs oder Objektbezogener Fehler, markiert wird die Zeile mit FormlarLocal
// =Dim formeltext As String
// formeltext = "=WENN(ISTZAHL($B$2);VERWEIS(1;1/('Calculations'!$C$115:'Calculations'!$AP$115<0);SPALTE('Calculations'!$C$115:'Calculations'!$AP$115)-1);"")"
// =Worksheets("Analysis").Range("B5").FormulaLocal = formeltext
Woran könnte das liegen?

AW: FormulaLocal = String

02.03.2017 13:02:05

Michael (der Alte)

Hi,
mangels Blättern mit Daten kann ich die Formel nicht testen, aber ich denke, das liegt daran, daß die Formel am Ende *nicht* "" enthält, sondern nur *ein* ".
Teste das mal aus mit geöffnetem "Direktfenster" (in VB mit Strg+g einzuschalten):

Sub test()
Dim sFormel As String
Dim vS1 As String, vS2 As String, tBl As String ' variabler String 1 + 2, Tabellenblatt
' 1. Deine Formel wie vorhanden:
sFormel = "=WENN(ISTZAHL($B$2);VERWEIS(1;1/"
sFormel = sFormel & "('Calculations'!$C$115:'Calculations'!$AP$115<0)"
sFormel = sFormel & ";SPALTE('Calculations'!$C$115:'Calculations'!$AP$115)-1);"")"
Debug.Print sFormel
' Ausgabe:
' =WENN(ISTZAHL($B$2);VERWEIS(1;1/('Calculations'!$C$115:'Calculations'!$AP$115<0);
' SPALTE('Calculations'!$C$115:'Calculations'!$AP$115)-1);")
' 2. zur Ansicht für die doppelten "":
vS1 = "hier will ich zwei Anführungszeichen+Klammer zu: """")"
Debug.Print vS1
' Ausgabe: hier will ich zwei Anführungszeichen+Klammer zu: "")
' Zuweisung variable Teile
vS2 = "$B$2"
tBl = "Calculations"
' Einbau in formeltext
sFormel = "=WENN(ISTZAHL(" & vS2 & ");VERWEIS(1;1/(" _
& tBl & "!$C$115:!$AP$115<0);SPALTE(" _
& tBl & "!$C$115:$AP$115)-1);"""")"
Debug.Print sFormel
' Ausgabe:
' =WENN(ISTZAHL($B$2);VERWEIS(1;1/(Calculations!$C$115:!$AP$115<0);
' SPALTE(Calculations!$C$115:$AP$115)-1);"")
End Sub

Ich denke, die "'" sind überflüssig, außerdem genügt bei einer Bereichsangabe *einmal* das Blatt.
Siehe z.B. =SUMME(Tabelle1!C2:C6) händisch/mit Maus eingegeben.
Schöne Grüße,
Michael

AW: FormulaLocal = String

02.03.2017 16:06:38

Kliewe

Hallo Michael,
vielen Dank für die schnelle und ausführliche Antwort :-)
Leider schaffe ich es erst am Montag den 13März den Vorschlag in mein Tool einzubauen.
Sobald ich es aber ausprobiert habe, melde ich mich!
Nochmal vielen Dank und bis dahin
Marcel

gerne, viel Erfolg & Gruß owT

04.03.2017 12:12:54

Michael (der Alte)

Fehler gesehen

04.03.2017 19:38:56

Michael (der Alte)

Hi,
in der mittleren Zeile von

sFormel = "=WENN(ISTZAHL(" & vS2 & ");VERWEIS(1;1/(" _
& tBl & "!$C$115:!$AP$115<0);SPALTE(" _
& tBl & "!$C$115:$AP$115)-1);"""")"

ist das "!" zuviel in der Angabe $C$115:!$AP$115 (nach dem :)
Gruß,
M.

AW: dynamischer Zellbezug Formular Local

28.02.2017 15:14:30

firmus

Hi Kliewe,
hier ein Beispiel: VLOOKUP = SVERWEIS in deutsch
'====================================================================================================
' VLOOKUP in Zeile 2 mit Variable durchkopieren
'====================================================================================================
tmpN1 = XStop2C - xStop1C + 2 'counter 2-23 wg. delta + both STOP-Marker
For tmpN2 = 2 To tmpN1
tmpN3 = xCaseNrC + tmpN2 - 1
tmpC1 = "=VLOOKUP($" & xCaseNrCa & "2" & ",'[" & wbkDA.Name & "]" & wshDA.Name & "'!$" & xCaseNrCa & ":$" & XStop2Ca & "," & tmpN2 & ",FALSE)"
wshSF.Cells(2, tmpN3).Formula = tmpC1
Next tmpN2
Gruß
Firmus

Beliebteste Forumthreads (12 Monate)

dynamischer Zellbezug Formular Local

Forumthread: dynamischer Zellbezug Formular Local

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread