Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
604to608
604to608
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sverweis mit VBA

Forumthread: Sverweis mit VBA

Sverweis mit VBA
27.04.2005 18:43:57
Gisela
Hallo,
ich muß in mehreren Spalten mit Sverweis arbeiten. Da die Daten anschließend noch weiter verwendet werden, muß ich die Formeln auch wieder löschen. Das ist von der Sache her auch kein Problem (kopie/Werte). Aber das ganze umfaßt 200 Zeilen und mehrere Spalten.
Hier der Sverweis mal für die Spalte B in der Tabelle2
=Testefehler(SVERWEIS(A3;Tabelle1!A:J;2;FALSCH)
Kann ich diesen Sverweis auch per VBA einsetzen, so dass nachher auch keine Formeln, sondern nur Werte in der Spalte stehen.
Oder gibt es hier nur die Möglichkeit über den Recorder?
Grüße und vielen Dank für jeden Hinweis
Gisela
Anzeige

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sverweis mit VBA
27.04.2005 19:39:05
Cardexperte
Hello Gisele, also sverweis in VBA ist: worksheetfunction.CountIf(), gleiche Syntax wie sverweis, was mir nicht klar ist ist die Funktion Testefehler!
Gruss WS
AW: Sverweis mit VBA
27.04.2005 19:44:32
Gisela
Hallo,
unter Testefehler ist die Function hinterlegt, mit der "NV" usw. unterdrückt wird.
Da ich nur Recordererfahrung habe, kannst Du mir sagen, wie eine Lösung ohne Recorder aussieht?
Vielen Dank und Gruß
Gisela
Anzeige
AW: Sverweis mit VBA
27.04.2005 19:57:00
Cardexperte
Hello Gisela,
Testefehler ist also eine selbst geschriebene Funktion?
also beim Debuggen würde ich erst einmal nur mit countif arbeiten und testen, falls Fehler muss im Code eine Fehlerbehandlungroutine eingebaut werden , z.B.
mit if err.number 0 then else, oder on error resume next, das kommt darauf an, am besten du schickst mal ein dummyfile und ich schreib dann mal das Makro dafür, sollte eigentlich kein Problem sein!
Gruss WS
Anzeige
AW: Sverweis mit VBA
27.04.2005 20:33:26
Gisela
Hallo,
es ist sehr nett, wenn Du mir helfen willst.
Wie schon gesagt habe ich das ganze per Recorder und sieht so aus:

Sub Sverweis()
Range("B3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-1],Tabelle1!C[-1]:C[8],2,FALSE))"
Range("C3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-2],Tabelle1!C[-2]:C[7],2,FALSE))"
Range("D3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-3],Tabelle1!C[-3]:C[6],2,FALSE))"
Range("E3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-4],Tabelle1!C[-4]:C[5],2,FALSE))"
Range("F3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-5],Tabelle1!C[-5]:C[4],2,FALSE))"
Range("G3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-6],Tabelle1!C[-6]:C[3],2,FALSE))"
Range("H3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-7],Tabelle1!C[-7]:C[2],2,FALSE))"
Range("I3").Select
ActiveCell.FormulaR1C1 = _
"=Testefehler(VLOOKUP(RC[-8],Tabelle1!C[-8]:C[1],2,FALSE))"
Range("B3:I3").Select
Selection.Copy
Range("B4:I200").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B3:I200").Select
Application.CutCopyMode = False
Selection.Copy
Range("B3:I200").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B3").Select
Call Nullen
End Sub

Über den Makro "Nullen" werden die "Nullen" gelöscht, da ich diese als Wert erhalte, wenn nicht alle z.Zt. 200 Zeilen belegt sind und nicht weiss, wie ich dass mit der Sverweisformel hinbekomme, dass statt Nullen nichts angezeigt wird.
Grüße
Gisela
Anzeige
AW: Sverweis mit VBA
27.04.2005 22:56:50
Beate
Hallo Gisela,
füge diesen Code ins Codefenster von Tabelle2 ein:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Dim rngB As Range
Set rngB = Sheets("Tabelle1").Range("A:J")
Cells(Target.Row, 2).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 2, False))
Cells(Target.Row, 3).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 3, False))
Cells(Target.Row, 4).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 4, False))
Cells(Target.Row, 5).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 5, False))
Cells(Target.Row, 6).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 6, False))
Cells(Target.Row, 7).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 7, False))
Cells(Target.Row, 8).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 8, False))
Cells(Target.Row, 9).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 9, False))
Cells(Target.Row, 10).Value = Application.WorksheetFunction.Testefehler(VLookup(Cells(ActiveCell.Row, 1), rngB, 10, False))
Application.EnableEvents = True
End If
End Sub

Deine Funktion Testefehler kenne ich ja nicht, von daher habe ich meinen Code ohne sie getestet und da lief er. Bei Eingabe in Tabelle2! Spalte A wird nun in der aktiven Zeile Spalten B:J jeweils der entsprechende Sverweis aus Tabelle 1 Spalte A:J genommen.
Gruß,
Beate
Anzeige
AW: Sverweis mit VBA
28.04.2005 07:15:06
Gisela
Hallo Beate,
vielen Dank für Deine Hilfe.
Ich habe den Code ins Tabellenblatt übernommen und bekomme die Fehlermeldung

