Microsoft Excel

Herbers Excel/VBA-Archiv

variabler Spaltenbezug und Zellenbezug

Betrifft: variabler Spaltenbezug und Zellenbezug von: R.
Geschrieben am: 25.08.2014 12:06:45

Hallo,

ich habe ein Problem. Ich ermittle mit zwei verschiedene Formeln die Spalte und Zeile als Zahl und möchte in dieser spezifischen Zelle ein Ergebnis ausweisen, leider funktioniert das ganze nicht. Fehlermeldung "typen unverträglich". Ich habe eine kleine Beispieldatei angefügt.

https://www.herber.de/bbs/user/92274.xlsm

Vielen Dank für Eure Hilfe im voraus.

Gruß R.

  

Betrifft: AW: variabler Spaltenbezug und Zellenbezug von: Hajo_Zi
Geschrieben am: 25.08.2014 12:10:56

du schreibst auf die Variablen Text umfangreich.

GrußformelHomepage


  

Betrifft: AW: variabler Spaltenbezug und Zellenbezug von: {Boris}
Geschrieben am: 25.08.2014 12:16:23

Hi,

da liegt so einiges im Argen.

Sub Makro1()
Dim var1 As Variant 'Spaltenbezug
Dim var2 As Variant 'Zeilenbezug
var1 = Evaluate("=SUMPRODUCT((A1:H1=DATE(M16,M15,1))*COLUMN(A:H))")
var2 = Evaluate("=COUNTA(a:a)")
Sheets("Tabelle1").Cells(30, 15).Value = var1
Sheets("Tabelle1").Cells(32, 15).Value = var2
Sheets("Tabelle1").Cells(var2, var1).Value = "Ergebnis"
End Sub
VG, Boris


  

Betrifft: AW: variabler Spaltenbezug und Zellenbezug von: R.
Geschrieben am: 25.08.2014 12:44:50

Danke Boris :) :)

Aber warum klappt die erste var1 Formel nicht wenn ich statt der A1:H1 diese R1C1:R2C8 verwende?

Gruß R.


  

Betrifft: AW: variabler Spaltenbezug und Zellenbezug von: {Boris}
Geschrieben am: 25.08.2014 12:48:48

Hi,

Du kannst nicht x-beliebig innerhalb einer Excelformel zwischen A1- und Z1S1-Schreibweise switchen.

VG, Boris


  

Betrifft: Ergänzung von: Erich G.
Geschrieben am: 25.08.2014 13:28:48

Hi R. (?),
warum deklarierst du var1 und var2 als Variant? In deinem Code werden den Variablen Strings zugewiesen:
var1 = "=SUMPRODUCT((R1C1:R2C8=DATE(R16C13,R15C13,1))*COLUMN(C1:C8))"
var2 = "=COUNT(a:a)"
Ebenso könnte da stehen:
var1 = "simsalabim"
Klarer wäre gewesen, du hättest "Dim var1 As String, var2 As String" deklariert.

Der Text wird in eine Zelle geschrieben, dort von Excel als Formel interpretiert und ausgewertet.
Die String-Inhalte der Variablen var1 und var2 ändern sich dadurch nicht - das sind nach wie vor Texte.
Und so etwas wie Cells(var2, var1) ist damit einfach nicht definiert.

In Boris' Code steht "var1 = Evaluate(...)", der Variablen var1 wird also der Wert der Formel in Klammern zugewiesen.
Das ist ein Long-, jedenfalls ein Zahlenwert.
"Dim var1 As Long, var2 As Long" würde hier gut passen!
Mit
Sheets("Tabelle1").Cells(30, 15).Value = var1
wird kein Text in die Zelle geschrieben, auch keine Formel, sondern nur einfach eine Zahl.
Und "Cells(var2, var1)" klappt auch problemlos.

Dass sich var1 mit der R1C1-Schreibweise nicht so berechnen lässt, liegt am Evaluate.
In der VBA-Hilfe zur Application-Methode Evaluate kannst du lesen, dass diese Methode die A1-Schreibweise braucht.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Ergänzung von: R.
Geschrieben am: 25.08.2014 15:54:00

Hallo Erich danke für deinen Hinweis und Erklärung.

http://msdn.microsoft.com/de-de/library/office/ff193019(v=office.15).aspx

hier steht das es Variant sein sollte.

