AW: Dynamische Summenbildung
25.04.2007 00:33:44
Erich
Hallo Bernd,
die folgende Prozedur ergänzt fehlende Formeln in den "Summe ..."-Zeilen,
wenn in der jeweiligen Zeile mindestens eine Formel steht:
Option Explicit
Sub Add_Summen()
Dim intC As Integer, rngF As Range, lngErst As Long, ii As Integer, jj As Integer
intC = Cells(1, Columns.Count).End(xlToLeft).Column
With Range(Cells(2, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
MsgBox .Address
Set rngF = .Find(What:="Summe *", _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not rngF Is Nothing Then
lngErst = rngF.Row
Do
For ii = 2 To intC
If rngF.Offset(0, ii - 1).HasFormula Then
rngF.Offset(0, ii - 1).Copy
Exit For
End If
Next ii
If ii > intC Then
MsgBox "In der Summenzeile " & rngF.Row & " steht keine Formel."
Else
For jj = 2 To intC
If Not rngF.Offset(0, jj - 1).HasFormula Then _
rngF.Offset(0, jj - 1).PasteSpecial Paste:=xlPasteFormulas, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next jj
End If
Set rngF = .FindNext(rngF)
Loop While Not rngF Is Nothing And rngF.Row lngErst
Application.CutCopyMode = False
End If
End With
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort