Sort mit ActiveCell, die nicht in Range liegt
05.02.2004 11:14:42
Sandra
Zunächst zur Erklärung meine Situation:
Ich definiere mit folgendem Makro die Werte einer ComboBox:
Sub UserForm1Zeigen()
For i = 412 To 514
If Sheets("Statist").Cells(i, 59).Value <> "" Then
UserForm1.ComboBox1.AddItem (Sheets("Statist").Cells(i, 59).Value)
End If
Next i
UserForm1.Show
End Sub
Jetzt definiere ich mit folgendem Makro den Inhalt der TextBox1:
Private Sub ComboBox1_Change()
TextBox1.Value = ComboBox1.Text
End Sub
Dann hole mir mit folgendem Makro die Daten in die UserForm:
Private Sub CommandButton6_Click()
Application.Run "Makro1"
Selection.Find(What:=UserForm1.TextBox1.Value, _
After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
UserForm1.TextBox1.Value = ActiveCell.Value
UserForm1.TextBox2.Value = ActiveCell.Offset(0, 2).Value & ". Spieltag"
UserForm1.TextBox3.Value = ActiveCell.Offset(0, 4).Value
Application.Run "Makro2"
Exit Sub
End Sub
Wobei Makro2 so aussieht:
Sub Makro2()
UserForm1.Name1.Value = Sheets("Statist").Range("H5").Value
UserForm1.Name2.Value = Sheets("Statist").Range("I5").Value
UserForm1.Name3.Value = Sheets("Statist").Range("J5").Value
UserForm1.Name4.Value = Sheets("Statist").Range("K5").Value
UserForm1.Name5.Value = Sheets("Statist").Range("L5").Value
UserForm1.Name6.Value = Sheets("Statist").Range("M5").Value
UserForm1.Name7.Value = Sheets("Statist").Range("N5").Value
UserForm1.Name8.Value = Sheets("Statist").Range("O5").Value
UserForm1.Name9.Value = Sheets("Statist").Range("P5").Value
UserForm1.Name10.Value = Sheets("Statist").Range("Q5").Value
UserForm1.Name11.Value = Sheets("Statist").Range("R5").Value
UserForm1.Name12.Value = Sheets("Statist").Range("S5").Value
UserForm1.Name13.Value = Sheets("Statist").Range("T5").Value
UserForm1.Name14.Value = Sheets("Statist").Range("U5").Value
UserForm1.Name15.Value = Sheets("Statist").Range("V5").Value
UserForm1.Name16.Value = Sheets("Statist").Range("W5").Value
UserForm1.Name17.Value = Sheets("Statist").Range("X5").Value
UserForm1.Name18.Value = Sheets("Statist").Range("Y5").Value
UserForm1.Name19.Value = Sheets("Statist").Range("Z5").Value
UserForm1.Name20.Value = Sheets("Statist").Range("AA5").Value
UserForm1.Name21.Value = Sheets("Statist").Range("AB5").Value
UserForm1.Name22.Value = Sheets("Statist").Range("AC5").Value
UserForm1.Name23.Value = Sheets("Statist").Range("AD5").Value
UserForm1.Name24.Value = Sheets("Statist").Range("AE5").Value
UserForm1.Name25.Value = Sheets("Statist").Range("AF5").Value
UserForm1.Name26.Value = Sheets("Statist").Range("AG5").Value
UserForm1.Name27.Value = Sheets("Statist").Range("AH5").Value
UserForm1.Name28.Value = Sheets("Statist").Range("AI5").Value
UserForm1.Name29.Value = Sheets("Statist").Range("AJ5").Value
UserForm1.Name30.Value = Sheets("Statist").Range("AK5").Value
UserForm1.B01.Value = Sheets("Statist").Range("H6").Value
UserForm1.B02.Value = Sheets("Statist").Range("I6").Value
UserForm1.B03.Value = Sheets("Statist").Range("J6").Value
UserForm1.B04.Value = Sheets("Statist").Range("K6").Value
UserForm1.B05.Value = Sheets("Statist").Range("L6").Value
UserForm1.B06.Value = Sheets("Statist").Range("M6").Value
UserForm1.B07.Value = Sheets("Statist").Range("N6").Value
UserForm1.B08.Value = Sheets("Statist").Range("O6").Value
UserForm1.B09.Value = Sheets("Statist").Range("P6").Value
UserForm1.B10.Value = Sheets("Statist").Range("Q6").Value
UserForm1.B11.Value = Sheets("Statist").Range("R6").Value
UserForm1.B12.Value = Sheets("Statist").Range("S6").Value
UserForm1.B13.Value = Sheets("Statist").Range("T6").Value
UserForm1.B14.Value = Sheets("Statist").Range("U6").Value
UserForm1.B15.Value = Sheets("Statist").Range("V6").Value
UserForm1.B16.Value = Sheets("Statist").Range("W6").Value
UserForm1.B17.Value = Sheets("Statist").Range("X6").Value
UserForm1.B18.Value = Sheets("Statist").Range("Y6").Value
UserForm1.B19.Value = Sheets("Statist").Range("Z6").Value
UserForm1.B20.Value = Sheets("Statist").Range("AA6").Value
UserForm1.B21.Value = Sheets("Statist").Range("AB6").Value
UserForm1.B22.Value = Sheets("Statist").Range("AC6").Value
UserForm1.B23.Value = Sheets("Statist").Range("AD6").Value
UserForm1.B24.Value = Sheets("Statist").Range("AE6").Value
UserForm1.B25.Value = Sheets("Statist").Range("AF6").Value
UserForm1.B26.Value = Sheets("Statist").Range("AG6").Value
UserForm1.B27.Value = Sheets("Statist").Range("AH6").Value
UserForm1.B28.Value = Sheets("Statist").Range("AI6").Value
UserForm1.B29.Value = Sheets("Statist").Range("AJ6").Value
UserForm1.B30.Value = Sheets("Statist").Range("AK6").Value
UserForm1.Liste1001.Value = ActiveCell.Offset(-392, -51).Value
UserForm1.Liste1002.Value = ActiveCell.Offset(-392, -50).Value
UserForm1.Liste1003.Value = ActiveCell.Offset(-392, -49).Value
UserForm1.Liste1004.Value = ActiveCell.Offset(-392, -48).Value
UserForm1.Liste1005.Value = ActiveCell.Offset(-392, -47).Value
UserForm1.Liste1006.Value = ActiveCell.Offset(-392, -46).Value
UserForm1.Liste1007.Value = ActiveCell.Offset(-392, -45).Value
UserForm1.Liste1008.Value = ActiveCell.Offset(-392, -44).Value
UserForm1.Liste1009.Value = ActiveCell.Offset(-392, -43).Value
UserForm1.Liste1010.Value = ActiveCell.Offset(-392, -42).Value
UserForm1.Liste1011.Value = ActiveCell.Offset(-392, -41).Value
UserForm1.Liste1012.Value = ActiveCell.Offset(-392, -40).Value
UserForm1.Liste1013.Value = ActiveCell.Offset(-392, -39).Value
UserForm1.Liste1014.Value = ActiveCell.Offset(-392, -38).Value
UserForm1.Liste1015.Value = ActiveCell.Offset(-392, -37).Value
UserForm1.Liste1016.Value = ActiveCell.Offset(-392, -36).Value
UserForm1.Liste1017.Value = ActiveCell.Offset(-392, -35).Value
UserForm1.Liste1018.Value = ActiveCell.Offset(-392, -34).Value
UserForm1.Liste1019.Value = ActiveCell.Offset(-392, -33).Value
UserForm1.Liste1020.Value = ActiveCell.Offset(-392, -32).Value
UserForm1.Liste1021.Value = ActiveCell.Offset(-392, -31).Value
UserForm1.Liste1022.Value = ActiveCell.Offset(-392, -30).Value
UserForm1.Liste1023.Value = ActiveCell.Offset(-392, -29).Value
UserForm1.Liste1024.Value = ActiveCell.Offset(-392, -28).Value
UserForm1.Liste1025.Value = ActiveCell.Offset(-392, -27).Value
UserForm1.Liste1026.Value = ActiveCell.Offset(-392, -26).Value
UserForm1.Liste1027.Value = ActiveCell.Offset(-392, -25).Value
UserForm1.Liste1028.Value = ActiveCell.Offset(-392, -24).Value
UserForm1.Liste1029.Value = ActiveCell.Offset(-392, -23).Value
UserForm1.Liste1030.Value = ActiveCell.Offset(-392, -22).Value
UserForm1.Liste2001.Value = ActiveCell.Offset(-220, -51).Value
UserForm1.Liste2002.Value = ActiveCell.Offset(-220, -50).Value
UserForm1.Liste2003.Value = ActiveCell.Offset(-220, -49).Value
UserForm1.Liste2004.Value = ActiveCell.Offset(-220, -48).Value
UserForm1.Liste2005.Value = ActiveCell.Offset(-220, -47).Value
UserForm1.Liste2006.Value = ActiveCell.Offset(-220, -46).Value
UserForm1.Liste2007.Value = ActiveCell.Offset(-220, -45).Value
UserForm1.Liste2008.Value = ActiveCell.Offset(-220, -44).Value
UserForm1.Liste2009.Value = ActiveCell.Offset(-220, -43).Value
UserForm1.Liste2010.Value = ActiveCell.Offset(-220, -42).Value
UserForm1.Liste2011.Value = ActiveCell.Offset(-220, -41).Value
UserForm1.Liste2012.Value = ActiveCell.Offset(-220, -40).Value
UserForm1.Liste2013.Value = ActiveCell.Offset(-220, -39).Value
UserForm1.Liste2014.Value = ActiveCell.Offset(-220, -38).Value
UserForm1.Liste2015.Value = ActiveCell.Offset(-220, -37).Value
UserForm1.Liste2016.Value = ActiveCell.Offset(-220, -36).Value
UserForm1.Liste2017.Value = ActiveCell.Offset(-220, -35).Value
UserForm1.Liste2018.Value = ActiveCell.Offset(-220, -34).Value
UserForm1.Liste2019.Value = ActiveCell.Offset(-220, -33).Value
UserForm1.Liste2020.Value = ActiveCell.Offset(-220, -32).Value
UserForm1.Liste2021.Value = ActiveCell.Offset(-220, -31).Value
UserForm1.Liste2022.Value = ActiveCell.Offset(-220, -30).Value
UserForm1.Liste2023.Value = ActiveCell.Offset(-220, -29).Value
UserForm1.Liste2024.Value = ActiveCell.Offset(-220, -28).Value
UserForm1.Liste2025.Value = ActiveCell.Offset(-220, -27).Value
UserForm1.Liste2026.Value = ActiveCell.Offset(-220, -26).Value
UserForm1.Liste2027.Value = ActiveCell.Offset(-220, -25).Value
UserForm1.Liste2028.Value = ActiveCell.Offset(-220, -24).Value
UserForm1.Liste2029.Value = ActiveCell.Offset(-220, -23).Value
UserForm1.Liste2030.Value = ActiveCell.Offset(-220, -22).Value
UserForm1.Liste3001.Value = ActiveCell.Offset(-113, -51).Value
UserForm1.Liste3002.Value = ActiveCell.Offset(-113, -50).Value
UserForm1.Liste3003.Value = ActiveCell.Offset(-113, -49).Value
UserForm1.Liste3004.Value = ActiveCell.Offset(-113, -48).Value
UserForm1.Liste3005.Value = ActiveCell.Offset(-113, -47).Value
UserForm1.Liste3006.Value = ActiveCell.Offset(-113, -46).Value
UserForm1.Liste3007.Value = ActiveCell.Offset(-113, -45).Value
UserForm1.Liste3008.Value = ActiveCell.Offset(-113, -44).Value
UserForm1.Liste3009.Value = ActiveCell.Offset(-113, -43).Value
UserForm1.Liste3010.Value = ActiveCell.Offset(-113, -42).Value
UserForm1.Liste3011.Value = ActiveCell.Offset(-113, -41).Value
UserForm1.Liste3012.Value = ActiveCell.Offset(-113, -40).Value
UserForm1.Liste3013.Value = ActiveCell.Offset(-113, -39).Value
UserForm1.Liste3014.Value = ActiveCell.Offset(-113, -38).Value
UserForm1.Liste3015.Value = ActiveCell.Offset(-113, -37).Value
UserForm1.Liste3016.Value = ActiveCell.Offset(-113, -36).Value
UserForm1.Liste3017.Value = ActiveCell.Offset(-113, -35).Value
UserForm1.Liste3018.Value = ActiveCell.Offset(-113, -34).Value
UserForm1.Liste3019.Value = ActiveCell.Offset(-113, -33).Value
UserForm1.Liste3020.Value = ActiveCell.Offset(-113, -32).Value
UserForm1.Liste3021.Value = ActiveCell.Offset(-113, -31).Value
UserForm1.Liste3022.Value = ActiveCell.Offset(-113, -30).Value
UserForm1.Liste3023.Value = ActiveCell.Offset(-113, -29).Value
UserForm1.Liste3024.Value = ActiveCell.Offset(-113, -28).Value
UserForm1.Liste3025.Value = ActiveCell.Offset(-113, -27).Value
UserForm1.Liste3026.Value = ActiveCell.Offset(-113, -26).Value
UserForm1.Liste3027.Value = ActiveCell.Offset(-113, -25).Value
UserForm1.Liste3028.Value = ActiveCell.Offset(-113, -24).Value
UserForm1.Liste3029.Value = ActiveCell.Offset(-113, -23).Value
UserForm1.Liste3030.Value = ActiveCell.Offset(-113, -22).Value
UserForm1.Liste4001.Value = ActiveCell.Offset(-391, -51).Value
UserForm1.Liste4002.Value = ActiveCell.Offset(-391, -50).Value
UserForm1.Liste4003.Value = ActiveCell.Offset(-391, -49).Value
UserForm1.Liste4004.Value = ActiveCell.Offset(-391, -48).Value
UserForm1.Liste4005.Value = ActiveCell.Offset(-391, -47).Value
UserForm1.Liste4006.Value = ActiveCell.Offset(-391, -46).Value
UserForm1.Liste4007.Value = ActiveCell.Offset(-391, -45).Value
UserForm1.Liste4008.Value = ActiveCell.Offset(-391, -44).Value
UserForm1.Liste4009.Value = ActiveCell.Offset(-391, -43).Value
UserForm1.Liste4010.Value = ActiveCell.Offset(-391, -42).Value
UserForm1.Liste4011.Value = ActiveCell.Offset(-391, -41).Value
UserForm1.Liste4012.Value = ActiveCell.Offset(-391, -40).Value
UserForm1.Liste4013.Value = ActiveCell.Offset(-391, -39).Value
UserForm1.Liste4014.Value = ActiveCell.Offset(-391, -38).Value
UserForm1.Liste4015.Value = ActiveCell.Offset(-391, -37).Value
UserForm1.Liste4016.Value = ActiveCell.Offset(-391, -36).Value
UserForm1.Liste4017.Value = ActiveCell.Offset(-391, -35).Value
UserForm1.Liste4018.Value = ActiveCell.Offset(-391, -34).Value
UserForm1.Liste4019.Value = ActiveCell.Offset(-391, -33).Value
UserForm1.Liste4020.Value = ActiveCell.Offset(-391, -32).Value
UserForm1.Liste4021.Value = ActiveCell.Offset(-391, -31).Value
UserForm1.Liste4022.Value = ActiveCell.Offset(-391, -30).Value
UserForm1.Liste4023.Value = ActiveCell.Offset(-391, -29).Value
UserForm1.Liste4024.Value = ActiveCell.Offset(-391, -28).Value
UserForm1.Liste4025.Value = ActiveCell.Offset(-391, -27).Value
UserForm1.Liste4026.Value = ActiveCell.Offset(-391, -26).Value
UserForm1.Liste4027.Value = ActiveCell.Offset(-391, -25).Value
UserForm1.Liste4028.Value = ActiveCell.Offset(-391, -24).Value
UserForm1.Liste4029.Value = ActiveCell.Offset(-391, -23).Value
UserForm1.Liste4030.Value = ActiveCell.Offset(-391, -22).Value
UserForm1.Liste5001.Value = ActiveCell.Offset(-219, -51).Value
UserForm1.Liste5002.Value = ActiveCell.Offset(-219, -50).Value
UserForm1.Liste5003.Value = ActiveCell.Offset(-219, -49).Value
UserForm1.Liste5004.Value = ActiveCell.Offset(-219, -48).Value
UserForm1.Liste5005.Value = ActiveCell.Offset(-219, -47).Value
UserForm1.Liste5006.Value = ActiveCell.Offset(-219, -46).Value
UserForm1.Liste5007.Value = ActiveCell.Offset(-219, -45).Value
UserForm1.Liste5008.Value = ActiveCell.Offset(-219, -44).Value
UserForm1.Liste5009.Value = ActiveCell.Offset(-219, -43).Value
UserForm1.Liste5010.Value = ActiveCell.Offset(-219, -42).Value
UserForm1.Liste5011.Value = ActiveCell.Offset(-219, -41).Value
UserForm1.Liste5012.Value = ActiveCell.Offset(-219, -40).Value
UserForm1.Liste5013.Value = ActiveCell.Offset(-219, -39).Value
UserForm1.Liste5014.Value = ActiveCell.Offset(-219, -38).Value
UserForm1.Liste5015.Value = ActiveCell.Offset(-219, -37).Value
UserForm1.Liste5016.Value = ActiveCell.Offset(-219, -36).Value
UserForm1.Liste5017.Value = ActiveCell.Offset(-219, -35).Value
UserForm1.Liste5018.Value = ActiveCell.Offset(-219, -34).Value
UserForm1.Liste5019.Value = ActiveCell.Offset(-219, -33).Value
UserForm1.Liste5020.Value = ActiveCell.Offset(-219, -32).Value
UserForm1.Liste5021.Value = ActiveCell.Offset(-219, -31).Value
UserForm1.Liste5022.Value = ActiveCell.Offset(-219, -30).Value
UserForm1.Liste5023.Value = ActiveCell.Offset(-219, -29).Value
UserForm1.Liste5024.Value = ActiveCell.Offset(-219, -28).Value
UserForm1.Liste5025.Value = ActiveCell.Offset(-219, -27).Value
UserForm1.Liste5026.Value = ActiveCell.Offset(-219, -26).Value
UserForm1.Liste5027.Value = ActiveCell.Offset(-219, -25).Value
UserForm1.Liste5028.Value = ActiveCell.Offset(-219, -24).Value
UserForm1.Liste5029.Value = ActiveCell.Offset(-219, -23).Value
UserForm1.Liste5030.Value = ActiveCell.Offset(-219, -22).Value
UserForm1.Liste6001.Value = ActiveCell.Offset(-112, -51).Value
UserForm1.Liste6002.Value = ActiveCell.Offset(-112, -50).Value
UserForm1.Liste6003.Value = ActiveCell.Offset(-112, -49).Value
UserForm1.Liste6004.Value = ActiveCell.Offset(-112, -48).Value
UserForm1.Liste6005.Value = ActiveCell.Offset(-112, -47).Value
UserForm1.Liste6006.Value = ActiveCell.Offset(-112, -46).Value
UserForm1.Liste6007.Value = ActiveCell.Offset(-112, -45).Value
UserForm1.Liste6008.Value = ActiveCell.Offset(-112, -44).Value
UserForm1.Liste6009.Value = ActiveCell.Offset(-112, -43).Value
UserForm1.Liste6010.Value = ActiveCell.Offset(-112, -42).Value
UserForm1.Liste6011.Value = ActiveCell.Offset(-112, -41).Value
UserForm1.Liste6012.Value = ActiveCell.Offset(-112, -40).Value
UserForm1.Liste6013.Value = ActiveCell.Offset(-112, -39).Value
UserForm1.Liste6014.Value = ActiveCell.Offset(-112, -38).Value
UserForm1.Liste6015.Value = ActiveCell.Offset(-112, -37).Value
UserForm1.Liste6016.Value = ActiveCell.Offset(-112, -36).Value
UserForm1.Liste6017.Value = ActiveCell.Offset(-112, -35).Value
UserForm1.Liste6018.Value = ActiveCell.Offset(-112, -34).Value
UserForm1.Liste6019.Value = ActiveCell.Offset(-112, -33).Value
UserForm1.Liste6020.Value = ActiveCell.Offset(-112, -32).Value
UserForm1.Liste6021.Value = ActiveCell.Offset(-112, -31).Value
UserForm1.Liste6022.Value = ActiveCell.Offset(-112, -30).Value
UserForm1.Liste6023.Value = ActiveCell.Offset(-112, -29).Value
UserForm1.Liste6024.Value = ActiveCell.Offset(-112, -28).Value
UserForm1.Liste6025.Value = ActiveCell.Offset(-112, -27).Value
UserForm1.Liste6026.Value = ActiveCell.Offset(-112, -26).Value
UserForm1.Liste6027.Value = ActiveCell.Offset(-112, -25).Value
UserForm1.Liste6028.Value = ActiveCell.Offset(-112, -24).Value
UserForm1.Liste6029.Value = ActiveCell.Offset(-112, -23).Value
UserForm1.Liste6030.Value = ActiveCell.Offset(-112, -22).Value
UserForm1.Gesamt1.Value = ActiveCell.Offset(0, -51).Value
UserForm1.Gesamt2.Value = ActiveCell.Offset(0, -50).Value
UserForm1.Gesamt3.Value = ActiveCell.Offset(0, -49).Value
UserForm1.Gesamt4.Value = ActiveCell.Offset(0, -48).Value
UserForm1.Gesamt5.Value = ActiveCell.Offset(0, -47).Value
UserForm1.Gesamt6.Value = ActiveCell.Offset(0, -46).Value
UserForm1.Gesamt7.Value = ActiveCell.Offset(0, -45).Value
UserForm1.Gesamt8.Value = ActiveCell.Offset(0, -44).Value
UserForm1.Gesamt9.Value = ActiveCell.Offset(0, -43).Value
UserForm1.Gesamt10.Value = ActiveCell.Offset(0, -42).Value
UserForm1.Gesamt11.Value = ActiveCell.Offset(0, -41).Value
UserForm1.Gesamt12.Value = ActiveCell.Offset(0, -40).Value
UserForm1.Gesamt13.Value = ActiveCell.Offset(0, -39).Value
UserForm1.Gesamt14.Value = ActiveCell.Offset(0, -38).Value
UserForm1.Gesamt15.Value = ActiveCell.Offset(0, -37).Value
UserForm1.Gesamt16.Value = ActiveCell.Offset(0, -36).Value
UserForm1.Gesamt17.Value = ActiveCell.Offset(0, -35).Value
UserForm1.Gesamt18.Value = ActiveCell.Offset(0, -34).Value
UserForm1.Gesamt19.Value = ActiveCell.Offset(0, -33).Value
UserForm1.Gesamt20.Value = ActiveCell.Offset(0, -32).Value
UserForm1.Gesamt21.Value = ActiveCell.Offset(0, -31).Value
UserForm1.Gesamt22.Value = ActiveCell.Offset(0, -30).Value
UserForm1.Gesamt23.Value = ActiveCell.Offset(0, -29).Value
UserForm1.Gesamt24.Value = ActiveCell.Offset(0, -28).Value
UserForm1.Gesamt25.Value = ActiveCell.Offset(0, -27).Value
UserForm1.Gesamt26.Value = ActiveCell.Offset(0, -26).Value
UserForm1.Gesamt27.Value = ActiveCell.Offset(0, -25).Value
UserForm1.Gesamt28.Value = ActiveCell.Offset(0, -24).Value
UserForm1.Gesamt29.Value = ActiveCell.Offset(0, -23).Value
UserForm1.Gesamt30.Value = ActiveCell.Offset(0, -22).Value
End Sub
Dazu hab ich jetzt die zwei folgenden Probleme:
1. Wie ich jetzt meinen zu sortierenden Bereich anspreche klappt mit:
Private Sub CommandButton3_Click()
Sheets("Statist").Select
Range("H5:BE514").Select
End Sub
Aber wie spreche ich jetzt mit der AktiveCell (die ja nicht im zu sortierenden Bereich liegt, sondern in Spalte 59 sprich BG liegt) den Key1 bei der Sortierung an?
Sortiert werden soll die Range: Range(ActiveCell.Offset(0, -2), ActiveCell.Offset(0, -51))
2. Wie kann ich meinen Makro2 verkleinern?
Herzliches Dankeschön für Eure Hilfe im voraus
Sandra