Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


1506 / 13644 ツリー ←次へ | 前へ→

【73929】セルを比べて一致すれば指定のセルを表示 nonoka 13/3/22(金) 17:46 質問[未読]
【73933】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/22(金) 21:47 発言[未読]
【73936】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/22(金) 22:10 回答[未読]
【73938】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/22(金) 22:24 発言[未読]
【73939】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/22(金) 22:39 回答[未読]
【73940】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/22(金) 23:01 発言[未読]
【73941】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/22(金) 23:07 発言[未読]
【73942】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/22(金) 23:09 回答[未読]
【73945】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/23(土) 6:47 発言[未読]
【73947】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/23(土) 7:58 発言[未読]
【73960】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/25(月) 10:52 回答[未読]
【73961】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/25(月) 12:13 発言[未読]
【73962】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/25(月) 13:43 回答[未読]
【73963】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/25(月) 14:26 発言[未読]
【73964】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/25(月) 15:25 回答[未読]
【73965】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/25(月) 15:48 発言[未読]
【73966】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/25(月) 16:02 発言[未読]
【73967】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/25(月) 18:25 回答[未読]
【73968】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/26(火) 10:46 発言[未読]
【73969】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/26(火) 11:05 発言[未読]
【73970】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/26(火) 15:40 発言[未読]
【73971】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/26(火) 16:29 回答[未読]
【73972】Re:セルを比べて一致すれば指定のセルを表示 UO3 13/3/26(火) 17:16 発言[未読]
【73973】Re:セルを比べて一致すれば指定のセルを表示 nonoka 13/3/26(火) 17:35 お礼[未読]

【73929】セルを比べて一致すれば指定のセルを表示
質問  nonoka  - 13/3/22(金) 17:46 -

引用なし
パスワード
   いつもお世話になってます。

K3に本日の日付が入ってます。

表は9行目に見出しが入っていて10行目から下がデータになります。
K3と比べる日付はN〜AAに入ります。

N10が3/22でK3が3/22の時に見出しのN9をL10に表示したいです。

1.データは追加型でK列(この場合はK10)にデータが入力された時点で
N〜AAの日付を読み取り同じものがあれば同じ列の9行目をL10に表示したいです。
2.日付がなかった場合はK列(この場合はK10)とK3の本日の日付を比べ本日の日付より前なら”未投入”とL10に表示
3.上記でも該当がない場合、次はM列(この場合はM10)とK3を比べて本日の日付よりM10が小さかった場合は”完了”とL10に表示

としたいです。

どうぞよろしくお願い申し上げます。

【73933】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/22(金) 21:47 -

引用なし
パスワード
   ▼nonoka さん:

▼nonoka さん:

こんばんは
該当の日付と同じ日付がN〜AAに複数あった場合はどうしましょうか?

【73936】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/22(金) 22:10 -

引用なし
パスワード
   ▼UO3 さん:

>
>こんばんは
>該当の日付と同じ日付がN〜AAに複数あった場合はどうしましょうか?


こんばんは。またまたお世話になります。
複数ある場合はないです。

【73938】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/22(金) 22:24 -

引用なし
パスワード
   ▼nonoka さん:

確認です。

>N10が3/22でK3が3/22の時に見出しのN9をL10に表示したいです。

この記述と、その下の 1,2,3 の記述はどういう関係でしょうか?
この記述も1,2,3もその行のL列になんらかの値をいれますけど、
K列の日付もあった、K3の日付もあった場合、L列には何が入るのですか?

>K列(この場合はK10)にデータが入力された時点で

これは、入力(転記)された時点で自動的に実行ということですか?

【73939】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/22(金) 22:39 -

引用なし
パスワード
   ▼UO3 さん:
>▼nonoka さん:
>
>確認です。
>
>>N10が3/22でK3が3/22の時に見出しのN9をL10に表示したいです。
>
>この記述と、その下の 1,2,3 の記述はどういう関係でしょうか?
>この記述も1,2,3もその行のL列になんらかの値をいれますけど、
>K列の日付もあった、K3の日付もあった場合、L列には何が入るのですか?
>
>>K列(この場合はK10)にデータが入力された時点で
>
>これは、入力(転記)された時点で自動的に実行ということですか?


