先日質問させていただいたのですが図を添付した方が分かり易いとのご指摘で再度投稿させていただきます。
下にデータが入っていきます。
添付では176個（LOT )のデータが17行目以降に入っています。
D４セルにはIFERROR（・・・・）を入れてみたら空白で表示されました。（当然？）
通常は担当者がセルにデータを入れると計算結果が（クルリと言う感じで）表示されるので実用上は問題無いのですが、最近このデータを要求されることが増えてきて、その時には最下行のセル（でなくてもどこでも）何かするとその列が計算されます。
ちなみにキャプチャー後に項目セル(D５）を削除してみても値が表示されました。（よって開けたまままの表です）
また、メニューの「数式」「再計算の実行」をいじってみましたが変化なし。（エラー表示のままでした）
コードは下記の通りです。
表にはAverage以外にMIN、MAX、STDEVをそれぞれ標準モジュールに同じ形式（コード）で仕込んであります。
Function sfAverage(Rng As Range, Optional bd) As Double
Dim LastRow As Long
Dim MyCol As Long
Dim tgRng As Range
Dim Border As Long
Dim StartRow As Long
Const DefBorder = 30
StartRow = 17 'データ開始行
If IsMissing(bd) Then
Border = DefBorder '省略された場合の閾値
Else
If ((bd = 0) Or (bd = "")) Then
Border = DefBorder '省略された場合の閾値
Else
Border = bd
End If
End If
MyCol = Rng.Column
LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
If LastRow <= StartRow Then
LastRow = StartRow
End If
'下から上方向に、数値となっているセルを探す
Do
If IsNumeric(Cells(LastRow, MyCol).Value) = True Then Exit Do
If LastRow <= StartRow Then Exit Do
LastRow = LastRow - 1
Loop
If LastRow > StartRow + Border - 1 Then
LastRow = LastRow - 1
StartRow = LastRow - Border + 1
End If
Debug.Print _
"開始行: " & StartRow & _
" / 終了行: " & LastRow & _
" / 対象列番号: " & MyCol
Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
sfAverage = WorksheetFunction.Average(tgRng)
End Function
先日質問させていただいたのですが図を添付した方が分かり易いとのご指摘で再度投稿させていただきます。
下にデータが入っていきます。
添付では176個（LOT )のデータが17行目以降に入っています。
D４セルにはIFERROR（・・・・）を入れてみたら空白で表示されました。（当然？）
通常は担当者がセルにデータを入れると計算結果が（クルリと言う感じで）表示されるので実用上は問題無いのですが、最近このデータを要求されることが増えてきて、その時には最下行のセル（でなくてもどこでも）何かするとその列が計算されます。
ちなみにキャプチャー後に項目セル(D５）を削除してみても値が表示されました。（よって開けたまままの表です）
また、メニューの「数式」「再計算の実行」をいじってみましたが変化なし。（エラー表示のままでした）
コードは下記の通りです。
表にはAverage以外にMIN、MAX、STDEVをそれぞれ標準モジュールに同じ形式（コード）で仕込んであります。
Function sfAverage(Rng As Range, Optional bd) As Double
Dim LastRow As Long
Dim MyCol As Long
Dim tgRng As Range
Dim Border As Long
Dim StartRow As Long
Const DefBorder = 30
StartRow = 17 'データ開始行
If IsMissing(bd) Then
Border = DefBorder '省略された場合の閾値
Else
If ((bd = 0) Or (bd = "")) Then
Border = DefBorder '省略された場合の閾値
Else
Border = bd
End If
End If
MyCol = Rng.Column
LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
If LastRow <= StartRow Then
LastRow = StartRow
End If
'下から上方向に、数値となっているセルを探す
Do
If IsNumeric(Cells(LastRow, MyCol).Value) = True Then Exit Do
If LastRow <= StartRow Then Exit Do
LastRow = LastRow - 1
Loop
If LastRow > StartRow + Border - 1 Then
LastRow = LastRow - 1
StartRow = LastRow - Border + 1
End If
Debug.Print _
"開始行: " & StartRow & _
" / 終了行: " & LastRow & _
" / 対象列番号: " & MyCol
Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
sfAverage = WorksheetFunction.Average(tgRng)
End Function
先日質問させていただいたのですが図を添付した方が分かり易いとのご指摘で再度投稿させていただきます。
下にデータが入っていきます。
添付では176個（LOT )のデータが17行目以降に入っています。
D４セルにはIFERROR（・・・・）を入れてみたら空白で表示されました。（当然？）
通常は担当者がセルにデータを入れると計算結果が（クルリと言う感じで）表示されるので実用上は問題無いのですが、最近このデータを要求されることが増えてきて、その時には最下行のセル（でなくてもどこでも）何かするとその列が計算されます。
ちなみにキャプチャー後に項目セル(D５）を削除してみても値が表示されました。（よって開けたまままの表です）
また、メニューの「数式」「再計算の実行」をいじってみましたが変化なし。（エラー表示のままでした）
コードは下記の通りです。
表にはAverage以外にMIN、MAX、STDEVをそれぞれ標準モジュールに同じ形式（コード）で仕込んであります。
Function sfAverage(Rng As Range, Optional bd) As Double
Dim LastRow As Long
Dim MyCol As Long
Dim tgRng As Range
Dim Border As Long
Dim StartRow As Long
Const DefBorder = 30
StartRow = 17 'データ開始行
If IsMissing(bd) Then
Border = DefBorder '省略された場合の閾値
Else
If ((bd = 0) Or (bd = "")) Then
Border = DefBorder '省略された場合の閾値
Else
Border = bd
End If
End If
MyCol = Rng.Column
LastRow = Cells(Rows.Count, MyCol).End(xlUp).Row
If LastRow <= StartRow Then
LastRow = StartRow
End If
'下から上方向に、数値となっているセルを探す
Do
If IsNumeric(Cells(LastRow, MyCol).Value) = True Then Exit Do
If LastRow <= StartRow Then Exit Do
LastRow = LastRow - 1
Loop
If LastRow > StartRow + Border - 1 Then
LastRow = LastRow - 1
StartRow = LastRow - Border + 1
End If
Debug.Print _
"開始行: " & StartRow & _
" / 終了行: " & LastRow & _
" / 対象列番号: " & MyCol
Set tgRng = Range(Cells(StartRow, MyCol), Cells(LastRow, MyCol))
sfAverage = WorksheetFunction.Average(tgRng)
End Function
