Excel VBA質問箱 IV

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

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


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

【63981】チェンジイベントについて たつ 10/1/7(木) 14:15 質問[未読]
【63982】Re:チェンジイベントについて kanabun 10/1/7(木) 14:45 発言[未読]
【63983】Re:チェンジイベントについて kanabun 10/1/7(木) 14:59 発言[未読]
【63984】Re:チェンジイベントについて たつ 10/1/7(木) 16:46 お礼[未読]
【63986】Re:チェンジイベントについて たつ 10/1/8(金) 10:37 質問[未読]
【63987】Re:チェンジイベントについて kanabun 10/1/8(金) 11:11 発言[未読]
【63988】Re:チェンジイベントについて kanabun 10/1/8(金) 11:43 発言[未読]
【63989】Re:チェンジイベントについて たつ 10/1/8(金) 12:50 お礼[未読]

【63981】チェンジイベントについて
質問  たつ  - 10/1/7(木) 14:15 -

引用なし
パスワード
   どうしても分からなくて投稿します

チェンジイベントを使用しています。
K列(k列内ならどこでもOK)に日付を入力すると、隣のセル(L列)にマクロによって設定された値が表示されるようになっているファイルがありますが…

同じ日付が続く場合、コピーして日付を入力しようと、
K列に同時に複数の値を入れると、一つのセルしか隣のセルに値が表示されません
これを回避するにはどうしたらよいのでしょうか?

どうか、アドバイスお願いします

Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastR As Integer, MDayR As Long
Dim 指定Day As Long, 基準Day As Long
Dim MyRange As Range, MyRange2 As Range
Dim MyItem As Date, 基準Item As Date

LastR = Sheets("設定").Range("A" & Rows.Count).End(xlUp).Row
If Target.Row = 1 Or Target.Row > LastR Then Exit Sub
If Target.Column <> 11 Then Exit Sub

Application.EnableEvents = False


MyItem = Range("K" & Target.Row).Value
基準Item = Range("G" & Target.Row).Value

With Sheets("MDay")
MDayR = .Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = .Range("A2:A" & MDayR).Find(what:=MyItem, lookat:=xlWhole)
  If Not MyRange Is Nothing Then
   指定Day = MyRange.Offset(0, 1).Value
   Set MyRange2 = .Range("A2:A" & MDayR).Find(what:=基準Item, lookat:=xlWhole)
     If Not MyRange2 Is Nothing Then
      基準Day = MyRange2.Offset(0, 1).Value
      Range("L" & Target.Row).Value = 指定Day - 基準Day - 10
     End If
     Else
      Range("L" & Target.Row).Value = ""
     End If
End With

Application.EnableEvents = True

End Sub

【63982】Re:チェンジイベントについて
発言  kanabun  - 10/1/7(木) 14:45 -

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

>同じ日付が続く場合、コピーして日付を入力しようと、
>K列に同時に複数の値を入れると、一つのセルしか隣のセルに値が表示されません
>これを回避するにはどうしたらよいのでしょうか?

入力した(複数)セル範囲と K列との共通するセル範囲をIntersectで抽出して
この範囲を 順にLoopしていけば良いと思います。

また、日付の検索は Findメソッドより ワークシート関数のMATCHを使ったほうが
セルの書式に左右されないので、確実かと思います。

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim myTarget As Range
  Dim 指定Day As Long, 基準Day As Long
  Dim myDate As Date, 基準Date As Date
  
  LastR = Range("A" & Rows.Count).End(xlUp).Row
  Set myTarget = Intersect(Range("K2").Resize(LastR - 1), Target) 'K列範囲
  If myTarget Is Nothing Then Exit Sub
  
  Application.EnableEvents = False
  
  Dim rngLookUp As Range
  Dim m As Variant
  With Sheets("MDay") '検索先範囲
   Set rngLookUp = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
  End With
  For Each c In myTarget
    c(1, 2).ClearContents      'L列をクリア
    myDate = c.Value        'K列
    基準Date = c.Offset(, -4).Value 'G列
    m = Application.Match(CLng(myDate), rngLookUp, 0)
    If IsNumeric(m) Then
      指定Day = rngLookUp.Item(m, 2).Value
      m = Application.Match(CLng(基準Date), rngLookUp, 0)
      If IsNumeric(m) Then
        基準Day = rngLookUp.Item(m, 2).Value
        c(1, 2).Value = 指定Day - 基準Day - 10 'L列
      End If
    End If
  Next
  
  Application.EnableEvents = True

End Sub

【63983】Re:チェンジイベントについて
発言  kanabun  - 10/1/7(木) 14:59 -

引用なし
パスワード
  
日付のMatch検索で
 Date型の変数 myDate に セルのValueを代入し
 Match検索でそれを整数に直して↓検索していますが、
>  m = Application.Match(CLng(myDate), rngLookUp, 0)
 
