AW: 2 Spalten in unterschiedlichen Dateien Abgleichen
22.08.2019 14:41:22
Nepumuk
Hallo Emre,
teste mal:
Option Explicit
Public Sub CompareMGNr()
Const WORKBOOK_NAME As String = "Emre1.xlsx" 'Anpassen !!!
Const FiLE_PATH As String = "H:\0821\" 'Anpassen !!!
Dim objcell As Range, objWorkbook As Workbook
Dim objWorksheet As Worksheet
Dim blnFound As Boolean
Dim lngRow As Long
With Application
.AskToUpdateLinks = False
.EnableEvents = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
Set objWorksheet = ThisWorkbook.Worksheets("Tabelle1") 'Anpassen !!!
For Each objWorkbook In Application.Workbooks
If objWorkbook.Name = WORKBOOK_NAME Then
blnFound = True
Exit For
End If
Next
If Not blnFound Then Set objWorkbook = Workbooks.Open(Filename:= _
FiLE_PATH & WORKBOOK_NAME, UpdateLinks:=3)
With objWorkbook.Worksheets("Messgrössenliste")
For lngRow = 7 To .Cells(.Rows.Count, 9).End(xlUp).Row
Set objcell = objWorksheet.Columns(9).Find(What:=.Cells(lngRow, 9).Text, _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
If Not objcell Is Nothing Then
Select Case objcell.Offset(0, 71).Text
Case "Veränderung"
Call objcell.Offset(0, 73).Resize(1, 11).Copy(Destination:=.Cells(lngRow, 52))
Case "entfällt"
.Cells(lngRow, 22).Value = "nicht relevant"
End Select
End If
Next
End With
If Not blnFound Then Call objWorkbook.Close(SaveChanges:=True)
Set objWorkbook = Nothing
Set objcell = Nothing
With Application
.AskToUpdateLinks = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
Gruß
nepumuk