AW: Button
15.02.2007 10:22:45
fcs
Hallo Chantal,
die Prozedur müsste etwa so aussehen.
Gruß
Franz
Sub Test()
Dim Zelle As Range
Dim B As String
Dim i As Integer
Dim S As Long
Dim Summe As Long
Dim leftCol As Long, tarCol As Long
Set Zelle = ActiveCell
Select Case Zelle.Address(0, 0)
Case "A10", "A11", "A12", "A13", "A14", "A15", "A16", "A17", "A18"
With ActiveSheet
For i = 1 To Len(.Cells(Zelle.Row, 1))
B = Mid(.Cells(Zelle.Row, 1), i, 1)
For S = 1 To 50
If UCase(B) = UCase(.Cells(2, S)) Then
Summe = Summe + .Cells(3, S)
Exit For
End If
Next S
For S = 1 To 11
If UCase(B) = UCase(.Cells(5, S)) Then Summe = Summe + .Cells(6, S)
Next S
Next i
leftCol = .Cells(Zelle.Row, 254).End(xlToLeft).Column
'Definiere hier ab welcher Spalte geschrieben werden soll
If leftCol < 5 Then
tarCol = 2
Else
tarCol = leftCol - 3
End If
'... oder hier ab Spalte 2 das Ergebnis schreiben
If leftCol < 2 Then
' tarCol = 2
Else
tarCol = leftCol + 1
End If
.Cells(Zelle.Row, tarCol) = Summe
End With
Case Else
MsgBox "Makro rechnet nur wenn eine der Zellen " & vbLf _
& "A10, A11, A12, A13, A14, A15, A16, A17 oder A18 gewählt ist"
End Select
End Sub