Hallo,
sollte laufen...
Public Sub Werte()
Dim rngFund As Range
Dim vaSuchbegriff As Variant
vaSuchbegriff = Sheets("1").Range("E5").Value
If Not IsDate(vaSuchbegriff) Then
MsgBox "Bitte ein gültiges Datum eintragen."
Sheets("1").Range("E5") = ""
Sheets("1").Range("E5").Select
Exit Sub
End If
Set rngFund = Sheets("2").Columns(1).Find(vaSuchbegriff, LookIn:=xlValues, Lookat:= _
xlPart)
If Not rngFund Is Nothing Then
With Sheets("2")
.Range(.Cells(rngFund.Row, 37), .Cells(rngFund.Row, 49)).Value = _
.Range(.Cells(rngFund.Row, 37), .Cells(rngFund.Row, 49)).Value
.Cells(rngFund.Row, 58).Value = .Cells(rngFund.Row, 58).Value
.Range(.Cells(rngFund.Row, 61), .Cells(rngFund.Row, 66)).Value = _
.Range(.Cells(rngFund.Row, 61), .Cells(rngFund.Row, 66)).Value
.Range(.Cells(rngFund.Row, 71), .Cells(rngFund.Row, 74)).Value = _
.Range(.Cells(rngFund.Row, 71), .Cells(rngFund.Row, 74)).Value
End With
Else
MsgBox "Das Datum " & vaSuchbegriff & " wurde" & vbLf & _
" in Blatt 2 nicht gefunden."
Sheets("1").Range("E5") = ""
Sheets("1").Range("E5").Select
End If
Set rngFund = Sheets("3").Columns(1).Find(vaSuchbegriff, LookIn:=xlValues, Lookat:= _
xlPart)
If Not rngFund Is Nothing Then
With Sheets("3")
.Range(.Cells(rngFund.Row, 3), .Cells(rngFund.Row, 6)).Value = _
.Range(.Cells(rngFund.Row, 3), .Cells(rngFund.Row, 6)).Value
.Range(.Cells(rngFund.Row, 8), .Cells(rngFund.Row, 11)).Value = _
.Range(.Cells(rngFund.Row, 8), .Cells(rngFund.Row, 11)).Value
.Range(.Cells(rngFund.Row, 13), .Cells(rngFund.Row, 16)).Value = _
.Range(.Cells(rngFund.Row, 13), .Cells(rngFund.Row, 16)).Value
.Range(.Cells(rngFund.Row, 20), .Cells(rngFund.Row, 23)).Value = _
.Range(.Cells(rngFund.Row, 20), .Cells(rngFund.Row, 23)).Value
.Range(.Cells(rngFund.Row, 25), .Cells(rngFund.Row, 28)).Value = _
.Range(.Cells(rngFund.Row, 25), .Cells(rngFund.Row, 28)).Value
.Range(.Cells(rngFund.Row, 30), .Cells(rngFund.Row, 33)).Value = _
.Range(.Cells(rngFund.Row, 30), .Cells(rngFund.Row, 33)).Value
.Range(.Cells(rngFund.Row, 37), .Cells(rngFund.Row, 40)).Value = _
.Range(.Cells(rngFund.Row, 37), .Cells(rngFund.Row, 40)).Value
.Range(.Cells(rngFund.Row, 42), .Cells(rngFund.Row, 45)).Value = _
.Range(.Cells(rngFund.Row, 42), .Cells(rngFund.Row, 45)).Value
.Range(.Cells(rngFund.Row, 47), .Cells(rngFund.Row, 50)).Value = _
.Range(.Cells(rngFund.Row, 47), .Cells(rngFund.Row, 50)).Value
.Range(.Cells(rngFund.Row, 52), .Cells(rngFund.Row, 57)).Value = _
.Range(.Cells(rngFund.Row, 52), .Cells(rngFund.Row, 57)).Value
.Range(.Cells(rngFund.Row, 59), .Cells(rngFund.Row, 62)).Value = _
.Range(.Cells(rngFund.Row, 59), .Cells(rngFund.Row, 62)).Value
.Range(.Cells(rngFund.Row, 64), .Cells(rngFund.Row, 67)).Value = _
.Range(.Cells(rngFund.Row, 64), .Cells(rngFund.Row, 67)).Value
.Range(.Cells(rngFund.Row, 71), .Cells(rngFund.Row, 74)).Value = _
.Range(.Cells(rngFund.Row, 71), .Cells(rngFund.Row, 74)).Value
.Range(.Cells(rngFund.Row, 76), .Cells(rngFund.Row, 79)).Value = _
.Range(.Cells(rngFund.Row, 76), .Cells(rngFund.Row, 79)).Value
.Range(.Cells(rngFund.Row, 81), .Cells(rngFund.Row, 84)).Value = _
.Range(.Cells(rngFund.Row, 81), .Cells(rngFund.Row, 84)).Value
.Range(.Cells(rngFund.Row, 88), .Cells(rngFund.Row, 91)).Value = _
.Range(.Cells(rngFund.Row, 88), .Cells(rngFund.Row, 91)).Value
.Range(.Cells(rngFund.Row, 93), .Cells(rngFund.Row, 96)).Value = _
.Range(.Cells(rngFund.Row, 93), .Cells(rngFund.Row, 96)).Value
.Range(.Cells(rngFund.Row, 98), .Cells(rngFund.Row, 101)).Value = _
.Range(.Cells(rngFund.Row, 98), .Cells(rngFund.Row, 101)).Value
.Range(.Cells(rngFund.Row, 105), .Cells(rngFund.Row, 108)).Value = _
.Range(.Cells(rngFund.Row, 105), .Cells(rngFund.Row, 108)).Value
.Range(.Cells(rngFund.Row, 110), .Cells(rngFund.Row, 113)).Value = _
.Range(.Cells(rngFund.Row, 110), .Cells(rngFund.Row, 113)).Value
.Range(.Cells(rngFund.Row, 115), .Cells(rngFund.Row, 118)).Value = _
.Range(.Cells(rngFund.Row, 115), .Cells(rngFund.Row, 118)).Value
.Range(.Cells(rngFund.Row, 122), .Cells(rngFund.Row, 125)).Value = _
.Range(.Cells(rngFund.Row, 122), .Cells(rngFund.Row, 125)).Value
.Range(.Cells(rngFund.Row, 127), .Cells(rngFund.Row, 130)).Value = _
.Range(.Cells(rngFund.Row, 127), .Cells(rngFund.Row, 130)).Value
.Range(.Cells(rngFund.Row, 132), .Cells(rngFund.Row, 135)).Value = _
.Range(.Cells(rngFund.Row, 132), .Cells(rngFund.Row, 135)).Value
.Range(.Cells(rngFund.Row, 139), .Cells(rngFund.Row, 142)).Value = _
.Range(.Cells(rngFund.Row, 139), .Cells(rngFund.Row, 142)).Value
.Range(.Cells(rngFund.Row, 144), .Cells(rngFund.Row, 147)).Value = _
.Range(.Cells(rngFund.Row, 144), .Cells(rngFund.Row, 147)).Value
.Range(.Cells(rngFund.Row, 149), .Cells(rngFund.Row, 152)).Value = _
.Range(.Cells(rngFund.Row, 149), .Cells(rngFund.Row, 152)).Value
.Range(.Cells(rngFund.Row, 167), .Cells(rngFund.Row, 169)).Value = _
.Range(.Cells(rngFund.Row, 167), .Cells(rngFund.Row, 169)).Value
.Range(.Cells(rngFund.Row, 172), .Cells(rngFund.Row, 174)).Value = _
.Range(.Cells(rngFund.Row, 172), .Cells(rngFund.Row, 174)).Value
.Range(.Cells(rngFund.Row, 177), .Cells(rngFund.Row, 179)).Value = _
.Range(.Cells(rngFund.Row, 177), .Cells(rngFund.Row, 179)).Value
.Range(.Cells(rngFund.Row, 187), .Cells(rngFund.Row, 189)).Value = _
.Range(.Cells(rngFund.Row, 187), .Cells(rngFund.Row, 189)).Value
End With
Else
MsgBox "Das Datum " & vaSuchbegriff & " wurde" & vbLf & _
" in Blatt 3 nicht gefunden."
Sheets("1").Range("E5") = ""
Sheets("1").Range("E5").Select
End If
End Sub
Gruß Werner