Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
Worksheet_Changeで複数の飛び飛びセルの指定方法
投稿日時: 21/10/20 09:21:40
投稿者: torao

お世話になっております。
 
シート内の指定セルの値変更時(入力規則)に
Worksheet_Changeで処理したいと考えております。
 
・指定セル:J4〜8行おきに指定したい。J4・J12・J20・J28・・・・J300
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Target.Address = Range("J4").Address Then
        Call 標準モジュール内のコード
    End If
End Sub
 
すみませんがアドバイスのほどよろしくおねがいします。

投稿日時: 21/10/20 10:01:08
投稿者: torao

よく考えたら
 J列の4行目〜300行目を選択したらEVENT発生
 IFで8行飛びの特定セルだったら
 処理・・・という流れで作成しています。

回答
投稿日時: 21/10/20 10:24:45
投稿者: Suzu

今、判らないのは、4行目から、8行毎 の部分でしょうか?
であれば、
 
if (セル.Row -4) Mod 8 = 0 Then
 
の様な条件分岐を使ってはどうでしょうか。
 
 
 
入力規則を使わずに、Changeイベントを使用する理由は何でしょうか?
 
入力規則であれば、
 
J列を条件付き書式の範囲として
=AND(ROW()>3,ROW()<300,MOD(ROW()-4,8)=0, 条件)
の様な数式で良いです。
 
 
対して、Changeイベントを使った場合、
 ・全セル に対しこの VBAが発動
 ・複数のセルに同時に値を入れる事があり得る場合を考える必要がある
    (J列の1セルづつではなく、対象セルを含んだ セルの値を一括で変更する操作
     コピペ・値を入力し確定時に Ctr+Enter にて複数セル同時値入力)
を考慮する必要があります。
その辺りを考慮しないと、全セルに対しクリア処理を行った場合、処理に多大な時間を要する事になります。
 
VBA の場合の ヒントとしては、
Intersect の引数に、Targer と、Range("J4:J300") を渡し、戻りを Rangeオブジェクトで受けます
その戻り値 の Rowプロパティ で判断すればよいです。

回答
投稿日時: 21/10/20 10:27:59
投稿者: Suzu

すみません。

Suzu さんの引用:
その戻り値 の Rowプロパティ で判断すればよいです。

 
この部分について訂正します、
戻り値が コレクションになる可能性がありますので、
コレクションの要素一つ一つに対し、Row にて判断を行う必要があります。

投稿日時: 21/10/20 10:35:58
投稿者: torao

アドバイスありがとうございます。
試してご報告いたします。

投稿日時: 21/10/20 12:28:17
投稿者: torao

アドバイス有難うございます。
下記のようにして転記出来ていることを確認できました。
 
【変更】指定セル:J4〜8行おきに指定したい。→I列に変更
 
・I列セル(8行おき):ID登録セル
・J列セル(8行おき):名前登録セル
・I列セルには、予め入力規則にて、別シートから名簿内の「名前」を登録している
 
処理流れ
・I列にある入力規則から氏名選択
・選択された氏名を別リストを検索し名前があれば
・I列の値をID置き換え、J列に名前転記
といった流れです。
 
※入力規則と数式での組み合わせも考えましたが、他に連動して処理したいことがあり
  VBAで作成いたしました。
 
(Worksheetモジュール)
 
Private Sub Worksheet_Change(ByVal Target As Range)
    
   '書き込み時のイベント再発防止(二重転記防止)
    Application.EnableEvents = False
 
    'ID列範囲を選択→イベント発生
    If Not Intersect(Target, Range("I4:I300")) Is Nothing Then
 
        '8行飛びセルなら処理
        If (ActiveCell.Row - 4) Mod 8 = 0 Then
 
            '別シートからI列にID転記、J列に名前転記
            Call 名前入力規則_ID変換
        End If
 
    End If
 
    Application.EnableEvents = True
     
End Sub
 
(標準モジュール)
 
Sub 名前入力規則_ID変換()
    Dim sh2 As Worksheet: Set sh2 = Worksheets("SH2")
    Dim i As Integer
    'リスト格納データ・名前→ID変換リスト取得
    Dim r As Long: r = sh2.Cells(sh2.Rows.Count, 4).End(xlUp).Row
    Dim v As Variant: v = sh2.Range("C3:D" & r).Value '比較用データ作成
    'IDの書き込み
    For i = 1 To UBound(v)
        If ActiveCell = v(i, 2) Then
         With ActiveCell
            .Value = v(i, 1)
            .Offset(, 1).Value = v(i, 2)
         End With
            Exit For
        End If
    Next i
End Sub

回答
投稿日時: 21/10/20 13:38:33
投稿者: Suzu

引用:
下記のようにして転記出来ていることを確認できました。

 
当方にてテスト(Excel2016)すると、動きませんが。。。
 
所々で使用している ActiveCell ですが、
Changeイベント発生時には、既に ActiveCellが 次のセル(次の行)に移動しており、
Targetのセルとは 一致しない事により、Row が次の行となるため、8の倍数となりませんので
 Call 名前入力規則_ID変換 が呼び出されません。
 
もしかして、3行目、11行目・・・ に入力し、4行目、12行目 の値を変えるのでしょうか?
 
だとしても、
今度は、名前入力規則_ID変換 内の ActiveCell は、入力したセルの次のセルになっていますから
Valueでの、一致判定が 希望通りの判定になりません。
 
その辺りは既に解決策を提示してはいるのですが、無視されている様ですので残念です。
 
 
新たに提示頂いた 名前入力規則_ID変換 について
・名前入力規則_ID変換 の 引数 として Rangeオブジェクトとして渡せば良いでしょう。
 ActiveCell ではなく、そのRangeオブジェクトに対し判定・操作を行います。
 
・毎回 リストを取得する必要があるのでしょうか?
 リストの更新頻度にもよりますが、ファイル読み込み時や、必要時に Public 変数に 読み込めば
 良いと思います。
 
・セル値 と 配列の値を比較
 セルの値を変数に受け、変数と配列値を判定した方が、
 セルへの参照を都度しなくて済むので高速に動作します。
 
・配列による検索ではなく、VLOOKUP関数でも良い様に思います。

トピックに返信