ansonsten geht die formel mit der R1C1-Schreibweise, aber nur wenn ich den letzten Spaltenteil A:H durch C1:C8 ersetzte der rest muss A1 schreibweise sein.


  

Betrifft: AW: Ergänzung von: Erich G.
Geschrieben am: 25.08.2014 18:56:18

Hi (?),
"hier steht das es Variant sein sollte"? Nein - da steht nur, welchen Datentyp der Wert hat, den Evaluate zurück gibt.
Das kann nur Variant sein - die Formel innerhalb der Evaluate-Klammer kann ja alle möglichen Werte haben,
Zahlen, Strings, Fehlerwerte, ...

Das bedeutet aber nicht, dass man ein Evaluate-Ergebnis nur als Variant verwenden könnte.
Weiß man, dass das Ergebnis ein Long ist, kann man den Wert natürlich einer Long-Variablen zuweisen,
dabei in Long konvertieren.

"wenn ich den letzten Spaltenteil A:H durch C1:C8 ersetzte" habe ich nicht verstanden. :-(
Wie sieht diese Formel jetzt bei dir aus (in Excel und in VBA)?

SPALTEN(C1:C8) wäre jeweils 3 - die Nummer der Spalte C.
Auch C1:C8 ist A1-Schreibweise, die Spalte C darf in der A1-Schreibweise ja vorkommen.
SPALTEN(A:H) oder SPALTEN($A:$H) ergibt als Array die Zahlen 1 bis 8 - wie gewünscht.

"der rest muss A1 schreibweise sein"? In Evaluate müssen alle Bezüge in A1-Schreibweise angegeben werden.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: Nee, Erich, da irrst du dich! Ich habe das ... von: Luc:-?
Geschrieben am: 25.08.2014 19:04:07

…deshalb auch nachgelesen und tatsächlich steht da wirklich …
Name Erforderlich Variant
…warum auch immer…
Das mit dem Ergebnis steht da extra.
Gruß, Luc :-?


  

Betrifft: @Luc: Widerspruch von: Erich G.
Geschrieben am: 25.08.2014 19:32:53

Hi Luc,
in Boris' Code - und nur darin kommt Evaluate vor - ist var1 die Variable, in die das Evaluate-Ergebnis geschrieben wird.
Der Variant-Rückgabewert wird also (dann bei mir) in eine Long-Variable konvertiert und gespeichert.

"Name Erforderlich Variant" steht bei "Parameter" und hat mit der Variablen var1 rein gar nichts zu tun.
Der Parameter ist bei Boris ein String - oder auch ein Variant vom Subtyp String.

Ich bleibe bei meiner Erklärung.

Anderes Beispiel:
Wenn ich weiß, dass eine Long-Zahl für ein Datum steht, kann ich doch den Long-Wert auch in eine Variable
vom Typ Date schreiben, oder? Oder gilt: Einmal long, immer long? :-)

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: Ich bezog mich nur auf den msdn.Link, ... von: Luc:-?
Geschrieben am: 25.08.2014 20:30:33

…Erich,
nicht auf die gezeigten Pgmm! Deshalb habe ich auch den Nachsatz …warum auch immer… geschrieben, denn das ist eigentlich nicht so recht einsichtig.
Übrigens wirft das vor diesem Hintergrund auf meine Diskussion (vor Monaten) mit dem VBA-Tutorial-Anbieter mit Access-Hintergrund doch ein eindeutiges (MS-)Licht. Inzwischen hat der aber eingesehen, dass Variant nicht per sé (schon gar nicht in xlVBA!) böse ist… ;-]
Noch was; einmal long, immer long gilt auf jeden Fall für Arrays und Variablen, solange es sich um dieselbe(n) handelt. Mit CDate kannst du einen Wert in eine andere Variable übertragen, die von diesem Datentyp (wie du es ja dargestellt hast, allerdings ohne CDate, was VBA dann automatisch macht) oder Variant ist. Ist eine Variable vom Typ Variant, kann man ihren Wert manipulieren wie man will, sie passt sich dem an. Ein Bsp; alle Werte eines leeren Variant-Arrays haben den Wert Empty. Wdn in diesem Array nur Texte gespeichert, wdn leere Werte als LeerString interpretiert. Sind es dagegen nur (echte) Zahlen, wdn die leeren Werte als 0 gedeutet. Den Pseudowert Null muss man aber extra zuweisen und der wird mE auch nicht uminterpretiert.
Luc :-?