Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Excel Tabelle mittels VBA Werte vergleichen und bei Übereinstimmung Namen zu weisen

Frage Entwicklung VB for Applications

Mitglied: tommhii

tommhii (Level 1) - Jetzt verbinden

28.01.2014, aktualisiert 15:53 Uhr, 6988 Aufrufe, 19 Kommentare

Hallo

ich habe eine Excel Tabelle da ist Spalte A mit Kennziffern belegt, die Kennziffern können mehrfach vorkommen. Spalte B bestimmte Teile namen. Spalte C enthält die Anzahl der Teilenamen. Spalte D,E,F ist leer ab Spalte G stehen wieder die Kennziffern und in H der Hersteller.

sieht so aus

A B C D E F G H
4 einl 100 0 Maier
4 zweil 23 1 Müller
12 drei 1230 2 Schmidt
123 xxxx 200 3 Krause
9088 yyyy 68 4 Hermann
9088 zzzz 999 5 Klaus
9999 dddd 10 6 Bernd

ich möchte das in Zeile A die Kennziffer gegen den Hersteller namen ausgetauscht wird aber leider hab ich keinen richtigen Plan.
Hat jemand eine Idee wie man das mittels VBA lösen kann.

Danke

VG
Tommhi
Mitglied: colinardo
28.01.2014, aktualisiert um 14:05 Uhr
Hallo Tommih,
mach dir eine Hilfsspalte z.B. nehme die Spalte E, dort fügst du dann in die erste Zelle folgende Formel ein:
=SVERWEIS($A1;$G:$H;2;FALSCH)
und kopierst sie über das kleine Viereck unten rechts in der Zelle nach unten.

Falls du es doch mit VBA machen willst hier der Code dafür, wenn ich deine Tabelle richtig interpretiert habe:
01.
Sub Zuordnen() 
02.
    Set sheet = Worksheets(1) 
03.
    Set rngSearchStart = sheet.Range("G1") 
04.
    Set rngSearchEnd = rngSearchStart.End(xlDown) 
05.
    Set rngTargetStart = sheet.Range("A1") 
06.
    Set rngTargetEnd = rngTargetStart.End(xlDown) 
07.
     
08.
    For Each cell In sheet.Range(rngTargetStart, rngTargetEnd) 
09.
        Set foundCell = sheet.Range(rngSearchStart, rngSearchEnd).Find(cell.Value, LookIn:=xlValues) 
10.
        If Not foundCell Is Nothing Then 
11.
            cell.Value = foundCell.Offset(0, 1).Value 
12.
        End If 
13.
    Next 
14.
End Sub
Hier das Demo-Sheet dazu.

Grüße Uwe
Bitte warten ..
Mitglied: tommhii
28.01.2014 um 14:45 Uhr
Hallo Uwe,

danke für die schnelle Antwort aber dein VBA Code bringt bei mir Fehler beim kompilieren an dieser Stelle
Set rngSearchStart = sheet.Range("G1")

Ich lese die Daten sätze aus 2 verschiedenen TXT Dateien erst die Werte Spalte A-C und dann G und H
Ich habe Deinen Code bei mir eingefügt aber es kommt immer der Fehler. Woran kann das liegen?


Sub einlesen()
ChDir "C:\"
Workbooks.OpenText Filename:="C:\Statistik.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1)), TrailingMinusNumbers:=True
Range("G1").Select
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\Hersteller.txt", _
Destination:=Range("$G$1"))
.Name = "Hersteller"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "="
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

VG
Tommhi
Bitte warten ..
Mitglied: colinardo
28.01.2014, aktualisiert um 15:02 Uhr
was für eine Fehlermeldung bringt er denn? denn hier geht es einwandfrei
hast du wohlmöglich die erste Zeile weggelassen? :
Set sheet = Worksheets(1) 
oder du musst den Verweis an dein Ziel-Workbook entsprechend anpassen, vorher am besten einen Verweis darauf erstellen, da du ansonsten das Import-Workbook referenzierst.
Set sheet = Workbooks(1).Worksheets(1)

sieh dir auch mal das Demo-Sheet von oben an...

Grüße Uwe
Bitte warten ..
Mitglied: tommhii
28.01.2014 um 15:38 Uhr
Hallo Uwe,

dein Demo geht bei mir auch aber so bald ich meinen code vorher ablaufen lasse bringt er Variable nicht definiert
Set rngSearchStart = sheet.Range("G1")


VG
Tommhii
Bitte warten ..
Mitglied: colinardo
LÖSUNG 28.01.2014, aktualisiert um 15:53 Uhr
OK dann hast du oben in deinem Projekt Option Explicit stehen, das bedeutet du musst die Variablen über Dim alle vorher definieren, wie hier
01.
Sub Zuordnen() 
02.
    dim sheet as Worksheet, rngSearchStart as Range, rngSearchEnd as Range, rngTargetStart as Range, rngTargetEnd as Range, cell as Range, foundCell as Variant 
03.
    Set sheet = Worksheets(1) 
04.
    Set rngSearchStart = sheet.Range("G1") 
05.
    Set rngSearchEnd = rngSearchStart.End(xlDown) 
