AW: selection.find ohne ergebnis
22.08.2005 11:19:43
Daniel
Sub einlesen()
Windows("GC.xls").Activate
Worksheets("Einlesen").Activate
fileToOpen = Application _
.GetOpenFilename("Messung (*.xls), *.txt")
Workbooks.Open Filename:=fileToOpen
Dim datei, lZelle, sWert, uWert, zWert As String
Dim anzahl, i, u As Integer
Dim S As Range
datei = ActiveWorkbook.Name
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
anzahl = ActiveWorkbook.Sheets.Count
'SAP Tabelle für Vergleich öffnen im Hintergrund
Workbooks.Open Filename:="H:\Projekt GC\SAP\SAP-Daten für GC.xls"
Windows("SAP-Daten für GC.xls").Activate
ActiveWindow.WindowState = xlMinimized
For u = 1 To anzahl
' Fabriknummer übertragen
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Sheets(u).Select
Range("G31").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
i = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
lZelle = "B" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Fabriknummer glätten
ActiveCell.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=False
ActiveCell.Replace What:="/", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=False
ActiveCell.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
ReplaceFormat:=False
'Position in SAP Tabelle bestimmen
sWert = ActiveCell.Value
Windows("SAP-Daten für GC.xls").Activate
Columns("CJ:CJ").Select
Set S = Selection.Find(What:=sWert, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
// wenn er hier jetzt die fabrnr nicht in der sap tabelle findet soll er die nächsten schritte bis zum end if auslassen
If Not S Is Nothing Then
'Hersteller übertragen
uWert = "BY" & CStr(ActiveCell.Row)
Range(uWert).Select
Selection.Copy
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
zWert = "A" & CStr(i + 1)
Range(zWert).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Baujahr übertragen
Windows("SAP-Daten für GC.xls").Activate
uWert = "CB" & CStr(ActiveCell.Row)
Range(uWert).Select
Selection.Copy
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
zWert = "C" & CStr(i + 1)
Range(zWert).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Tech. Platz übertragen
Windows("SAP-Daten für GC.xls").Activate
uWert = "CD" & CStr(ActiveCell.Row)
Range(uWert).Select
Selection.Copy
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
zWert = "D" & CStr(i + 1)
Range(zWert).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Tech. Platz Bezeichnung übertragen
Windows("SAP-Daten für GC.xls").Activate
uWert = "CE" & CStr(ActiveCell.Row)
Range(uWert).Select
Selection.Copy
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
zWert = "E" & CStr(i + 1)
Range(zWert).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Zugehörigkeit übertragen
Windows("SAP-Daten für GC.xls").Activate
uWert = "BW" & CStr(ActiveCell.Row)
Range(uWert).Select
Selection.Copy
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
zWert = "F" & CStr(i + 1)
Range(zWert).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
'Messdatum übertragen
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Range("B15").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
lZelle = "G" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.WindowState = xlMinimized
'Gaswerte übertragen
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Range("G42:G52").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
lZelle = "H" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveWindow.WindowState = xlMinimized
'Bemerkung GC
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Range("G36").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
lZelle = "S" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'°C oben
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Range("G34").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
lZelle = "T" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'°C unten
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Range("I97").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
lZelle = "U" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Alterungwserte übertragen
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Range("I89:I95").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
lZelle = "V" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
ActiveWindow.WindowState = xlMinimized
'Bemerkung Alterung
Windows(datei).Activate
ActiveWindow.WindowState = xlNormal
Range("G99").Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlNormal
Worksheets("Einlesen").Activate
lZelle = "AC" & CStr(i + 1)
Range(lZelle).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next
Workbooks(datei).Close SaveChanges:=False
Workbooks("SAP-Daten für GC.xls").Close SaveChanges:=False
Windows("GC.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("A1").Select
End Sub