Statt Formeln VBA ?

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("BF35766")) Is Nothing And Target.Count = 1 Then Select Case (Target) Case Is = "N" Cells(2, 2) = "nein" Case Is = "j-xPV" Cells(2, 2) = "ja" Case Is = "CHA/xPV", "EV-N / FV-XPV", "I-J/N" Cells(2, 2) = "eventuell" End Select End If End Sub

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("BF35766")) Is Nothing And Target.Count = 1 Then Select Case (Target) Case Is = "N" Cells(2, 75) = "nein" Case Is = "j-xPV" Cells(2, 75) = "ja" Case Is = "CHA/xPV", "EV-N / FV-XPV", "I-J/N" Cells(2, 75) = "eventuell" End Select End If End Sub

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns(58)) Is Nothing And Target.Count = 1 Then Select Case (Target) Case Is = "N" Target.Offset(, 17) = "nein" Case Is = "j-xPV" Target.Offset(, 17) = "ja" Case Is = "CHA/xPV", "EV-N / FV-XPV", "I-J/N" Target.Offset(, 17) = "eventuell" End Select End If End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim arrDaten If Target.Count > 1 Then Exit Sub If Intersect(Target, Columns("BF")) Is Nothing Then Exit Sub Application.EnableEvents = False arrDaten = Array(Array("N", "J-xPV", "XHS/xPV", "EV-N/xPV", "I-J/N"), Array("Nein", "Ja", " _ eventuell", "eventuell", "eventuell")) If Not IsError(Application.Match(Target, arrDaten(0), 0)) Then Target.Offset(0, -9) = arrDaten(1)(Application.Match(Target, arrDaten(0), 0) - 1) Else MsgBox "Suchbegriff nicht vorhanden" End If Application.EnableEvents = True End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim arrDaten If Target.Count > 1 Then Exit Sub If Intersect(Target, Columns("BF")) Is Nothing Then Exit Sub Application.EnableEvents = False arrDaten = Array(Array("N", "J-xPV", "XHS/xPV", "EV-N/xPV", "I-J/N"), Array("Nein", "Ja", " _ _ eventuell", "eventuell", "eventuell")) If Not IsError(Application.Match(Target, arrDaten(0), 0)) Then Target.Offset(0, 17) = arrDaten(1)(Application.Match(Target, arrDaten(0), 0) - 1) Else MsgBox "Suchbegriff nicht vorhanden" End If Application.EnableEvents = True End Sub

Hi Josef,
in deinem Eröffnungsbeitrag stand, dass der Wert in Spalte AW eingetragen werden soll. In diesem Fall ist Target.Offset(0, -9) richtig gewesen.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrDaten
Dim raZelle As Range
If Intersect(Target, Columns("BF")) Is Nothing Then Exit Sub
Application.EnableEvents = False
arrDaten = Array(Array("N", "J-xPV", "XHS/xPV", "EV-N/xPV", "I-J/N"), Array("Nein", "Ja", " _
eventuell", "eventuell", "eventuell"))
If Target.Count > 1 Then
If Not IsError(Application.Match(Selection.Cells(1), arrDaten(0), 0)) And Selection.Cells( _
1)  "" Then Selection.Offset(0, 17) = arrDaten(1)(Application.Match(Selection.Cells(1), arrDaten(0), 0) - 1)
Else
If Not IsError(Application.Match(Target, arrDaten(0), 0)) And Target  "" Then Target. _
Offset(0, 17) = arrDaten(1)(Application.Match(Target, arrDaten(0), 0) - 1)
End If
Application.EnableEvents = True
End Sub

Beliebteste Forumthreads (12 Monate)

12 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread