Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1620to1624
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1620to1624
1620to1624
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel mittels VBA eintragen (BF)

Formel mittels VBA eintragen (BF)
19.04.2018 14:16:47
Matthias
Hallo Leute
wie muss ich schreiben, dass ich unten durch Schleife A1 bis A1000 einsetze?
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NICHT(ISTNV(SVERWEIS(SVERWEIS(A & i;SPZ_RS!A2:D4135;4;FALSCH);Kundenliste!C2:V500;20;FALSCH)))"
Vielen Dank
Matthias

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Range ...
19.04.2018 14:23:30
Fennek
Hallo,
wenn es nicht von Hand gehen darf (relevanten Bereich markieren, Formel einmal eintragen; einmal eintragen und "anwenden auf")
könnte es so gehen:

Range("A1:A1000").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NICHT(ISTNV(SVERWEIS(SVERWEIS(A & i;SPZ_RS!A2:D4135;4;FALSCH);Kundenliste!C2:V500;20;FALSCH))) _
mfg
AW: Range ...
19.04.2018 14:29:43
Matthias
Hallo Fennek,
danke, es soll im inneren Sverweis A1; A2;.....A1000 eingesetzt werden, da die bedingte Formatierung
in 2010 eben nicht automatisch ersetzt. Also muss ich mit einer Schleife 1000 BF erzeugen.
Vielen Dank
Matthias
Anzeige
AW: Formel mittels VBA eintragen (BF)
19.04.2018 14:35:53
ChrisL
Hi
Wozu es die Schleife braucht verstehe ich nicht.
Sub t()
Range("A1:A1000").FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NICHT(ISTNV(SVERWEIS(SVERWEIS(A1;SPZ_RS!A$2:D$4135;4;FALSCH);Kundenliste!C$2:V$500;20;FALSCH)) _
)"
End Sub

Sub tt()
Dim i As Long
For i = 1 To 1000
Cells(i, 1).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NICHT(ISTNV(SVERWEIS(SVERWEIS(A" & i & ";SPZ_RS!A2:D4135;4;FALSCH);Kundenliste!C2:V500;20; _
FALSCH)))"
Next i
End Sub
cu
Chris
AW: Formel mittels VBA eintragen (BF)
19.04.2018 15:07:54
Matthias
Hallo alle Engagierten,
mein Thema ist nicht der Sverweis per se oder das Nicht. Da in 2010 in der bedingten Formatierung, nicht automatisch die Zellbezüge angepasst werden. Dazu stand auch was im Netz. Also will ich jetzt in einer Schleife 1000 Zeilen Beispielhaft E1 bis E1000 mit
"=NICHT(ISTNV(SVERWEIS(SVERWEIS(A" & i & ";SPZ_RS!A2:D4135;4;FALSCH);Kundenliste!C2:V500;20; _
FALSCH)))" formatieren. mein Problem ist, das A" & i & "; genau so in der BF steht und nicht A1; A2...
A999;A1000.
Ich hoffe ich konnte es jetzt besser formulieren.
Danke
Matthias
Anzeige
AW: Formel mittels VBA eintragen (BF)
19.04.2018 15:25:01
Matthias
Userbild
So sieht es aus !
Da sollte A1 stehen usw.
VG
Matthias
AW: Formel mittels VBA eintragen (BF)
19.04.2018 15:52:46
ChrisL
Hi
Das Problem im Screenshot habe ich dir bereits gelöst (siehe Antwort von vorher).
In XL2010 war tatsächlich etwas betr. den Bezügen. Ich meine die ZS-Schreibweise gemäss Vorschlag Daniel sollte helfen.
cu
Chris
AW: Formel mittels VBA eintragen (BF)
19.04.2018 16:07:31
Matthias
Hallo chris,
vielen Dank, geht auch mit A1 usw.
der Haaken war das : A" & i & "
lieben Dank
Matthias
Anzeige
AW: Formel mittels VBA eintragen (BF)
19.04.2018 14:38:04
Daniel
Hi
du brauchst keine Schleife.
Schreibe die Formel mit Z1S1-Adressierungsart, dann wird deutlich, dass die Formel eigentlich für alle Zellen gilt:
Range("A1:A1000").Formatcontitions.Add Type:=xlexpression, formula1:= _
"=NICHT(ISTNV(SVERWEIS(SVERWEIS(Z(0)S1;SPZ_RS!Z2S1:Z4135S4;4;FALSCH);Kundenliste!Z2S3:Z500S22;20;FALSCH)))"
Gruß Daniel
AW: Formel mittels VBA eintragen (BF)
19.04.2018 14:38:15
Fennek
VBA spricht englisch, vielleicht muss es übersetzt werden.

for i = 1 to 1000
cells(i,1).FormatConditions.Add Type:=xlExpression, Formula1:= _
"=NICHT(ISTNV(SVERWEIS(SVERWEIS(A" & i & ";SPZ_RS!A2:D4135;4;FALSCH);Kundenliste!C2:V500;20; _
FALSCH)))"
next i
(ungetestet)
Anzeige
AW: Formel mittels VBA eintragen (BF)
19.04.2018 14:48:50
Mirko
Hi Ihr,
passt fast - nur auch "SVERWEIS" und "NICHT" auf Englisch übersetzen:

=NOT(ISNA(VLOOKUP(VLOOKUP(A & i,SPZ_RS!R[1]C:R[4134]C[3],4,FALSE),Kundenliste!R[1]C[2]:R[499]C[ _
21],20,FALSE)))
VG
Mirko
AW: Formel mittels VBA eintragen (BF)
19.04.2018 15:04:11
Daniel
VBA spricht mit Excel englisch, aber keine Regel ohne Ausnahme ;-)
die Regel der Bedingten Formatierung muss in landestypischer Schreibweise (hier also deutsch) angegeben werden (getestet im Excel 2013 und war in 2010 auch so)
dafür kann man sowohl A1- als auch Z1S1-Bezugsart verwenden. Man darf aber nicht mischen, sondern muss sich für eine entscheiden. Anhand der ersten Adressen in der Formel versuchst VBA dann zu erkennen, was vorliegt, daher muss man beim Schreiben auf Eindeutigkeit achten:
dh ZS1 kann sowohl eine A1- als auch eine Z1S1-Adresse in Kurzsschreibweise sein, dh als Z1S1-Adresse sollte man dann besser: Z(0)S1 schreiben, dann ist es eindeutig.
Gruß Daniel
Anzeige
Prüfe die Fkt. der VBA-erzeugten bedF, denn ...
19.04.2018 16:41:49
lupo1
... da gibt es anscheinend systematisch Probleme!
http://www.online-excel.de/fom/fo_read.php?f=1&bzh=91915&h=91896#a123x
Und zwar durchaus über viele XL-Versionen mit verschiedenen Erscheinungsformen.
Ganz sicher ist nur:
- Vergib zuerst einen definierten Namen für die bedF-Formel
- und verwende dann diesen in ihr.
AW: Formel mittels VBA eintragen (BF)
19.04.2018 17:12:26
snb
Hast du versucht?
Sub M_snb() 'Konstruktionsmakro für eine Tabelle
[A1] = 100
[B2:D2] = Array(1, 3, 197)
With [B2:D3].FormatConditions.Add(2, , "=MIN(ABS($B2:$D2-$A$1))=ABS(B2-$A$1)")
.Font.ColorIndex = 3
.ModifyAppliesToRange [B2:D6]
End With
End Sub

Klappt auch nicht
19.04.2018 17:21:02
lupo1
1. Die 1 (falsch) wird formatiert, nicht (richtig) die 3 und 197
2. Anm: Du meinstest dreimal [B2:D2], nicht [B2:D3] bzw. [B2:D6]
3. Du beziehst Dich auf mich, aber bist im falschen Ast. Zum Glück erkannt.
AW: Klappt auch nicht
19.04.2018 18:34:38
snb
Die Formel war falsch, jedoch das ist nicht das Thema, oder ? 
Sub M_snb()
[A1] = 100
[B2:D2] = Array(1, 3, 197)
With [B2:D3].FormatConditions.Add(2, , "=MAX($B2:$D2)-$A$1=ABS(B2-$A$1)")
.Font.ColorIndex = 3
.ModifyAppliesToRange [B2:D6]
End With
End Sub

Anzeige
Hm ... zuerst dachte ich, es klappt. Aber ...
19.04.2018 18:52:07
lupo1
... mal ja, mal nein.
Im Moment nicht rekonstruierbar.
Wo kam denn die falsche Formel her? Von mir? Ich hatte gar nicht hingeschaut ...
AW: Klappt auch nicht
20.04.2018 10:36:20
snb
Von den Ästen hier verstehe ich nur Bahnhof, oder Gleis 11.
Scheinen mir unnötig kompliziert und ohne Mehrwert.

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige