PDA

Vollständige Version anzeigen : Bezug von Zeilen/Zellen


SeNf
30.04.2009, 06:56
Moin,
damit ich beim einfügen von neuen spalten nicht immer den ganzen code ändern muß
wollte ich allen relevanten spalten bereichsnamen gegeben, jetzt hänge ich nur noch dort wo ich mit "cell" rechne, wie löse ich das denn?
und
wenn ich zb. für spalte "V" den namen "FREI" vergeben habe, wie muß ich folgenden code ändern? Range("V7:V" & fFree)
Range("FREI7:FREI" & fFree) funktioniert nicht... :-(

auch hier spalte "V" als "FREI"
Dim fFree As Long
Dim rngDatFind As Range
Dim lngCounter As Long
Dim Zell As Range

'sucht die lezte freie zeile in spalte A
fFree = Rows.Count
If Range("FREI")(fFree).Value = "" Then
fFree = Range("FREI")(fFree).End(xlUp).Row
End If

'zellen farblos und ohne text setzen

With Range("V7:V" & fFree)
.Value = ""
.Interior.ColorIndex = xlNone
End With

Set rngDatFind = Columns(33).Find("1", LookIn:=xlValues)
For lngCounter = 7 To fFree
If Cells(lngCounter, 33).Value = 1 Then
With Cells(lngCounter, "V")
.Interior.ColorIndex = 37
.Value = "TEST"
End With
End If
Next lngCounter

dank im vorraus
und gruß
aus dem süden

Melanie Breden
30.04.2009, 09:29
Hallo,

versuchs mal so:
Dim fFree As Long
Dim rngDatFind As Range
Dim lngCounter As Long
Dim Zell As Range

'sucht die lezte freie zeile in spalte A
fFree = Rows.Count
If Range("FREI").Cells(fFree).Value = "" Then
fFree = Range("FREI").Cells(fFree).End(xlUp).Row
End If

'zellen farblos und ohne text setzen

With Range(Cells(7, Range("FREI").Column), Cells(fFree, Range("FREI").Column))
.Value = ""
.Interior.ColorIndex = xlNone
End With

Set rngDatFind = Columns(33).Find("1", LookIn:=xlValues)
For lngCounter = 7 To fFree
If Cells(lngCounter, 33).Value = 1 Then
With Cells(lngCounter, Range("FREI").Column)
.Interior.ColorIndex = 37
.Value = "TEST"
End With
End If
Next lngCounter

Gruß
Melanie

SeNf
30.04.2009, 11:29
super funktioniert,
nur noch ein "problem" wie löse ich das bei Columns(33) und darunter in der zeile mit if cells(...; 33)
Gruß
Steffen

Melanie Breden
30.04.2009, 12:22
Hallo Steffen,

hast du denn auch für die Spalte 33 eine Variable, oder einen Namen definiert?

Gruß
Melanie

SeNf
30.04.2009, 12:57
Hallo Melanie,
jep hab ich: "SP33" :-)
gruß
Steffen

Melanie Breden
30.04.2009, 13:20
Hallo Steffen,

genauso wie bei dem anderen definierten Namen:

' rngDatFind wird im weiteren Code nicht verwendet!
' Set rngDatFind = Range("SP33").Find("1", LookIn:=xlValues)
For lngCounter = 7 To fFree
If Cells(lngCounter, Range("SP33").Column).Value = 1 Then
With Cells(lngCounter, Range("FREI").Column)
.Interior.ColorIndex = 37
.Value = "TEST"
End With
End If
Next lngCounter


Alternativ geht auch:

If Range("SP33").Cells(lngCounter).Value = 1 Then

SeNf
30.04.2009, 14:23
hat super funktioniert! danke
und deswegen gleich noch eine frage :-)
zb
Columns("AM:CM").ColumnWidth = 4
wenn ich den bereich AM bis CM definiert habe wie spreche ich den dann an?
Gruß
Steffen

Melanie Breden
30.04.2009, 15:00
Hallo Steffen,

wenn der Bereichsname ganze Spalten enthält, kannst du ihn einfach
als Range angeben:

Range("Bereichsname").ColumnWidth = 4

SeNf
30.04.2009, 22:03
funkt auch! danke
und weils so schön ist noch was :-)

Combobox1.RowSource = "Tabelle2! A3:A100"
da würde ich auch noch gerne einen bereich definieren und am besten noch die befüllung der spalte einschränken
begrenzen würde ich wieder so:

Dim Free As Long
Free = Rows.Count
If Range("SpalteA")(Free).Value = "" Then
Free = Range("SpalteA")(Free).End(xlUp).Row
End If

und wie weiter?
Gruß
Steffen

Melanie Breden
01.05.2009, 07:37
Hallo Steffen,

wenn ich dich richtig verstanden habe, willst du den benutzten Bereich von
A3:A100 im Auswahlfeld einer ComboBox anzeigen?

Hierfür kannst du anstelle eines Namens mit festem Bezug einen dynamischen Namen definieren.
Ein dynamischer Name bezieht sich dann automatisch nur auf die Zellen, in denen Werte vorhanden sind.
Voraussetzung für den folgenden Bereichsnamen ist, dass die Zellen fortlaufend gefüllt sind.

Definiere im Namensmanager über das Menü Einfügen|Namen|Definieren folgenden Namen:

Name: ComboData
Bezug: =BEREICH.VERSCHIEBEN(Tabelle1!$A$3;;;ANZAHL2(Tabelle1!$A$3:$A$100))

Den Namen kannst du direkt im Eigenschaftenfenster der ComboBox neben die RowSource-Eigenschaft schreiben.

Um den Bereichsnamen per Code an die ComboBox zuzuweisen verwende folgende Syntax:

ComboBox1.RowSource = Range("ComboData").Address(External:=True)

SeNf
02.05.2009, 18:28
Danke! so langsam kommt licht ins dunkel, wobei ich feststellen muss wie wenig ich wirklich weiß...
jetzt habe ich noch doppelte einträge in der liste kann ich die noch in der formel vom BEZUG rausfiltern?
Gruß
Steffen

SeNf
02.05.2009, 20:15
Columns("I:I").sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("SPALTE_A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("ComboDATA"), Unique:=True

das wäre meine lösung das ganze auch noch zu sortieren aber jetzt habe ich ja wieder das problem das die bereiche I:I und I2 wieder buchstaben bezogen sind...
Gruß
Steffen

Melanie Breden
03.05.2009, 15:46
Hallo Steffen,

dann hast du das Prinzip doch noch nicht ganz verstanden?

Name: Spalte_I -> I:I

Die gesamte Spalte referenzierst du einfach als Range.
Und die Zelle I2 ist die zweite Zelle in diesem Bereich:

Range("Spalte_I").sort Key1:=Range("Spalte_I").Cells(2), Order1:=xlAscending, ...

SeNf
03.05.2009, 22:29
Hallo Melanie,
wie gesagt licht im dunkel aber noch lange nicht hell :-)
eigentlich denke ich hab ich das ganze schon verstanden,
aber die bezüge funktionieren nicht immer, oder eben der rest vom code je nach dem was ich grad versemmel...
Gruß
Steffen

SeNf
03.05.2009, 23:51
...doch nicht so ganz klar, warum zb. kann ich in dem code unten den color index nicht so suchen: If Range(Cells(lngCounter, bCounter - 1).Interior.ColorIndex = 37 Then wo ist da mein denkfehler?
denn mit der suche nach X funktioniert das ja... If Cells(lngCounter, bCounter - 1) = "X" Then ...

Sub Test()
Dim fFree As Long
Dim KW As Long
Dim t&
Dim rngDatFind
Dim lngCounter
Dim bCounter As Long
'_____________________erste freie reihe suchen in Spalte SP_NR
fFree = Rows.Count
If Range("SP_NR").Cells(fFree).Value = "" Then
fFree = Range("SP_NR").Cells(fFree).End(xlUp).Row
End If
'_____________________Berechnt die aktuelle KW
t = DateSerial(Year(Date + (KW - Weekday(Date)) Mod 7 - 3), 1, 1)
KW = (Date - t - 3 + (Weekday(t) + 1) Mod 7) \ 7 + 1
'_____________________
Set rngDatFind = Rows(4).Find(KW, LookIn:=xlValues)
For lngCounter = 7 To fFree
bCounter = Cells(lngCounter, Columns.Count).End(xlToLeft).Column + 1

'---> If Cells(lngCounter, bCounter - 1) = "X" Then

If Range(Cells(lngCounter, bCounter - 1).Interior.ColorIndex = 37 Then

Range(Cells(lngCounter, bCounter), Cells(lngCounter, rngDatFind.Column)).Interior.ColorIndex = 46
End If
Next
End Sub

Gruß
Steffen

jinx
04.05.2009, 04:44
Moin, Steffen,

eine Range-Anweisung erwartet eine Zelle oder einen Zellenbereich, die Cells-Anweisung selbst nur die Angabe zu einer Zelle selbst. Bei Deiner Anweisung fehlt eine schließende Klammer, und der Code kann meiner Meinung nach ohne Range wie folgt geschrieben werden:

If Cells(lngCounter, bCounter - 1).Interior.ColorIndex = 37 Then
Range(Cells(lngCounter, bCounter), Cells(lngCounter, rngDatFind.Column)).Interior.ColorIndex = 46
End If

SeNf
04.05.2009, 09:59
moin Jinx,
das war natürlich mein erster lösungsansatz, aber das funktioniert so leider nicht, es kommt kein fehler aber es passiert auch nichts, wechsel ich die farbe wieder gegen das "G" dann werden die betreffenden felder rot gefüllt...?
hmmm...
Gruß
Steffen

SeNf
04.05.2009, 10:23
...hier noch der link zum gleichen thema poste leider 2x
Link (http://www.ms-office-forum.net/forum/showthread.php?p=1217349&posted=1#post1217349)
gruß
Steffen

Melanie Breden
04.05.2009, 15:19
Hallo Steffen,

die Syntax ist aber jetzt richtig.
Du prüfst ja auf eine bestimmte Zellfarbe, ist die gesuchte denn überhaupt vorhanden?

SeNf
04.05.2009, 15:38
Hallo Melanie,
die farbe ist da, was ich inzwischen raus habe ist das ein beliebiger text zusätzlich zur farbe in einem der felder sein muß (siehe tabelle im link oben)
gruß
Steffen

BoskoBiati
04.05.2009, 16:20
Hallo Steffen,

nicht ganz richtig! s. meine Antwort im o.g. Link.

jinx
04.05.2009, 17:35
Moin, Steffen und Edgar,

super - wir haben zumindest zwei Threads zu einem Problem, welches keines ist und in denen verquickt alles aufgemischt wird... :(

Thread ist zu!