06.
    Set rngTargetStart = sheet.Range("A1") 
07.
    Set rngTargetEnd = rngTargetStart.End(xlDown) 
08.
     
09.
    For Each cell In sheet.Range(rngTargetStart, rngTargetEnd) 
10.
        Set foundCell = sheet.Range(rngSearchStart, rngSearchEnd).Find(cell.Value, LookIn:=xlValues) 
11.
        If Not foundCell Is Nothing Then 
12.
            cell.Value = foundCell.Offset(0, 1).Value 
13.
        End If 
14.
    Next 
15.
End Sub
Bitte warten ..
Mitglied: tommhii
28.01.2014 um 15:52 Uhr
Hallo Uwe
hab nochmal alles kontrolliert geht jetzt habe die Zeile noch zugefügt die war in Deiner Demo drin
Dim sheet As Worksheet, cell As Range

Eine Frage hab ich noch kann ich jetzt noch eine Überschrift mittels vba einfügen, in die erste Zeile mit aktuellen Datum.

Danke nochmal im voraus

VG
Tommhii
Bitte warten ..
Mitglied: colinardo
LÖSUNG 28.01.2014, aktualisiert um 16:31 Uhr
klar, aber wo soll deine erste Zeile sein ? in A1 steht doch schon was
sheet.Range("A1").Value = "Deine Überschrift " & date()
wie hast du eigentlich deinen obigen Code hinbekommen .... ?
Bitte warten ..
Mitglied: tommhii
28.01.2014 um 16:41 Uhr
ja ich wollte mir ja nicht die 1. Zeile überschreiben. Was micht noch stört das die Schaltfläche noch zu sehen ist kann man die irgendwie im Nachgang ausblenden.
Bitte warten ..
Mitglied: colinardo
28.01.2014, aktualisiert um 16:50 Uhr
Zitat von tommhii:

ja ich wollte mir ja nicht die 1. Zeile überschreiben. Was micht noch stört das die Schaltfläche noch zu sehen ist
kann man die irgendwie im Nachgang ausblenden.
kann man via Code machen, aber dazu müsstest du den Typ des Buttons erst noch in ein ActiveX-Button ändern. Du kannst ihn aber auch manuell rauslöschen: einmal Rechtsklick auf den Button damit dieser markiert ist, und dann ENTF drücken.
Bitte warten ..
Mitglied: tommhii
07.02.2014, aktualisiert um 09:52 Uhr
Hallo

das hat geklappt, ich habe noch eine ander Frage dazu ich habe 11 verschieden Werkstücke lw , rw; wb (winkel mit Bohrung)
fle, wdb, ufob, ufmb, flemb, dae, daeb, smb diese Teile werden von unterschiedlichen Herstellern produziert.
ich habe jetzt nach dem ich die Daten aus der Datenbank ausgelesen habe folgende Tabelle mit den 3 Spalten
Hersteller Teilebezeichnug Anzahl die 1. Spalte mit der Überschrift Hersteller beginnt mit B4 Teilebezeichnung C4 und Anzahl D4 da die Tabelle sehr lang jetzt ist wollte ich die Tabelle umgestalten in folgende Form
beginnend mit B4 Hersteller die nächsten spalten sollen die 11 Teilearten sein bis M4 und M5 soll dann die Summe aus der Zeile ergeben.
Wie könnet man das mit enen VBA script lösen das aus der einen Tabelle die ander entsteht

Herstellername TeileName Anzahl
Meyer fle 5799
Meyer wdb 665
Meyer ufodb 50
Meyer dae 288
Meyer daeb 33
Müller fle 618
Müller wdb 1303
Müller lw 1850
Müller ufodb 98
Krüger fle 44
Krüger wdb 344
Krüger dae 490
Winter fle 1220
Winter ufodb 507
Winter dae 88
Winter rw 664
Winter smb 17

usw.

die Werte aus der Tabelle wollte ich in diese Tabelle umwandeln
Hersteller fle lw rw dae smb ufodb wdb ...... Summe

wer hätte da einen Tipp für mich.

Danke
VG
Tommhi
Bitte warten ..
Mitglied: colinardo
07.02.2014, aktualisiert um 10:10 Uhr
Hallo Tommhi,
das ist eindeutig ein Fall für eine Pivot-Tabelle, da brauchts kein Makro...
Übrigens, wo sollen denn die Mengen stehen ? unter den Artikeln ?
Grüße Uwe
Bitte warten ..
Mitglied: tommhii
07.02.2014 um 11:17 Uhr
Hallo Uwe,

die Anzahl der Teile sollen in der neuen Tabelle so eingetragen werden

Hersteller fle wdb dae ufodb......... summe
Müller 22 449 0 5234 5705
Meyer 0 18 122 0 ........ 140

kannst du mir da irgendwie weiterhelfen.

VG
Tommhi
Bitte warten ..
Mitglied: colinardo
07.02.2014, aktualisiert um 11:32 Uhr
wie schon gesagt eine Pivot-Tabele erledigt das mit 3 Klicks, die Leute wissen nur meistens nicht, wie diese anzuwenden sind ...

ed96ccf28ff66c037e8c8e4709d47f93 - Klicke auf das Bild, um es zu vergrößern

Demo-Pivot-Tabelle
Bitte warten ..
Mitglied: tommhii
08.02.2014 um 11:19 Uhr
Hallo Uwe,

danke erstmal mit dem Tip Pivot-Tabelle. Aber kann man das ganze nicht noch irgendwie automatisieren .
Ich möchte eine automatisierte Erstellung einer solchen Auswertung haben, damit dies auch jemand machen kann, der keine Ahnung von vba und Pivot-Tabelle etc. hat .

VG
Tommhii
Bitte warten ..
Mitglied: colinardo
08.02.2014, aktualisiert um 11:58 Uhr
klar geht das alles mit entsprechendem Aufwand, mach doch einfach einen Import der Daten in diese Vorlage mit der Pivot-Tabelle dann ist dies ebenfalls automatisiert, und die Pivot-Tabelle wieder aktuell.
Die Pivottabelle lässt sich natürlich auch automatisiert via VBA erstellen :
Demo_Pivot_Automated_227948.xlsm
Bitte warten ..
Mitglied: tommhii
08.02.2014 um 12:13 Uhr
danke dafür , aber was muss ich ändern wenn die Pivot-Tabelle in einen anderen Tabellenblatt entstehen soll.
Bitte warten ..
Mitglied: colinardo
08.02.2014, aktualisiert um 12:29 Uhr
Zitat von tommhii:

danke dafür , aber was muss ich ändern wenn die Pivot-Tabelle in einen anderen Tabellenblatt entstehen soll.
diesen Code nehmen und in Zeile 4 das Destination-Sheet angeben, Zeile 5 legt die Zelle im Destination-Sheet fest.
01.
Sub CreatePivotTable() 
02.
    Dim rngData As Range, rngStart As Range, rngEnd As Range, sheet As Worksheet 
03.
    Set sheet = Worksheets(1) 
04.
    Set targetsheet = Worksheets(2) 
05.
    Set rngDestination = targetsheet.Range("A1") 
06.
    Set rngStart = sheet.Range("B3") 
07.
    Set rngEnd = rngStart.End(xlDown).Offset(0, 2) 
08.
    Set rngData = sheet.Range(rngStart, rngEnd) 
09.
    On Error Resume Next 
10.
    If Not targetsheet.PivotTables("Auswertung") Is Nothing Then 
11.
        targetsheet.PivotTables("Auswertung").TableRange2.Clear 
12.
    End If 
13.
     
14.
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngData).CreatePivotTable TableDestination:=rngDestination, TableName:="Auswertung" 
15.
     
16.
    With targetsheet.PivotTables("Auswertung").PivotFields("Herstellername") 
17.
        .Orientation = xlRowField 
18.
        .Position = 1 
19.
    End With 
20.
    With targetsheet.PivotTables("Auswertung").PivotFields("TeileName") 
21.
        .Orientation = xlColumnField 
22.
        .Position = 1 
23.
    End With 
24.
    targetsheet.PivotTables("Auswertung").AddDataField targetsheet.PivotTables("Auswertung").PivotFields("Anzahl"), "Summe von Anzahl", xlSum 
25.
End Sub
zwischendurch mal lesen schadet auch nicht:
VBA/VBS/WSH/Office Developer Referenzen
Bitte warten ..
Mitglied: tommhii
10.02.2014 um 08:23 Uhr
Hallo Uwe,

danke für deine schnelle Hilfe.

VG
Tommhi
Bitte warten ..
Mitglied: tommhii
10.02.2014, aktualisiert um 15:04 Uhr
Hallo uwe,

ich habe den code bei mir eingebaut in Tabelle1 und versucht mittels eines aktiv Steuerelement Button zu starten, aber es kommt in Tabelle2 nur folgendes Bild.

In Tabelle 1 ist die erste Überschrift der Daten Tabelle in B4 und die Pivot Tabelle soll auch in B4 anfangen. Ich habe dies auch im Code angepast aber es geht nicht.

das erscheint in der Tabelle2

Auswertung
Klicken Sie in diesen Bereich, um den PivotTable-Bericht zu bearbeiten.

VG
Tommhi
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(2)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
VB for Applications
gelöst Excel VBA Werte von 2 verschiedenen Sheets vergleichen und aktualisieren (4)

Frage von drimrim zum Thema VB for Applications ...

VB for Applications
Bestimmte Daten aus eine CSV-Datei in eine Excel-Tabelle importieren (2)

Frage von MariaElena zum Thema VB for Applications ...

Batch & Shell
Werte vergleichen die nicht 100 Prozent gleich sind (4)

Frage von functionstrut zum Thema Batch & Shell ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
gelöst Server erkennt Client nicht wenn er ausserhalb des DHCP Pools liegt (28)

Frage von Mar-west zum Thema LAN, WAN, Wireless ...

Windows Server
Server 2008R2 startet nicht mehr (Bad Patch 0xa) (18)

Frage von Haures zum Thema Windows Server ...

Outlook & Mail
Outlook 2010 findet ost datei nicht (18)

Frage von Floh21 zum Thema Outlook & Mail ...