エクセル小僧:数式か、否か [コンピューター]
エクセルで数式か否か判定する関数ですが、ワークシート関数とVBAでは名前が違います。
ワークシート関数:ISFORMULA
VBA:HasFormula
例1:条件付き書式にISFORMULA(ワークシート関数)を使用した場合
=AND((TODAY()-$F8)>90, $F8<>"", CELL("format", $F8)="D1", ISFORMULA($D8))
【解説】
・F8セルからF55セルまで、日付が入っている。
・D8セルからD55セルまで、数式が入っている。
・条件1から条件4までの3つの条件全てが成立したとき、セルを赤く塗りつぶす。
・条件1:セル(F列)の日付が、今日から90日前より前であること⇒(TODAY()-$F8)>90
・条件2:セル(F列)が空白でないこと⇒$F8<>""
・条件3:セル(F列)の書式が日付(yyyy/mm/dd)であること⇒CELL("format", $F8)="D1"
・条件4:セル(D列)が数式であること⇒ISFORMULA($D8)
例2:マクロで.HasFormulaを使って数式の有無を判定している場合
Sub HenKigen1(ByVal sheet1 As Worksheet, ByVal Nod1 As Long, ByVal myRange1 As Range)
'【機能】パスワード変更から-Nod1日経過した最初のセルへ飛ぶ
'【引数】
' sheet1 : シート名
' Nod1 : 日数 (Number of Days) 90日前の場合 Nod1=-90
' myRange1 : 検索対象のセル範囲
'【変数】
Dim Day1 As Date ' 基準日
Dim Day2 As Date ' 条件に合致したセルの中で最も古い日付
Dim Range1 As Range ' セル範囲
Dim Range2 As Range ' セル範囲(最も古い日付)
Dim Range_t As Range ' セル範囲(合計)
Dim NoC As Long ' 条件に合致したセル数(Number of Cell)
Dim Nod2 As Long ' Nod1の逆
'【コード】
sheet1.Activate ' 対象シートをアクティブ化
Day1 = Date ' 今日
Day1 = DateAdd("d", Nod1, Day1) ' -Nod1日前
For Each Range1 In myRange1
' 空白でなく、変更日が-Nod1日より前で、パスワード(New)[D列]が式の場合
If (Range1.Value <> "") _
And (Range1.Value < Day1) _
And (Range1.Offset(0, -2).HasFormula) Then '
If Range_t Is Nothing Then
Set Range_t = Range1 ' 最初に条件に合致したセル
Set Range2 = Range1 ' 最初に条件に合致したセル(最も古い日付)
Day2 = Range1.Value ' 最も古い日付を更新
NoC = 1 ' 合致したセル数をカウント
Else
Set Range_t = Union(Range_t, Range1) ' 2個目以降に条件に合致したセルを結合
NoC = NoC + 1 ' 合致したセル数をカウントアップ
If Range1.Value < Day2 Then
Set Range2 = Range1 ' 最も古い日付のセルを更新
Day2 = Range1.Value ' 最も古い日付を更新
End If
End If
End If
Next Range1
If Not (Range_t Is Nothing) Then ' 合致したセルがあったら
' Application.Goto Range2, True ' パスワード変更から-Nod1日経過したセルの内、最古のセルへ
Range_t.Select ' パスワード変更から-Nod1日経過したセル範囲を選択
Range2.Activate ' パスワード変更から-Nod1日経過したセルの内、最古のセルをActivate
ActiveWindow.SmallScroll ToLeft:=Range_t(1, 1).Column - 1 ' A列が一番左になるようにスクロール
Nod2 = 0 - Nod1 ' Nod1の符号を反転
MsgBox ("変更から" & Nod2 & "日以上経過したパスワードは" & NoC & "個あります。" & _
vbCrLf & "最古は" & Range2.Address & "(" & Day2 & ")です。") ' 合致したセル数の表示
End If
End Sub
【解説】
Range1.Offset(0, -2).HasFormulaで、Range1のセルの2列左のセル[ .Offset(0, -2) ]の
数式の有無[ .HasFormula ]を判定しています。
【参照記事】
エクセル小僧:条件付き書式(複数条件を数式で)
http://cheese999.blog.so-net.ne.jp/2016-06-15
エクセル小僧:更新期限切れのセルを選択(改2)
http://cheese999.blog.so-net.ne.jp/2016-07-15-1
ワークシート関数:ISFORMULA
VBA:HasFormula
例1:条件付き書式にISFORMULA(ワークシート関数)を使用した場合
=AND((TODAY()-$F8)>90, $F8<>"", CELL("format", $F8)="D1", ISFORMULA($D8))
【解説】
・F8セルからF55セルまで、日付が入っている。
・D8セルからD55セルまで、数式が入っている。
・条件1から条件4までの3つの条件全てが成立したとき、セルを赤く塗りつぶす。
・条件1:セル(F列)の日付が、今日から90日前より前であること⇒(TODAY()-$F8)>90
・条件2:セル(F列)が空白でないこと⇒$F8<>""
・条件3:セル(F列)の書式が日付(yyyy/mm/dd)であること⇒CELL("format", $F8)="D1"
・条件4:セル(D列)が数式であること⇒ISFORMULA($D8)
例2:マクロで.HasFormulaを使って数式の有無を判定している場合
Sub HenKigen1(ByVal sheet1 As Worksheet, ByVal Nod1 As Long, ByVal myRange1 As Range)
'【機能】パスワード変更から-Nod1日経過した最初のセルへ飛ぶ
'【引数】
' sheet1 : シート名
' Nod1 : 日数 (Number of Days) 90日前の場合 Nod1=-90
' myRange1 : 検索対象のセル範囲
'【変数】
Dim Day1 As Date ' 基準日
Dim Day2 As Date ' 条件に合致したセルの中で最も古い日付
Dim Range1 As Range ' セル範囲
Dim Range2 As Range ' セル範囲(最も古い日付)
Dim Range_t As Range ' セル範囲(合計)
Dim NoC As Long ' 条件に合致したセル数(Number of Cell)
Dim Nod2 As Long ' Nod1の逆
'【コード】
sheet1.Activate ' 対象シートをアクティブ化
Day1 = Date ' 今日
Day1 = DateAdd("d", Nod1, Day1) ' -Nod1日前
For Each Range1 In myRange1
' 空白でなく、変更日が-Nod1日より前で、パスワード(New)[D列]が式の場合
If (Range1.Value <> "") _
And (Range1.Value < Day1) _
And (Range1.Offset(0, -2).HasFormula) Then '
If Range_t Is Nothing Then
Set Range_t = Range1 ' 最初に条件に合致したセル
Set Range2 = Range1 ' 最初に条件に合致したセル(最も古い日付)
Day2 = Range1.Value ' 最も古い日付を更新
NoC = 1 ' 合致したセル数をカウント
Else
Set Range_t = Union(Range_t, Range1) ' 2個目以降に条件に合致したセルを結合
NoC = NoC + 1 ' 合致したセル数をカウントアップ
If Range1.Value < Day2 Then
Set Range2 = Range1 ' 最も古い日付のセルを更新
Day2 = Range1.Value ' 最も古い日付を更新
End If
End If
End If
Next Range1
If Not (Range_t Is Nothing) Then ' 合致したセルがあったら
' Application.Goto Range2, True ' パスワード変更から-Nod1日経過したセルの内、最古のセルへ
Range_t.Select ' パスワード変更から-Nod1日経過したセル範囲を選択
Range2.Activate ' パスワード変更から-Nod1日経過したセルの内、最古のセルをActivate
ActiveWindow.SmallScroll ToLeft:=Range_t(1, 1).Column - 1 ' A列が一番左になるようにスクロール
Nod2 = 0 - Nod1 ' Nod1の符号を反転
MsgBox ("変更から" & Nod2 & "日以上経過したパスワードは" & NoC & "個あります。" & _
vbCrLf & "最古は" & Range2.Address & "(" & Day2 & ")です。") ' 合致したセル数の表示
End If
End Sub
【解説】
Range1.Offset(0, -2).HasFormulaで、Range1のセルの2列左のセル[ .Offset(0, -2) ]の
数式の有無[ .HasFormula ]を判定しています。
【参照記事】
エクセル小僧:条件付き書式(複数条件を数式で)
http://cheese999.blog.so-net.ne.jp/2016-06-15
エクセル小僧:更新期限切れのセルを選択(改2)
http://cheese999.blog.so-net.ne.jp/2016-07-15-1
2016-08-05 07:54
ヤバイぜ!(8)
コメント(1)
トラックバック(0)
ヤバイぜ! ありがとうございます[__猫]
by cheese999 (2016-08-06 22:50)