実行というよりたえずK3とNからAAを比べて日付が一致したていれば
N9からAA9の値を表示したいです。
NからAAは工程、下には工程通過日が入ります。
本日の工程をL列に表示したいだけです。
次の日になった場合、当然工程が一つ進みます。
すると、L列の表示も変わるようにしたいです。

説明が下手で申し訳ありません。

【73940】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/22(金) 23:01 -

引用なし
パスワード
   ▼nonoka さん:

実行のタイミングは、あとでいかようにもできますので横に置き
たとえば、K3の日付と同じものが N10にあったとします。
で、K10 の日付が P10 にあったとします。
L10 にいれるのはどこですか?

お聞きしているのはそういうことです。
K3の日付と比較して。。。。というところと
K10の日付と比較して。。。というところが、どういう関係なのかな?と思ったものですから。

【73941】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/22(金) 23:07 -

引用なし
パスワード
   ▼nonoka さん:

もしかして・・・
1,2,3が優先度だとして
K3の値とN〜AAの比較が 1 で、1,2,3 は 2,3,4 ということですか?
それならわかるのですが。

【73942】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/22(金) 23:09 -

引用なし
パスワード
   ▼UO3 さん:
>▼nonoka さん:
>
>もしかして・・・
>1,2,3が優先度だとして
>K3の値とN〜AAの比較が 1 で、1,2,3 は 2,3,4 ということですか?
>それならわかるのですが。

1で該当がない場合に2、
2でもない場合に3という意味です。

【73945】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/23(土) 6:47 -

引用なし
パスワード
   ▼nonoka さん:

どうも、意図がうまくつたわらないようですねぇ。
もちろん、1,2,3 の順番は理解してますよ。
お聞きしているのは、その 1,2,3 の前にK3の値とN〜AAの比較がありますけど、
それは、1,2,3 より優先度が高い、つまり、K3の日付がN〜AAにあれば
1,2,3は行わないのですね? そういうことですけど。

【73947】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/23(土) 7:58 -

引用なし
パスワード
   ▼nonoka さん:

判定の優先度がいまいちわかりませんが、K3とN〜AAの比較を第一優先としたコードです。

Sub Sample()
  Dim c As Range
  Dim a As Variant
  Dim r As Range
  Dim bDt As Double
  
  Application.ScreenUpdating = False
  bDt = Range("K3").Value2
  
  For Each c In Range("K10", Range("K" & Rows.Count).End(xlUp))
    With c.EntireRow
      .Range("L1").ClearContents
      If IsDate(c.Value) Then
        Set r = .Range("N1:AA1")
        a = Application.Match(bDt, r, 0)
        If IsNumeric(a) Then
          .Range("L1").Value = Cells(9, a + 13).Value
        Else
          a = Application.Match(.Range("K1").Value2, r, 0)
          If IsNumeric(a) Then
            .Range("L1").Value = Cells(9, a + 13).Value
          Else
            If .Range("K1").Value2 < bDt Then
              .Range("L1").Value = "未投入"
            Else
              If .Range("M1").Value2 < bDt Then .Range("L1").Value = "完了"
            End If
          End If
        End If
      End If
    End With
  Next
          
  Application.ScreenUpdating = True
  MsgBox "処理完了"
  
End Sub

【73960】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/25(月) 10:52 -

引用なし
パスワード
   ▼UO3 さん:
返信遅れまして申し訳ありません。
早急にご対応頂きありがとうございました。
作動確認OKでした。

K3の日付が日が変わるごとに変化します。
これをシートタブ、右クリック、コードとしたいのですが、
何か変更箇所等ありますでしょうか?
private sub?でしたか?
ご教授ください。

【73961】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/25(月) 12:13 -

引用なし
パスワード
   ▼nonoka さん:

