Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
case文
投稿日時: 21/06/02 10:33:05
投稿者: 春麗

A列の日付を見て J列に曜日を入れているのですが,
祝日場合は曜日ではなく祝日入れる場合,苦肉の策で以下の様にしてみたのですが,
毎年変動する祝日もあり,毎年case文を書き替えなければいけないのでしょうか.
 
   Dim i, x As Long
   Dim syukujitu As String
     
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
  For i = 2 To LastRow
     Cells(i, 10) = "=TEXT(RC[-9],""aaa"")"
  syukujitu = Cells(i, 1).Value
 
Select Case syukujitu
Case "2021/01/01"
Cells(i, 10) = "祝日"
Case "2021/01/11"
Cells(i, 10) = "祝日"
Case "2021/02/11"
Cells(i, 10) = "祝日"
Case "2021/03/23"
Cells(i, 10) = "祝日"
Case "2021/03/20"
Cells(i, 10) = "祝日"
Case "2021/04/29"
Cells(i, 10) = "祝日"
Case "2021/05/03"
Cells(i, 10) = "祝日"
Case "2021/05/04"
Cells(i, 10) = "祝日"
Case "2021/05/05"
Cells(i, 10) = "祝日"
Case "2021/07/22"
Cells(i, 10) = "祝日"
Case "2021/07/23"
Cells(i, 10) = "祝日"
Case "2021/08/08"
Cells(i, 10) = "祝日"
Case "2021/09/08"
Cells(i, 10) = "祝日"
Case "2021/09/23"
Cells(i, 10) = "祝日"
Case "2021/11/03"
Cells(i, 10) = "祝日"
Case "2021/11/23"
Cells(i, 10) = "祝日"
End Select
 

回答
投稿日時: 21/06/02 11:06:07
投稿者: Suzu

普通は、カレンダーのリストを作ります。
 
例えば、Sheet「祝日」のA列に、
    A
1    2021/1/1
2    2021/1/11
3    2021/2/11
4    2021/3/23
5    2021/3/20
6    :
 
 
とすれば、VBAを使用せずとも
=IF(COUNTIF(祝日!A:A,A1)=1,"祝日",TEXT(A1,"aaa"))
の様な式で良いのでは。

回答
投稿日時: 21/06/02 12:20:25
投稿者: mattuwan44

>毎年変動する祝日もあり,毎年case文を書き替えなければいけないのでしょうか.
 
祝日は、固定の月日もありますが、
多くは計算(数式を設定する)ことで、求められます。
ですが、近年ちょいちょい法律が変わるので、メンテナンスも大変です。
 
メンテナンスの事を考えたら、シート上にリストを作っておくことをお勧めします。
 
参考URL>>
 
https://www.becoolusers.com/use/holiday.html
 
http://addinbox.sakura.ne.jp/holiday_logic.htm

投稿日時: 21/06/02 15:34:41
投稿者: 春麗

Suzuさん
mattuwan44さん
 
シートを作成する方法,どうもありがとうございました.
これならシートを更新すればよいのでマクロを触ることはなくなりますね.
 
マクロの中にある一つの処理なものですから,
Suzuさんに教えていただいた式を使ってマクロに組み込んでみようと思います.

投稿日時: 21/06/02 22:20:55
投稿者: 春麗

Suzu さんの引用:
普通は、カレンダーのリストを作ります。
 
例えば、Sheet「祝日」のA列に、
    A
1    2021/1/1
2    2021/1/11
3    2021/2/11
4    2021/3/23
5    2021/3/20
6    :
 
 
とすれば、VBAを使用せずとも
=IF(COUNTIF(祝日!A:A,A1)=1,"祝日",TEXT(A1,"aaa"))
の様な式で良いのでは。

 
教えてください.
祝日sheetを作成しました.
  
        A
1 2021/1/1
2 2021/1/11
3 2021/2/11
4 2021/3/23
   
そして,指定セルに以下の関数が書き込まれる様にしました.
  
   LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    LastRow = LastRow - 1
    i = 2
  Do While i <= LastRow
   Cells(i, 10).Value = "=IF(COUNTIF(祝日!A:A," & Cells(i, 1) & ")=1,""祝日"",TEXT(" & Cells(i, 1) & ",""aaa""))"
  
ところが,どうしても 月 や 火 の様に曜日が入ります.
何が間違っているのかどうしてもわかりません.
 
作成したいシート情報ですが,
A2セル以降,A列に日付が入っています.
A列と同じ行のJ列に祝日 または 曜日 を入れる様にしています.

回答
投稿日時: 21/06/02 23:15:29
投稿者: WinArrow
投稿者のウェブサイトに移動

代案1
> Cells(i, 10).Value = "=IF(COUNTIF(祝日!A:A," & Cells(i, 1) & ")=1,""祝日"",TEXT(" & Cells(i, 1) & ",""aaa""))"
 

   Cells(i, 10).Formula = "=IF(COUNTIF(祝日!A:A," & Cells(i, 1).Address(0,0) & ")=1,""祝日"",TEXT(" & Cells(i, 1).Address(0,0) & ",""aaa""))"
 
代案2
    If WorksheetFunction.Countiif(Worksheets("祝日").Columns("A"), Cells(i, 1).Value) = 1 Then
        Cells(i, 10).Value = "祝日"
    Else
        Cells(i, 10).Value = Format(Cells(i, 10).Value, "aaa")
    End If
 
 
ところで
> LastRow = LastRow - 1
は、何のおまじないですか?
 

回答
投稿日時: 21/06/02 23:35:06
投稿者: WinArrow
投稿者のウェブサイトに移動

余計なことかもしれませんが、
「振替休日」や「国民の休日」は、「祝日」なんですか?
祝日カレンダーは、何時作成するのですか?
処理のタイミングで2年分持った方がよいかもしれません。
(年度をまたがるデータがある場合)
 
通常は、2月頃、翌年の祝日が決まります。
今年は、特別なのかもしれませんが、今年になってから、今年の五輪関連の祝日が決まりました。
年に1回の処理だと固定的に考えない方がよいと思います。

投稿日時: 21/06/02 23:54:50
投稿者: 春麗

WinArrow さんの引用:

   Cells(i, 10).Formula = "=IF(COUNTIF(祝日!A:A," & Cells(i, 1).Address(0,0) & ")=1,""祝日"",TEXT(" & Cells(i, 1).Address(0,0) & ",""aaa""))"
 

 
WinArrowさん
 
どうもありがとうございました.
Address(0, 0)はどの様な働きをしているのでしょうか.
プロパティの引数で、前半が行、後半が列だと思うのですが,
Value ではなく,Formula を使うことに関連するのでしょうか.
Valueばかりを使っていましたが,.Formula の説明を始めて読みました.
計算式を設定する場合と,算式を参照する場合 の違いの様に書かれていていましたが,
ピンときません.
お手すきの際に教えていただけるとありがたいです.
 
WinArrow さんの引用:

 
> LastRow = LastRow - 1
は、何のおまじないですか?
 

ウォッチ式で値を確認した際に,どうしても1多くカウントしていたので,
恥ずかしながらの浅知恵で -1としましたが,
今回改めて実行しウォッチ式を確認しますと,正しい値でした.
何かしらゴミがあったのか不明ですが,不要であることがわかりました.
ご指摘ありがとうございました.
 

回答
投稿日時: 21/06/02 23:57:32
投稿者: simple

こんな書き方もできますよ。

    Dim s As String
    Dim lastRow As Long

    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    s = "=IF(COUNTIF(祝日!A:A,A2)=1,""祝日"",TEXT(A2,""aaa""))"
    Range(Cells(2, "J"), Cells(lastRow, "J")).Formula = s
でしょうか。
代入する式は,変数にしておけば、簡単に内容を確認できます。
手間を惜しまないことですね。

回答
投稿日時: 21/06/03 07:16:17
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:
どうもありがとうございました.
Address(0, 0)はどの様な働きをしているのでしょうか.

 
Addressは、セル番地を取得するプロパティです。
プロパテイを省略すると「Value」(値)と見做されます。
 
セルに数式を代入する場合
  

セルB1に「=A1+1」という数式を代入するとして
(1)
Range("B1").Formula = "=" & Range("A1") & "+ 1"

(2)
@Range("B1").Formula = "=" & Range("A1").Address(0,0) & "+1"
または
ARange("B1").Formula = "=" & Range("A1").Address & "+1"
を比べてみてください。
  
引用:
プロパティの引数で、前半が行、後半が列だと思うのですが,

上位の@とAを比較すれば分かります。
どちらを使っても結果は同じになります。
  
しかし、セル範囲に一気に数式を代入する場合は、結果が異なります。
  
引用:
Value ではなく,Formula を使うことに関連するのでしょうか.
Valueばかりを使っていましたが,.Formula の説明を始めて読みました.
計算式を設定する場合と,算式を参照する場合 の違いの様に書かれていていましたが,
ピンときません.
お手すきの際に教えていただけるとありがたいです.

  
プロパティの意味をきちんと理解しましょう。
Value :「値」
Formula :「数式」
Address : アドレス・・・・セルのプロパティの場合は、セルの番地
   Address(0, 0) とAddress(False, False)は同じです。
   絶対参照にするか、相対参照にするかということです。
  
なお、「Value」を使っても「Formula」を使っても結果は同じになります。
結果が同じになれば、それでいいということではありません。
可読性をよくするためには、適切なプロパティを使うようにしましょう。

回答
投稿日時: 21/06/03 09:47:41
投稿者: WinArrow
投稿者のウェブサイトに移動

誤解があるといけないので・・・ひとこと
 
>「Value」を使っても「Formula」を使っても結果は同じになります
は、セルに代入する場合のことです。
 
セルを参照する場合は、まったく、異なります。
 

投稿日時: 21/06/10 10:21:56
投稿者: 春麗

simpleさん
代案をありがとうございました.
まったく同じ動きが確認できました.
メンバによっては,こちらの方がわかりやすい.と言う者もいました.
正解は一つではないのですね.
勉強になります.
 
 
WinArrowさん
なるほど!と思えることが増えると面白いですね.
本当にどうもありがとうございました.