Probleme mit IF
17.07.2008 13:38:56
Gordon
ich habe folgendes Makro was bisher lief:
Private Sub CommandButton1_Click()
Call Tabelle3.Listen
End Sub
Sub Listen()
Dim i, Anzahl As Long
Dim Datei As String
Dim VZ$
Application.DisplayAlerts = False
Application.EnableEvents = False
Datei = Worksheets("Listen erstellen").Range("e6").Value
If Worksheets("Listen erstellen").Range("e6").Value = "" Then
Datei = "Listengenerator2-803 Original.xls"
End If
On Error GoTo err
Workbooks.Open Filename:=ThisWorkbook.Path & "\" & Datei, ReadOnly:=True
err:
If err.Number = 1004 Then
MsgBox "Dateiname nicht korrekt oder die Originaldatei befindet sich nicht in diesem Ordner: " & _
ThisWorkbook.Path & "\", vbCritical, "Fehler"
Exit Sub
End If
ThisWorkbook.Sheets("Generator").Range("a1:a2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("a3:a2502").Value
ThisWorkbook.Sheets("Generator").Range("b1:b2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("b3:b2502").Value
ThisWorkbook.Sheets("Generator").Range("c1:c2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("c3:c2502").Value
ThisWorkbook.Sheets("Generator").Range("d1:d2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("d3:d2502").Value
ThisWorkbook.Sheets("Generator").Range("e1:e2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("e3:e2502").Value
ThisWorkbook.Sheets("Generator").Range("j1:j2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("f3:f2502").Value
ThisWorkbook.Sheets("Generator").Range("k1:k2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("g3:g2502").Value
ThisWorkbook.Sheets("Generator").Range("l1:l2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("h3:h2502").Value
ThisWorkbook.Sheets("Generator").Range("m1:m2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("i3:i2502").Value
ThisWorkbook.Sheets("Generator").Range("n1:n2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("j3:j2502").Value
ThisWorkbook.Sheets("Generator").Range("o1:o2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("k3:k2502").Value
ThisWorkbook.Sheets("Generator").Range("p1:p2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("l3:l2502").Value
ThisWorkbook.Sheets("Generator").Range("q1:q2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("m3:m2502").Value
ThisWorkbook.Sheets("Generator").Range("r1:r2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("n3:n2502").Value
ThisWorkbook.Sheets("Generator").Range("s1:s2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("o3:o2502").Value
ActiveWorkbook.Close
Nun habe ich eine, aus meiner Sicht, simple If-Funktion eingebaut:
If ActiveWorkbook.Sheets("Tabelle1").Range("L2").Value = "Leistungsbereichs-Nr." Or " _
Leistungsbereichs-Nummer" Or "Leistungsbereichsnummer" Or "Leistungsbereichsnr." Then
ThisWorkbook.Sheets("Generator").Range("a1:a2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("a3:a2502").Value
ThisWorkbook.Sheets("Generator").Range("b1:b2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("b3:b2502").Value
ThisWorkbook.Sheets("Generator").Range("c1:c2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("c3:c2502").Value
ThisWorkbook.Sheets("Generator").Range("d1:d2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("d3:d2502").Value
ThisWorkbook.Sheets("Generator").Range("e1:e2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("e3:e2502").Value
ThisWorkbook.Sheets("Generator").Range("j1:j2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("f3:f2502").Value
ThisWorkbook.Sheets("Generator").Range("k1:k2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("g3:g2502").Value
ThisWorkbook.Sheets("Generator").Range("l1:l2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("h3:h2502").Value
ThisWorkbook.Sheets("Generator").Range("m1:m2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("i3:i2502").Value
ThisWorkbook.Sheets("Generator").Range("n1:n2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("j3:j2502").Value
ThisWorkbook.Sheets("Generator").Range("o1:o2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("k3:k2502").Value
ThisWorkbook.Sheets("Generator").Range("p1:p2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("l3:l2502").Value
ThisWorkbook.Sheets("Generator").Range("q1:q2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("m3:m2502").Value
ThisWorkbook.Sheets("Generator").Range("r1:r2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("n3:n2502").Value
ThisWorkbook.Sheets("Generator").Range("s1:s2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("o3:o2502").Value
Else
ThisWorkbook.Sheets("Generator").Range("a1:a2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("a3:a2502").Value
ThisWorkbook.Sheets("Generator").Range("b1:b2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("b3:b2502").Value
ThisWorkbook.Sheets("Generator").Range("c1:c2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("c3:c2502").Value
ThisWorkbook.Sheets("Generator").Range("d1:d2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("d3:d2502").Value
ThisWorkbook.Sheets("Generator").Range("e1:e2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("e3:e2502").Value
ThisWorkbook.Sheets("Generator").Range("j1:j2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("f3:f2502").Value
ThisWorkbook.Sheets("Generator").Range("k1:k2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("g3:g2502").Value
ThisWorkbook.Sheets("Generator").Range("l1:l2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("h3:h2502").Value
ThisWorkbook.Sheets("Generator").Range("m1:m2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("i3:i2502").Value
ThisWorkbook.Sheets("Generator").Range("n1:n2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("j3:j2502").Value
ThisWorkbook.Sheets("Generator").Range("o1:o2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("k3:k2502").Value
ThisWorkbook.Sheets("Generator").Range("q1:q2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("l3:l2502").Value
ThisWorkbook.Sheets("Generator").Range("r1:r2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("m3:m2502").Value
ThisWorkbook.Sheets("Generator").Range("s1:s2500").Value = ActiveWorkbook.Sheets("Tabelle1"). _
Range("n3:n2502").Value
End If
ActiveWorkbook.Close
Leider wird mir nun immer beim Starten des Makro der "Laufzeitfehler '13': Typen unverträglich" angezeigt. Hat jemand zufällig 'ne Ahnung woran das liegen könnte? As der Excel-Hilfe werde ich leider nicht schlau.
Beste Grüße
Gordon