PDA

Vollständige Version anzeigen : Spalte hochzählen


dickmann_hh
04.05.2009, 11:59
Moin!!

weiß jemand wie ich Spalten hochzählen kann? Die erste Zelle ist kein Problem, nun möchte ich die nächste Zelle in der Spalte (Zeile #6)direkt daneben ansprechen, da komme ich nicht weiter.


Sub parameter()

Dim paramColumn As String
Dim paramName As Range

For Each paramName In Range("C2:Z2")
If IsEmpty(paramName) = False Then 'wenn die Zelle in der Zeile nicht leer dann...
paramColumn = (Left(paramName.Address(False, False), _
Len(paramName.Address(False, False)) - Len(CStr(paramName.Row)))) 'die evtl. zweistellige Spaltenname als String speichern
MsgBox (Range(paramColumn & "6").Value) 'den Inhalt der Zelle von Spalte paramColumn, z.B. Spalte C, Zeile 6 im Message Box anzeigen
MsgBox (Range(paramName.Column + 1 & "6").Value) 'den Inhalt der daneben stehenden Zelle (von Spalte paramColumn+1, z.B. Spalte D Zeile 6) soll angezeigt werden
End If
Next paramName

End Sub


bei der zweiten Msgbox-Anweisung kommt es zu einer Typunverträglichkeit. Ist ja auch irgendwie klar, dass es Probleme geben kann, wenn man String hochzählen will. Aber wie macht man das denn richtig?

zweite Frage: kann mir jemand erzählen, was diese Zeile tut (oben auch drin):

paramColumn = (Left(paramName.Address(False, False), _
Len(paramName.Address(False, False)) - Len(CStr(paramName.Row))))


die habe ich aus einem Buch rauskopiert, weiß aber nicht was es macht. Aber es tut was ich brauche, nämlich die Spalte der aktuellen Zelle als Buchstaben ausgeben und zwar auch mal zweistellig (z.B. AB)

Danke und Gruß,
Dicky

Melanie Breden
04.05.2009, 15:49
Hallo Dicky,

aus welchem Buch hast du denn solch einen Spagetticode?

VBA ist eine Objektbezogene Programmiersprache.
Wenn du einen Range als Objekt verwendest, kannst du dessen Eigenschaften (Zeile, Spalte, Werte etc.) einsetzen und musst nicht
zusätzlich einen Spaltenbuchstaben extrahieren um daraus wieder umständlich einen Range zusammenzusetzen.

Deine Prozedur könnte dann z.B. so aussehen:

Public Sub Testy()
Dim rngCell As Range

For Each rngCell In Range("C2:Z2")
' Zelle in Zeile 2 prüfen
If rngCell.Value <> "" Then
' Wert in Zeile 2
MsgBox rngCell.Value
' Wert aus versetzter Zelle (4 Zeilen nach unten, 1 Spalte nach rechts)
MsgBox rngCell.Offset(RowOffset:=4, ColumnOffset:=1).Value
End If
Next rngCell
End Sub


Zu deiner zweiten Frage:

paramColumn = (Left(paramName.Address(False, False), _
Len(paramName.Address(False, False)) - Len(CStr(paramName.Row))))

Wie gesagt, brauchst du keinen Spaltenbuchstaben, wenn du stattdessen das Range-Objekt genau referenzierst.
Die Syntax dazu ist zudem äußerst umständlich und schneidet die Zeilenzahl vom Spaltenbuchstaben ab.
Einfacher wäre der Spaltenbuchstabe z.B. so zu ermitteln:

paramColumn = Replace(Cells(1, paramName.Column).Address(False, False), 1, "")

Aber wie gesagt ist der Spaltenbuchstabe gar nicht nötig und erschwert die VBA Programmierung nur unnötig.

dickmann_hh
04.05.2009, 18:31
Hallo Melanie,

das sieht doch ganz gut und vor allem übersichtlich aus. Ich werde es morgen im Büro mal ausprobieren. Leider habe ich die Datei nicht mitgenommen :stupid:

Ich melde mich dann! Danke

Gruß,
Dicky

dickmann_hh
05.05.2009, 21:43
Hallo Melanie,

das funktioniert so in etwa wie ich mir das vorstelle. ich hab das ein bissschen angepasst für meine Bedürfnisse und das sieht dann so aus:


Public Sub Testy()
Dim rngCell As Range

For Each rngCell In Range("C2:Z2")
' Zelle in Zeile 2 prüfen
If rngCell.Value <> "" Then
' Wert in Zeile 2
MsgBox rngCell.Value
' Wert aus versetzter Zelle (0 Zeile nach unten, 1 Spalte nach rechts), da nur der Inhalt der daneben stehenden Zelle angezeigt werden soll, wobei beide zellen gleichen Inhalt haben, z.B. 5 und 5, 8 und 8
MsgBox rngCell.Offset(RowOffset:=0, ColumnOffset:=1).Value
End If
Next rngCell
End Sub


so was ich jetzt nicht nachvolziehen kann ist: die 5 wird 3 mal angezeigt, ebenso die 8, obwohl ja eigentlich nur zwei mal msgbox(...) dort stehen.

ääähm moment, mir ist gerade aufgefallen, dass die msgbox-anweisungen keine Klammer haben, wie ich sie sonst kenne. Liegt ds vielleicht daran?

Grüße,
Dicky

chris-kaiser
05.05.2009, 22:25
hi

eigentlich müsstest du (zumindest) noch eine leere Anzeige haben!
schreibe mal noch ein paar Einträge in die zweite Zeile, dann hast du noch mehr Anzeigen.

2 Msgboxen in einer Schleife (mit einer Abfrage) liefern so oft eine Ausgabe wie Werte gefunden werden!

ob die msgbox in einer Klammer steht oder nicht ist in diesem Fall egal!
die Klammern werden gebraucht, wenn ein Rückgabewert erforderlich ist.

z.b wenn abgefragt werden sollte ob nein oder Ja gedrückt worden ist, da würde es ohne Klammer nicht funktionieren.

so z.B
Sub msgboxklammer()
'das würde hier ohne Klammer nicht gehen
If MsgBox("wollen Sie wirklich....", vbYesNo, "just for fun") = vbYes Then
'hier wird die Klammer nicht benötigt
MsgBox " sie haben JA gedrückt"
Else
'die Klammer ist überflüssig, aber angezeigt wird der Text
MsgBox ("Sie haben NEIN gedrückt")
End If
End Sub


p.s aus welchem Buch stammt der erste Code wirklich, das ist ja infernalisch :)

