| |
▼名無しの権兵衛 さん：
こんな感じでしょうか。
Function func(集計項目 As Range) As Double
Dim f As Range
Dim r As Range
Dim c As Range
Application.Volatile
With Range(集計項目, 集計項目.End(xlDown))
Set f = .Find(集計項目.Value, 集計項目, , xlWhole)
If f.Row = 集計項目.Row Then
Set r = Range(集計項目.Offset(1), .Cells(.Count))
Else
Set r = Range(集計項目.Offset(1), f.Offset(-1))
End If
End With
For Each c In r.Offset(, Application.Caller.Column - 集計項目.Column)
If Not c.HasFormula Then
func = func + c.Value
End If
Next
End Function
でも、func2がよいと思います。
Function func2(集計項目 As Range, データ範囲 As Range, 列番号 As Long) As Double
Dim f As Range
Dim r As Range
Dim c As Range
With データ範囲.Columns(1).Cells
Set f = .Find(集計項目.Value, 集計項目, , xlWhole)
If f.Row <= 集計項目.Row Then
Set r = Range(集計項目.Offset(1), .Cells(.Count))
Else
Set r = Range(集計項目.Offset(1), f.Offset(-1))
End If
End With
For Each c In r.Columns(列番号).Cells
If Not c.HasFormula Then
func2 = func2 + c.Value
End If
Next
End Function
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
