Hallo Community
und Danke Rene für die Antwort.
Habe in der Zwischenzeit selbst mal einen Code geschrieben. Der funktioniert ebenfalls.
Ich glaube mein Problem war einfach zu vage formuliert als das letztendlich der richtige Code geliefert werden konnte.
Trotzdem bin ich sehr dankebar für deine Mühe und ich lerne auch aus deiner Arbeit weiter.
Falls es Jemanden interessiert mein Code:
Public variable As Integer
Sub makro2()
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
variable = variable + 1
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = variable
End Sub
Sub makro3()
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
variable = variable - 1
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = variable
End Sub
Sub makro4()
On Error GoTo Fehler
If Sheets("Overwatch").Range("B2").Value = "" Or Sheets("Overwatch").Range("B2").Value = " " Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("V2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("t2").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("T2").FormulaLocal
variable = ThisWorkbook.Worksheets("Overwatch").Range("T2").Value
Exit Sub
Else
GoTo Next0
Next0:
If Sheets("Overwatch").Range("B2").Value = "Boiler" Or Sheets("Overwatch").Range("B2").Value = "boiler" Then
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 1
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next1
Next1:
If Sheets("Overwatch").Range("B2").Value = "Chem" Or Sheets("Overwatch").Range("B2").Value = "chem" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 23
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next2
Next2:
If Sheets("Overwatch").Range("B2").Value = "Civil" Or Sheets("Overwatch").Range("B2").Value = "civil" Or Sheets("Overwatch").Range("B2").Value = "civi" Or Sheets("Overwatch").Range("B2").Value = "civil" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 39
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next3
Next3:
If Sheets("Overwatch").Range("B2").Value = "Coal" Or Sheets("Overwatch").Range("B2").Value = "coal" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 49
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next4
Next4:
If Sheets("Overwatch").Range("B2").Value = "Corr" Or Sheets("Overwatch").Range("B2").Value = "corr" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 66
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next5
Next5:
If Sheets("Overwatch").Range("B2").Value = "ExpO" Or Sheets("Overwatch").Range("B2").Value = "expo" Or Sheets("Overwatch").Range("B2").Value = "Expo" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 81
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next6
Next6:
If Sheets("Overwatch").Range("B2").Value = "FM" Or Sheets("Overwatch").Range("B2").Value = "fm" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 86
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next7
Next7:
If Sheets("Overwatch").Range("B2").Value = "GRE" Or Sheets("Overwatch").Range("B2").Value = "gre" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 109
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Else
GoTo Next8
Next8:
If Sheets("Overwatch").Range("B2").Value = "I&C" Or Sheets("Overwatch").Range("B2").Value = "Chem" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 131
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next9
Next9:
If Sheets("Overwatch").Range("B2").Value = "IAC" Or Sheets("Overwatch").Range("B2").Value = "iac" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 153
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next10
Next10:
If Sheets("Overwatch").Range("B2").Value = "LUVO" Or Sheets("Overwatch").Range("B2").Value = "luvo" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 171
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next11
Next11:
If Sheets("Overwatch").Range("B2").Value = "Mills" Or Sheets("Overwatch").Range("B2").Value = "mills" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 175
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next12
Next12:
If Sheets("Overwatch").Range("B2").Value = "MRO" Or Sheets("Overwatch").Range("B2").Value = "mro" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 183
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next13
Next13:
If Sheets("Overwatch").Range("B2").Value = "MTL" Or Sheets("Overwatch").Range("B2").Value = "mtl" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 206
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next14
Next14:
If Sheets("Overwatch").Range("B2").Value = "Nuc" Or Sheets("Overwatch").Range("B2").Value = "nuc" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 234
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next15
Next15:
If Sheets("Overwatch").Range("B2").Value = "OCR" Or Sheets("Overwatch").Range("B2").Value = "ocr" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 237
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next16
Next16:
If Sheets("Overwatch").Range("B2").Value = "Path" Or Sheets("Overwatch").Range("B2").Value = "path" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 258
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Else: GoTo Next17
Next17:
If Sheets("Overwatch").Range("B2").Value = "Pipe" Or Sheets("Overwatch").Range("B2").Value = "pipe" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 262
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next18
Next18:
If Sheets("Overwatch").Range("B2").Value = "Prec" Or Sheets("Overwatch").Range("B2").Value = "prec" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 290
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Else
GoTo Next19
Next19:
If Sheets("Overwatch").Range("B2").Value = "Pump" Or Sheets("Overwatch").Range("B2").Value = "pump" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 298
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next20
Next20:
If Sheets("Overwatch").Range("B2").Value = "Scaff" Or Sheets("Overwatch").Range("B2").Value = "scaff" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 304
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next21
Next21:
If Sheets("Overwatch").Range("B2").Value = "Semi" Or Sheets("Overwatch").Range("B2").Value = "semi" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 330
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next22
Next22:
If Sheets("Overwatch").Range("B2").Value = "Temp" Or Sheets("Overwatch").Range("B2").Value = "temp" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 344
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else:
GoTo Next23
Next23:
If Sheets("Overwatch").Range("B2").Value = "TSM" Or Sheets("Overwatch").Range("B2").Value = "tsm" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 358
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
Else
GoTo Next24
Next24:
If Sheets("Overwatch").Range("B2").Value = "Val" Or Sheets("Overwatch").Range("B2").Value = "val" Then
ThisWorkbook.Worksheets("Overwatch").Range("A5").FormulaLocal = ThisWorkbook.Worksheets("Overwatch").Range("U2").FormulaLocal
ThisWorkbook.Worksheets("Overwatch").Range("S2").Value = 380
variable = ThisWorkbook.Worksheets("Overwatch").Range("S2").Value
Exit Sub
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Fehler:
MsgBox "Bitte geben sie eine Tracker-ID ein!", vbExclamation, "Fehler!"
Exit Sub
End Sub
Dieser Code ermöglicht es mir nach Kategorien zu suchen bspw.: Civi ≠ civi ≠ civil dann per Tastendruck in den Trackernummern auf und ab zu navigieren, dies funktioniert auch wenn man eine absolute Adressierung sucht, bspw.: Civi08 außerdem wird ein Fehler ausgegeben, der den Anwender auffordert eine richtige ID einzugebn.
Darüber hinaus habe cih die Formeln die durch das Makro kopiert werden in die Tabelle mit eingearbeitet, falls neue Spalten hinzugefügt werden, werden die Zellkoordinaten adaptiert.
Vielen Dank nochmal für die rasche Resonanz.
Ich werde das Forum weiter empfehlen!
VG Mika
|