dickmann_hh
06.05.2009, 15:31
heißt: Excel-VBA Codebook von Monika Weber und Bert Körn. Melanie dürfte Monika auch kennen, weil sie ja auch irgendwie so eine ... **Zitat** Microsoft MVP im Bereich Excel und Microsoft User Specialist. Sie ist Office-Trainerin in der Schweiz **Zitat-Ende** Aber da hat jede doch seine Spezialität, die beiden vielleicht in Spagetti-Code. Diese habe ich aber auch meinem Bedarf etwas angepasst, aber der Stil ist gleich geblieben.

Den Code werde ich hoffentlich heute Aben ausprobieren können. Ich melde mich dann wieder. Danke schon mal!

Gruß,
Dicky

NoNet
06.05.2009, 15:55
Hallo Melanie,

weshalb nicht noch einfacher :cool: :

paramColumn = Replace(paramName .Address(0,0),paramName .Row,"")

chris-kaiser
06.05.2009, 21:21
Hallo Dicky

Danke für die Info :) (habe mir mal die <a href="http://bertkoern.de/buch/382731979Xbsp.pdf">Leseprobe</a> angeschaut, werde aber hier keine Bewertung abgeben, da ich den Rest des Buches nicht kenne)

und mein Democode hat mit deiner Anforderung aber nichts zu schaffen, der sollte nur erklären wann eine Klammer bei der msgbox benötigt wird und wann nicht.

das Beste wäre wenn du eine Bsp.datei hochladen würdest und dort kurz beschreibst welches Ziel du hast, falls dein gewünschtes Ergebnis noch nicht erreicht wurde.

das hier habe ich mal vor einiger Zeit geschrieben
(die Frage, wie ermittle ich den Spaltenbuchstaben kommt öfter vor :) )
http://www.excel-inlife.de/index.php?option=com_content&task=view&id=28&Itemid=69