セルのValueプロパティでなく、セルのValue2プロパティを使えば
直接MATCHの検索値に日付(数値)を指定できます。
以下、一部変数の宣言忘れがありましたので
それを付け加えたプロシージャコードです。

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim LastR as long
  Dim myTarget As Range
  Dim 指定Day As Long, 基準Day As Long
  
  LastR = Range("A" & Rows.Count).End(xlUp).Row
  Set myTarget = Intersect(Range("K2").Resize(LastR - 1), Target) 'K列範囲
  If myTarget Is Nothing Then Exit Sub
  
  Application.EnableEvents = False
  
  Dim rngLookUp As Range, c As range
  Dim m As Variant
  With Sheets("MDay") '検索先範囲
   Set rngLookUp = .Range("A2", .Cells(.Rows.Count, 1).End(xlUp))
  End With
  For Each c In myTarget
    c(1, 2).ClearContents      'L列をクリア
    m = Application.Match(c.Value2, rngLookUp, 0)
    If IsNumeric(m) Then
      指定Day = rngLookUp.Item(m, 2).Value
      m = Application.Match(c.Offset(, -4).Value2, rngLookUp, 0)
      If IsNumeric(m) Then
        基準Day = rngLookUp.Item(m, 2).Value
        c(1, 2).Value = 指定Day - 基準Day - 10 'L列
      End If
    End If
  Next
  
  Application.EnableEvents = True

End Sub

【63984】Re:チェンジイベントについて
お礼  たつ  - 10/1/7(木) 16:46 -

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

アドバイスありがとうございました
いまから、解読してみます

また質問するかも知れませんが、その際は、よろしくお願いします

【63986】Re:チェンジイベントについて
質問  たつ  - 10/1/8(金) 10:37 -

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

ありがとうございました
おかげ様で解決しましたが、一点、どうしてもわからない部分があり質問します
下記のコードの中で、

”c(1,2)”

↑の部分があるのですが、どうしてこれが、隣のL列を示すのか??
超初歩的な質問ですみませんが、教えてください

>>  For Each c In myTarget
>    c(1, 2).ClearContents      'L列をクリア
>    m = Application.Match(c.Value2, rngLookUp, 0)
>    If IsNumeric(m) Then
>      指定Day = rngLookUp.Item(m, 2).Value
>      m = Application.Match(c.Offset(, -4).Value2, rngLookUp, 0)
>      If IsNumeric(m) Then
>        基準Day = rngLookUp.Item(m, 2).Value
>        c(1, 2).Value = 指定Day - 基準Day - 10 'L列
>      End If
>    End If
>  Next
>

【63987】Re:チェンジイベントについて
発言  kanabun  - 10/1/8(金) 11:11 -

引用なし
パスワード
   ▼たつ さん:
こんにちは。

>▼kanabun さん:
>
>ありがとうございました
>おかげ様で解決しましたが、一点、どうしてもわからない部分があり質問します
>下記のコードの中で、
>
>”c(1,2)”
>
>↑の部分があるのですが、どうしてこれが、隣のL列を示すのか??
>超初歩的な質問ですみませんが、教えてください

いえいえ、決して初歩的な質問ではないですよ
c はひとつのセル すなわちRangeオブジェクト … これはいいですね?
c(1,2) は 実は c.Item(1,2) の省略形なんです。

よく Cells(1,1) などと書きますが、これも実は
  ActiveSheet.Cells.Item(1,1)
の簡略表記法なんです。
  (意味はActiveSheetの「すべてのセルの集合」のうちの
   要素(1,1)の単一セル ということで [A1]セルになります。
   Cellsプロパティは シートのあとに付けるとシートのすべてのセルの集合
   という意味で、集合のなかの個々の要素を Itemプロパティで
   指定しているわけです)

たとえば、セルc が具体的に [Z2] セルのことだとすると、
  c(1,1) は
  Range("Z2").Item(1,1)
を簡略表記したもので、
[Z2]セルを基点としてその(1行目, 1列目) の要素、すなわち
[Z2]セル自身のことを指します。

そういう訳で [Z2]セルの右隣のセル[AA2] は 
   c.Item(1, 2)
という表現で指定できることになります。
(ちなみに
  c が [Z2]のとき 左隣の[Y2]セルは c.Item(1, 0) と表せます。
  c(1, 0)
  では、c(0,1) といったら どのセルのことでしょう?
   [Z1]セルです)

◆ここにとても分かりやすく書いてあるので、参考にしてください
ht tp://kobobau.com/xls/yama/vba/range.address1.html

【63988】Re:チェンジイベントについて
発言  kanabun  - 10/1/8(金) 11:43 -

引用なし
パスワード
   よくみれば 同じところで Itemプロパティを明記して書いてましたね

>    m = Application.Match(c.Value2, rngLookUp, 0)
>    If IsNumeric(m) Then
>      指定Day = rngLookUp.Item(m, 2).Value

これは
 c.Value2 の値で 別シートの rngLookUp という1列範囲をMatch検索する
 見つかると、rngLookUp範囲内の何行目に見つかったか 行番号が
 返されるので、
>   指定Day = rngLookUp.Item(m, 2).Value
 で、
  rngLookUp という範囲の (m行目, 2列目)の値を 変数「指定Day」に
  代入する

という構文になり、
c(1,2) すなわち c.Item(1,2) と同じ方法で ある範囲の(から見た)単一
セルを参照しています。

【63989】Re:チェンジイベントについて
お礼  たつ  - 10/1/8(金) 12:50 -

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

とってもわかりやすくて、理解ができました
本当にありがとうございました

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