Sub oder 


Function nicht definiert. Markiert ist (VLookup.
Die 


Function TesteFehler sieht so aus:


Function TesteFehler(x1)
If IsError(x1) = True Then
TesteFehler = 0
Else
TesteFehler = x1
End If
End Function



Hast Du noch einen Tipp?

Viele Grüße

Gisela
Anzeige
AW: funktioniert
28.04.2005 08:05:19
Gisela
Hallo Beate,
sorry, funktioniert jetzt, nachdem ich TesteFehler entfernt habe.
Vielen Dank für Deine Hilfe
Grüße
Gisela
AW: funktioniert
28.04.2005 10:57:54
Beate
Hallo Gisela,
bei mir geht auch diese Verkürzung (Makro im Codefenster von Tabelle2!):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Application.EnableEvents = False
Dim rngA As Range
Dim rngB As Range
Set rngA = Cells(ActiveCell.Row, 1)
Set rngB = Sheets("Tabelle1").Range("A:J")
Cells(Target.Row, 2).Value = Application.VLookup(rngA, rngB, 2, False)
Cells(Target.Row, 3).Value = Application.VLookup(rngA, rngB, 3, False)
Cells(Target.Row, 4).Value = Application.VLookup(rngA, rngB, 4, False)
Cells(Target.Row, 5).Value = Application.VLookup(rngA, rngB, 5, False)
Cells(Target.Row, 6).Value = Application.VLookup(rngA, rngB, 6, False)
Cells(Target.Row, 7).Value = Application.VLookup(rngA, rngB, 7, False)
Cells(Target.Row, 8).Value = Application.VLookup(rngA, rngB, 8, False)
Cells(Target.Row, 9).Value = Application.VLookup(rngA, rngB, 9, False)
Cells(Target.Row, 10).Value = Application.VLookup(rngA, rngB, 10, False)
Application.EnableEvents = True
End If
End Sub

Gruß,
Beate
Anzeige
AW: funktioniert nicht ganz
28.04.2005 11:20:31
Gisela
Hallo,
vielen Dank an beide.
An Beate die Frage: "Change" bezieht sich immer nur auf die Eingabe in einer Zelle.
Kann das auch angepasst werden, damit das Ereignis auch ausgelöst wird, wenn ich z.B. mehrere Zellen gleichzeitig einkopiere.
Und funktioniert das dann auch, wenn die Daten nicht reinkopiert, sondern über einen Makro eingelesen werden?
Das ist eine wahrlich harte Nuß für mich.
Grüße
Gisela
Anzeige
Codeerweiterung gesucht
28.04.2005 12:07:02
Beate
Hallo Gisela,
ja, dieses Makro springt jeweils nach Eingabe in Spalte A an.
Für deinen neuen Wunsch müßte man wohl irgendwas mit ner Schleife basteln, die solange läuft, bis alle neu zugefügten Zeilen in A die entsprechenden Sverweise erhalten haben.
Aber damit bin ich codemäßig an meinen Grenzen angelangt, bzw. fehlt mir die Zeit, es bis zur Lösung zu verfolgen.
Zu deiner Frage: "Und funktioniert das dann auch, wenn die Daten nicht reinkopiert, sondern über ein Makro eingelesen werden" - ja sicher, man könnte das Makro, über das eingelesen wird ja direkt entsprechend erweitern. Aber auch dies kann ich dir nicht umsetzen.
Ich habe die Frage mal auf offen gestellt.
Gruß,
Beate
Anzeige
AW: Codeerweiterung gesucht
28.04.2005 17:53:37
Gisela
Hallo Beate,
vielen Dank für die Hilfe.
Ich hab den Ablauf jetzt komplett mit dem Recorder aufgenommen.
Funktioniert vielleicht nicht optimal - aber funktioniert.
Grüße
Gisela
AW: Sverweis mit VBA
28.04.2005 08:21:48
Cardexperte
sorry, war natürlich vlookup
;

Forumthreads zu verwandten Themen

Mit VBA In Zelle Excelformel schreiben
Gho-Reg

Hi Leute
Ich habe hier diesen netten VBA Code geschrieben und leider wie solls auch anders sein kommt dauernd eine Fehlermeldung :)
CODE:
rowbeforeinteger = 2
Dim text As String
text = "=IF(" & epsoldcolumn & rowbeforeinteger & "=""x"";1;0)+IF(" & emailsoldco...
25.08.2008 11:08:33
Thread-ID: 1004403
6 Beiträge
variabler SVerweis mit VBA
Jonathan

Hallo zusammen,
in einer Tabelle sind verschiedene Zeilen mit einem "x" in Spalte C markiert. Die Anzahl der "x" variert.
Nun versuche ich ein Makro zu erstellen, welches mir, wenn eine Zeile mit einem "x" markiert ist in
Spalte B einen SVerweis (Suchwert steht in Spalte A) einträg...
17.06.2009 14:28:57
Thread-ID: 1082250
3 Beiträge
SVerweis ??? in VBA
kirsche