最初にお聞きした実行タイミングの件ですね。
基本的には、このコードの内容を(少し手直しして)このシートのシートモジュールの
Worksheet_Change に記述することになりますが、その自動処理のトリガーとして
K10から下のK列に変更があった場合のほか、理屈としては、K3 が変更になっても
実行必要ですね。

K3 は人間が入力するのですか?それとも =TODAY() といった関数が入っているのですか?

【73962】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/25(月) 13:43 -

引用なし
パスワード
   ▼UO3 さん:
>
>K3 は人間が入力するのですか?それとも =TODAY() といった関数が入っているのですか?

K3=TODAY()です。

【73963】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/25(月) 14:26 -

引用なし
パスワード
   ▼nonoka さん:

こんにちは

そういうことでしたら

・まずブックを開いたときに処理する。
(一度処理すれば、その日のうちは2度目に開いたときの処理は不要ですが、まぁ、そこは横着に)
・で、万が一、K3の値をマニュアルで入力する場合があったときも考え、K3 と K10以降のK列の値が
 変更になれば処理。

ちょっと難点がなきにしもあらず。
夜中に作業をしていて、日付がかわったあと、シートに何かの変化があれば、K3の値がかわりますが
その時には、イベントをキャッチできませんので、処理は行われません。

ThisWorkbookモジュールに。(対象のシート名は、とりあえず"Sheet1"としていますが
実際のものに直してください。

(★コードを書いただけで動かして確認してません。不備あれば指摘願います)

Option Explicit

Const shName As String = "Sheet1"    '実際のものに

Private Sub Workbook_Open()
  Call Sample(Columns("K"))
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim r As Range
  If Not Sh Is Sheets(shName) Then Exit Sub
  Set r = Intersect(Target, Sh.Columns("K"))
  If r Is Nothing Then Exit Sub
  Call Sample(r)
End Sub

Sub Sample(Target As Range)
  Dim c As Range
  Dim a As Variant
  Dim r As Range
  Dim bDt As Double
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  With Sheets(shName)
   bDt = .Range("K3").Value2
  
   For Each c In Target.Cells
    If c.Row = 3 Or c.Row >= 10 Then
      With c.EntireRow
        .Range("L1").ClearContents
        If IsDate(c.Value) Then
          Set r = .Range("N1:AA1")
          a = Application.Match(bDt, r, 0)
          If IsNumeric(a) Then
            .Range("L1").Value = Target.Parent.Cells(9, a + 13).Value
          Else
            a = Application.Match(.Range("K1").Value2, r, 0)
            If IsNumeric(a) Then
              .Range("L1").Value = Target.Parent.Cells(9, a + 13).Value
            Else
              If .Range("K1").Value2 < bDt Then
                .Range("L1").Value = "未投入"
              Else
                If .Range("M1").Value2 < bDt Then .Range("L1").Value = "完了"
              End If
            End If
          End If
        End If
      End With
    End If
   Next
  End With
  
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  
End Sub

【73964】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/25(月) 15:25 -

引用なし
パスワード
   ▼UO3 さん:

ありがとうございます。
動作確認しました。動作はほぼ完璧でした。さすがです。

ひとつ、手直し可能か教えてください。
10行目から下(見出しから下)のデータ部分を削除すると
K列の見出しが消えてしまいます。K列のみオートフィルターをセットしています。
データを削除した場合を想定してK列の見出し及びオートフィルターは消えないようにしておきたいです。

>・で、万が一、K3の値をマニュアルで入力する場合があったときも考え、K3 と K10以降のK列の値が変更になれば処理。

これが、原因ならここを削除頂いてもかまいません。
宜しくお願いします。

【73965】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/25(月) 15:48 -

引用なし
パスワード
   ▼nonoka さん:

こんにちは

わぁ、行削除ですか!!

で、今、アップしたコードをつらつら眺めていたら、不備なところがてんこ盛り!!
それが、指摘の不具合につながっているのかどうかは、まだ確かめていません。
やっぱり、ちゃんと環境をつくって動かして確認しなきゃいけませんね。

少し時間ください。

(それにしても、行削除・・・ちょっと大変かも)

【73966】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/25(月) 16:02 -

引用なし
パスワード
   ▼nonoka さん:

こんにちは

まだ、ざくっとした確認しかしていないのですが・・・

で、さらに手を抜いて K3 の値が万が一入れなおされても処理はしないことにしました。

Option Explicit

Const shName As String = "Sheet1"    '実際のものに

Private Sub Workbook_Open()
  With Sheets(shName)
    Call Sample(.Range("K10", .Range("K" & .Rows.Count).End(xlUp)))
  End With
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim r As Range
  If Not Sh Is Sheets(shName) Then Exit Sub
  Set r = Intersect(Target, Sh.Columns("K"))
  If r Is Nothing Then Exit Sub
  If Target.Rows(1).Cells.Count = Target.Parent.Columns.Count Then Exit Sub '行削除、挿入
  Call Sample(r)
End Sub

Sub Sample(Target As Range)
  Dim c As Range
  Dim a As Variant
  Dim r As Range
  Dim bDt As Double
 
  Application.ScreenUpdating = False
  Application.EnableEvents = False
 
  With Sheets(shName)
   bDt = .Range("K3").Value2
 
   For Each c In Target.Cells
    If c.Row >= 10 Then
      With c.EntireRow
        .Range("L1").ClearContents
        If IsDate(c.Value) Then
          Set r = .Range("N1:AA1")
          a = Application.Match(bDt, r, 0)
          If IsNumeric(a) Then
            .Range("L1").Value = Target.Parent.Cells(9, a + 13).Value
          Else
            a = Application.Match(.Range("K1").Value2, r, 0)
            If IsNumeric(a) Then
              .Range("L1").Value = Target.Parent.Cells(9, a + 13).Value
            Else
              If .Range("K1").Value2 < bDt Then
                .Range("L1").Value = "未投入"
              Else
                If .Range("M1").Value2 < bDt Then .Range("L1").Value = "完了"
              End If
            End If
          End If
        End If
      End With
    End If
   Next
  End With
 
  Application.ScreenUpdating = True
  Application.EnableEvents = True
 
End Sub

【73967】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/25(月) 18:25 -

引用なし
パスワード
   ▼UO3 さん:
検証してみました。
本来、未投入となるところは空白になり、
完了となるべきところが未投入になります。
単に表示を逆にすればよろしいですか?


もう一つ、K列が他のコードで日付が変わっていきます。
いれた時点では表示は間違って表示されていますが、
ファイルを開きなおすと正常に表示されます。

おそらくIFの優先でたとえばひとつ目にtureならそれが残っている感じだと思います。

【73968】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/26(火) 10:46 -

引用なし
パスワード
   ▼nonoka さん:

こんにちは

まず、

>K列が他のコードで日付が変わっていきます。
>いれた時点では表示は間違って表示されていますが、
>ファイルを開きなおすと正常に表示されます。

「間違って表示」という意味が、いまいちわからないのですが、
「他のコード」とは、"計画調整" で K列にセットし、ループで調整しているところでしょうか?
最新の、その部分のコードがわからないのですが、仮に、そこで

Application.EnableEvents = False

'K列へのセット処理

Application.EnableEvents = True

こんなようになっているとすれば、「他のコード」で K列の日付がかわっても
今回の Workbook_SheetChange は実行されません。ブックを開きなおすと
Workbook_Open が必ず実行されるので、その結果、今回のコードが動きます。

ただ、「他のコード」に、Application.EnableEvents の手当てが加わっていないとすれば
そこでの変更で、対象の行(K列に値が入った行)について自動的に、今回の処理が実行される
はずなんですが・・・・????

もう1つ、K列への日付のセットが、"計画調整" だけで行われるとすれば、今回のコードも
Workbook_SheetChange ではなく "計画調整" 内で起動させるほうがいいかもしれませんよ。

で、

>本来、未投入となるところは空白になり、
>完了となるべきところが未投入になります。

「本来」というところが、コードとしては、そちらの要件どおりに書いているので、
どうなるべきかは、このトピで最初に説明のあった条件にしています。
念のため、コードと、最初の説明をつきあわせましたが、合致しているように思いますが?
コードで、処理している順番を言葉で書くと以下の通りです。
(条件が満足した時点で、その条件によるセットを行い、打ち止め)

1. まず、K列の入力が【日付】であった場合にのみ、この行を処理します。
  空白を含み、日付以外の値が入った場合は処理しません。(L列は空白となります)
2. K列入力された行に関して、その行のN〜AA列にK3(今日)があれば、その列のタイトルを L列にセットします。
3. K列入力日付が その行のN〜AA列にあれば、その列のタイトルをL列にセットします。
4. K列入力日付が K3(今日)未満、つまり昨日以前なら、L列に "未投入" とセットします。
5. K列入力日付が K3(今日)以上、つまり今日以降で、かつ M列日付が K3(今日)未満、つまり昨日以前なら
  L列に"完了"とセットします。
6. いずれの条件にも合致しなければ L列は空白になります。
  

【73969】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/26(火) 11:05 -

引用なし
パスワード
   ▼nonoka さん:

こんにちは

>K列が他のコードで日付が変わっていきます。
>いれた時点では表示は間違って表示されていますが、
>ファイルを開きなおすと正常に表示されます。

もう1つ可能性があります。
今回のコードでも、登場しますが

Application.EnableEvents = False

'なんらかの処理

Application.EnableEvents = True

こんなコードの実行中、「何らかの処理」で不具合があり、処理が中断されたとします。
そうしますと、Application.EnableEvents = True の実行がされていませんので
このエクセルでは、イベントが一切発生しなくなり、Workbook_SheetChange は実行されません。
このステータスは、このエクセルそのものを終わらせるまで継承されます。
(エクセルを終了させ、再度立ち上げると、Application.EnableEvents は True にリセット
 されていますので、イベントのキャッチができるようになりますが)

K列日付を変更しているのに、うんともすんとも いわなくなった場合、この可能性はありますね。

VBE画面のメニュー、 表示 -> イミディエイトウィンドウ で表示されるところに
?Application.EnableEvents とタイプしてエンターしますと次の行に 現在の状態が
False なのか True なのかが表示されます。
ここで False と表示された場合、Application.EnableEvents = True とタイプしてエンターすれば
復旧します。

【73970】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/26(火) 15:40 -

引用なし
パスワード
   ▼nonoka さん:


こんにちは

もしかして、最初の説明文の

>2.日付がなかった場合はK列(この場合はK10)とK3の本日の日付を比べ本日の日付より前なら”未投入”とL10に表示

これは、「K列に入力した日付がK3,本日の日付より大きければ、つまり明日以降なら"未投入"」
じゃないのですか?

【73971】Re:セルを比べて一致すれば指定のセルを...
回答  nonoka  - 13/3/26(火) 16:29 -

引用なし
パスワード
   ▼UO3 さん:
>
>これは、「K列に入力した日付がK3,本日の日付より大きければ、つまり明日以降なら"未投入"」
>じゃないのですか?

そうです。日本語から勉強する必要がありそうですね。
すいません。

【73972】Re:セルを比べて一致すれば指定のセルを...
発言  UO3  - 13/3/26(火) 17:16 -

引用なし
パスワード
   ▼nonoka さん:

それでは

If .Range("K1").Value2 < bDt Then



If .Range("K1").Value2 > bDt Then

にかえてお試しください。

【73973】Re:セルを比べて一致すれば指定のセルを...
お礼  nonoka  - 13/3/26(火) 17:35 -

引用なし
パスワード
   ▼UO3 さん:
出来ました!ありがとうございます!
さらにsubも入れて計画調整に組み込みました。
一応ざっくり検証しましたが、問題ないように思います。
ありがとうございました!
これからも宜しくお願い致します。

1506 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free