Immer wieder wird in Foren die Frage gestellt
"wie kann ich aus einer Zelladresse den Spaltenbuchstaben ermitteln, damit ich damit weiterarbeiten kann"
Warum eigentlich …….., weil der Makrorekorder einem „vorgaukelt“ das es anders nicht geht. (Zum Thema Makrorekorder siehe auch:
http://www.online-excel.de/excel/singsel_vba.php?f=78)

Wird etwas aufgezeichnet steht ja

Range("A1").Select
ActiveCell.FormulaR1C1 = "xyz
Range("A2").Select

oder

Range("A1:J1").Select
Selection.ClearContents

angenommen ich mochte von der aktiven Zelle 3 Spalten weiter einen Eintrag machen.
Würde es nach dem Makrorekorder gehen, müsste jetzt ermittelt werden welche Spalte gerade die aktive ist und wenn möglich den Buchstaben ermitteln, damit ich herausfinde welchen Buchstaben ich verwenden soll damit 3 Spalten weiter ein Eintrag gemacht werden kann….
NEIIIN!!!
dafür gibt es das Offset

ActiveCell.Offset(0, 3).Value = "xyz"

was macht Offset -> die Werte in der Klammer bedeuten (Zeilenversatz, Spaltenversatz)

ActiveCell.Offset(-1, 4).Value = "xyz
wäre eine Zeile höher und 4 Spalten nach rechts versetzt

Eine relativ unbekannte Möglichkeit ist auch

ActiveCell(1, 4).Value = "xyz"

wäre dasselbe wie

ActiveCell.Offset(0, 3).Value = "xyz"

Nur steht 1 jetzt für die aktuelle Position!!

Nächste Aufgabe:
angenommen ich möchte mit einer Schleife, Werte in A1:C50 eintragen

For Zeile = 1 To 50
For Spalte = "A" To "C"
Range(Spalte & Zeile).Value = "x"
Next
Next
Wird nicht funktionieren!!!!!!

For Zeile = 1 To 50
For Spalte = 1 To 3
Range(Spalte & Zeile).Value = "x"
Next
Next

GEHT auch nicht!!!!!

1,2,3 muss noch in Buchstaben umgewandelt werden, da ja beim Range ein Buchstabe benötigt wird.
NEIIIN!!!

Lösung:
statt Range, Cells verwenden

Cells(Zeile, Spalte)

Range("B5")
wäre
Cells(5,2)
nun kann auch obige Aufgabe gelöst werden
For Zeile = 1 To 50
For Spalte = 1 To 3
Cells(Zeile,Spalte).Value = "x"
Next
Next

Bereiche wie
Range("C2:G100")
können auch so ausgegeben werden
Range(Cells(2,2),Cells(100,7))

Fazit: Spaltenbuchstaben ermitteln ist zu 99,999% überflüssig
ist aber nur meine persönliche Meinung!!!

für ALLE die denoch eine Funktion oder Sub haben wollen

Sub Spaltenbuchstabe()
With ActiveCell 'oder anderer Range
SpBuchst = Replace(.Address(0, 0), .Row, "")
End With
End Sub

'geht auch mit Excel 97
Sub Spaltenbuchstabe()
With ActiveCell 'oder anderer Range
SpBuchst = WorksheetFunction.Substitute(.Address(0, 0), .Row, "")
End With
End Sub

'oder als Funktionen
Function SB(rng As Range) As String
SB = Replace(rng.Address(0, 0), rng.Row, "")
End Function

Function SBStr(col As Integer) As String
SBStr = Replace(Cells(1, col).Address(0, 0), 1, "")
End Function

'oder umgekehrt
Function SBnum(col As String) As Integer
SBnum = Cells(1, col).Column
End Function

dickmann_hh
07.05.2009, 14:36
also gut, dann lade ich mal eine Datei hoch. In der Datei ist auch mein VBA-Code mit drin.

Vielleicht habt ihr ja eine Idee. Ich wäre sehr dankbar

Gruß,
Dicky

chris-kaiser
07.05.2009, 14:50
hi

Set upperLimit = Range(testName.Offset(1, 0), testName.Offset(1, 1))

nicht & ":" &
verwenden :)

das gleiche natürlich für die anderen Bereiche
p.s das war im vorigen Post der Hinweis hier

Bereiche wie
Range("C2:G100")
können auch so ausgegeben werden
Range(Cells(2,2),Cells(100,7))

dickmann_hh
10.05.2009, 19:15
hallo chris,

das funktioniert, danke! wie macht man denn

Range("A3:A1000,B3:B1000")?

wobei die Bereiche als Variable stehen sollen, da diese ja flexibel sein sollen. Diesen Ausdruck brauche ich, weil (für die erste Testreihe) in der Spalte A die Nummern und in B die Messwerte stehen. Für die zweite stehen die Messwerte in Spalte C, d.h. es müsste sein, Range ("A3:A1000, C3:C1000").

Mein Code wäre (nachdem das Diagram erzeugt wurde):

ActiveChart.SetSourceData Source:=Sheets("Messwerte").Range(nummerBereich & "," & werteBereich), _
PlotBy:=xlColumns


Aber dann macht es "Typ unverträglich". Aber wenn ich stattdessen mache:

ActiveChart.SetSourceData Source:=Sheets("Messwerte").Range(nummerBereich , werteBereich), _
PlotBy:=xlColumns

dann macht er bei dem zweiten Diagram (da bei jeder Testreihe ein neues Diagram eingefügt werden soll) zwei Datenreihen:
- mit Spalte A für die X-Achse und Spalte B für die Y-Achse (identisch wie beim ersten Diagram, also fehl am platz)
- mit Spalte A für die X-Achse und Spalte C für die Y-Achse.

Ich bin ein bissel überfordert. Könnte ihr Experten hier mal helfen?

Gruß,
Dicky

IngGi
11.05.2009, 09:32
Hallo Dicky,

das geht zum Beispiel so:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Dim</span> werteBereich <span class="TOKEN">As</span> Range
<span class="TOKEN">Dim</span> nummerBereich <span class="TOKEN">As</span> Range
&nbsp;
<span class="TOKEN">Set</span> werteBereich = Range(&quot;C3:C1000&quot;)
<span class="TOKEN">Set</span> nummerBereich = Range(&quot;A3:A1000&quot;)
&nbsp;
<span class="TOKEN">With</span> ActiveChart
.SetSourceData werteBereich
.SeriesCollection(1).XValues = nummerBereich
<span class="TOKEN">End</span> <span class="TOKEN">With</span>
&nbsp;</pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

Gruß Ingolf

dickmann_hh
11.05.2009, 19:12
das kann doch gar nicht wahr sein!!

was hat Excel fürn Problem?! wenn ich den Code kompiliere:

Set werteBereich = Range("C3:C1000")
Set nummerBereich = Range("A3:A1000")

dann bricht es ab mit der Fehlermeldung Laufzeitfehler 1004: die Methode 'Range' für das Objekt '_Global' ist fehlgeschlagen.

Das komische ist, manchmal bricht es in der ersten Zeile ab, manchmal zweite. Was istn da los? Leigt es vielleicht daran, dass ich mit Excel 2002 arbeite? Ist solch ein Ausdruck dort unbekannt? Glaube ich zwar nicht, aber wer weiß? Ich habe das Gefühl, dass Excel 2002 Probleme damit hat, mit der Anweisung umzugehen.

Gruß,
Dicky

IngGi
11.05.2009, 20:31
Hallo Dicky,

referenziere die beiden Zellbereiche mal ausführlich über den Tabellenblattnamen:

Set werteBereich = Worksheets("Messwerte").Range("C3:C1000")
Set nummerBereich = Worksheets("Messwerte").Range("A3:A1000")

Ohne diese ausführliche Referenzierung geht das zum Beispiel dann schief, wenn gerade nicht das Tabellenblatt, sondern das Diagramm aktiv ist (und der Code nicht im Modul des Tabellenblattes steht).

Gruß Ingolf

dickmann_hh
13.05.2009, 06:12
Hallo Ingolf,

vielen Dank!! das klappt super! Jetzt gehts weiter und habe neue Fragen. Mache ich neue Threads auf :-)

Gruß,
Dicky