hallo leute,
mal wieder brauche ich eure hilfe.
ich weiß nicht, ob ich mit dem SVerweis in VBA übehaupt richtig liege. in B1 steht mein 1. suchbegriff (25) und in A3, A5 ... A25 (immer in zweierschritten) steht mein 2. suchbegriff.
nun möchte ich in tabelle "Stammdaten" zuerst die s...
08.07.2009 23:26:22
Thread-ID: 1087239
5 Beiträge
SVerweis in VBA
Stefanglander

Hallo Forum,
ich habe folgenden Code in einer Zelle:
=WENN(C4="";"";SVERWEIS(C4;Stoffplan!$A$2:$F$100;2;FALSCH))
Mag mir jamand sagen wie und wo ich das in VBA hinterlegen kann.
Danke im Vorfelde
Stefan
05.10.2009 21:11:52
Thread-ID: 1107351
11 Beiträge
zu langer SVERWEIS in VBA möglich?
Holger

Hallo liebe Leute,
hab diese Frage schon bei "Office-Lösung" gestellt, mir konnte aber nicht geholfen werden.
Folgendes Problem:
ich möchte mit einem SVERWEIS in ca. 15 verschiedenen Tabellen suchen. In Spalte A ist mein Suchkriterium und in Spalte B ein Begriff (Ort). Ich möchte al...
04.01.2010 09:32:17
Thread-ID: 1126969
14 Beiträge
Anzeige
Formel Sverweis in VBA
Marko Albrecht

Hallo,
ich hoffe mir kann bei meinem, nachfolgend beschriebenen, Problem geholfen werden.Das Problem ist , daß in der nachstehenen Formel(Wenn, Istfehler, Sverweis) VBA ein Hochkomma in die Formel schreibt und die Formel dadurch nicht Ihre Funktion erfüllt.Die Bezugszelle der Formel Sverweis...
11.04.2002 14:27:49
Thread-ID: 112467
10 Beiträge
Anzeige
Formel per VBA in Zelle schreiben
einfachf

Hallo Zusammen,
ich habe folgendes Problem
Vielleicht kann mir ja jemand helfen.
Ich prüfe in welcher Zelle der letzte Wert steht mit
CellCount = Cells(65536, 23).End(xlUp).Row
Nun möchte ich in sagen wir mal Cells(CellCount, 21) die Formel eintragen
Summe aus Cells(Cel...
20.02.2010 14:24:22
Thread-ID: 1139917
7 Beiträge
Sverweis mit VBA funktionier nicht richtig
F.

Hallo zusammen
Ich möchte zu einer Ziffer, welche ab dem zweiten Blatt in A1 steht, die dazugehörige Bezeichnung aus dem ersten Blatt "Konti" (Spalte B) ziehen und diese dann im Feld B1 eintragen. Irgendwas mache ich falsch.
Sub Konten_bezeichnen() Dim i As Long For i = Sheets("Konti")...
10.03.2010 14:58:20
Thread-ID: 1144879
5 Beiträge
SVerweis in VBA umsetzen
Ines

Hallo,
ich habe mir ein Userform erstellt, da ich Daten in Bezug auf das Datum in eine Tabelle eintragen muss.
Mein Problem ist, dass die Daten nicht irgendwo automatisch eingetragen werden sollen.
Ich würde gerne
im Userform 4 Textfelder ausfüllen und das Datum angeben
D...
10.03.2010 21:36:01
Thread-ID: 1145009
9 Beiträge
Formel in VBA umsetzen
Josef


Hallo!
Wie würde bitte die untenstehende Formel in VBA umgesetzt lauten?
=RUNDEN(F11*0,2;2)
F11 wäre in diesem Fall eine TextBox in einer UserForm.
Danke
Josef
15.04.2010 07:06:19
Thread-ID: 1151875
3 Beiträge
SVerweis mittels VBA
Marta

Hallo Liebe Forumer,
habe eine Frage zu nem SVerweis. Wie realisiere ich =SVERWEIS(B59;'04 Fehlermanagement Fs Kommen'!A1:B5837;2;FALSCH) mittels VBA) ich verstehe es mal so gar nicht... :(
hab es mit dem Makrorekorder aufgezeichnet. nur das Problem ist, dass die Zeilenanzahl von Woche...
04.05.2010 10:40:36
Thread-ID: 1155280
2 Beiträge
VBA Anzahl in Zelle C18 schreiben/ einfrieren
Marcy

Hallo Forum,
mit unterem Code entferne ich meine doppelten Einträge in Spalte A ab Zeile 25.
Funktioniert auch.
Jetzt hätte ich allerdings gerne mindestens eine Erweiterung:
In Zelle C18, soll vor dem Löschen die Anzahl der gefüllten Zellen (handelt sich um Zahlen) eingetragen w...
27.10.2011 08:24:59
Thread-ID: 1235504
3 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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