[[20080602145300]]　『イベントマクロ』（まくりん）　ページの最後に飛ぶ

『イベントマクロ』（まくりん）

 過去ログを見ながら以下のようなイベントマクロを作成しましたが、
うまくいきません。

 KLM列3行以下　にア・イ・ウ・・・・シという選択肢があり
 その内容がQ14：R16に入っています。
 選択すると、N列の同じ行に　Rから選んできた文字を記載するというイベントマクロのつもりです

　　　　Q　　　　R

 4　　　ア　　　○○○
 5　　　イ　　　○○○
 6　　　ウ　　　○○○
 ・・・
 16

 K L M列は　ア～スまで各列ひとつずつ、選べます。

 Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
    ' ｢K3:M(i)｣のｾﾙ以外は処理なし
    If ((Target.Row > 3) Or (Target.Column > 10) Or (Target.Column < 14)) Then Exit Sub

      Cells(, 1).Value = "=VLOOKUP(K3,Q4:R16,2)&""　""&VLOOKUP(L3,Q4:R16,2)&""　""&VLOOKUP(M3,Q4:R16,2)"
      Cells(, 1).Value = Cells(, 1).Value

 End Sub

 Cells(, 1) が[N列の同じ行に]のつもりなら、
Cells(Target.Row, 14) とします。
また、入力したい数式が
=VLOOKUP(K3,Q4:R16,2)&"　"&VLOOKUP(L3,Q4:R16,2)&"　"&VLOOKUP(M3,Q4:R16,2)
であるなら、
"=VLOOKUP(K3,Q4:R16,2)&""　""&VLOOKUP(L3,Q4:R16,2)&""　""&VLOOKUP(M3,Q4:R16,2)"
でしょうか。
ただこの数式ですと、Targetが何行目であっても同じ式が入りますけど、
それでよいのでしょうかね。（検索値は必ず３行目）
もしかしたら、次のような数式でしょうか。
　
"=VLOOKUP(K" & Target.Row & ",Q4:R16,2)&""　""&VLOOKUP(L" & Target.Row & ",Q4:R16,2)&""　""&VLOOKUP(M" & Target.Row & ",Q4:R16,2)"
　
    If ((Target.Row > 3) Or (Target.Column > 10) Or (Target.Column < 14)) Then Exit Sub
これもロジック的には
「列番号が１０より大きい場合」または「列番号が１４より小さい場合」ですが、
Targetがどんな列でもプロシージャを抜けてしまいます。
どうも不等号の向きが逆ではないかと思われます。
　
 Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
    ' ｢K3:M(i)｣のｾﾙ以外は処理なし
     If Target.Row < 3 Then Exit Sub
     If Target.Column <= 10 Then Exit Sub
     If Target.Column >= 14 Then Exit Sub
     Cells(Target.Row, 14).Formula = _
         "=VLOOKUP(K" & Target.Row & ",Q4:R16,2)&""　""&VLOOKUP(L" & Target.Row & ",Q4:R16,2)&""　""&VLOOKUP(M" & Target.Row & ",Q4:R16,2)"

     Cells(Target.Row, 14).Value = Cells(Target.Row, 14).Value

 End Sub
　
(みやほりん)(-_∂)b

 ありがとございます。
 みやほりんさまのおかげで出来ましたが、一回目の処理の際、3つのセルのうちひとつでも空白があると、#N/Aとなってしまいます。回避方法はないでしょうか？

 　　
対処法は、何種類かありますが、
１）数式の工夫
２）検索範囲Q列の最後のデータとして「0」を仕込んでおく。
３）コードでKLM列の入力状況を判別して条件分岐。
　　
などを思いつきます。
なお、近似値検索を意図しない場合は、VLOOKUPの検索の型にFALSEを
指定することをお勧めします。
(みやほりん)(-_∂)b

 VLOOKUPはあきらめて 
 別シートにリスト.sheetというのを作って以下のコードを作ってみましたがやっぱりだめでした。

 Private sub 
　   If Target.Row < 3 Then Exit Sub
     If Target.Column <= 10 Then Exit Sub
     If Target.Column >= 14 Then Exit Sub

 Dim i , j , k  As Integer
 i =  Range("K").value
 j =  Range("L").value 
 k =  Range("M").value   
 Worksheets("Sheet1").Range("N" & 3 + i) = Worksheets("リスト").Cells(i +1, 2) + Cells(j +1, 2) +Cells(k +1, 2) 
 EndSub

 またもう少し悩んでみます。（まくりん）

 あらら、1000字以上のアドバイスを簡単に捨ててしまわれるのですねぇ。
　
Excel VBAは「Excelに命令を伝える為の言語」ですから、テキトーでは動きません。
VBAは便利ですが、融通は利かないので。
　
同様に・・・・
私が具体的な回避策を私が書けなかったのは、
「どうなれば#N/A!を回避したことになるのか」が
具体的に書かれていないからです。
　
#N/A!となるような場合は、どういう結果になればよろしかったのでしょうか。
うーん、残念ながら、たぶん今週はこれで回答打ち止めですので、一番簡単だ
と思われる方法を。
　
・Q4:R16　のリスト範囲をQ4:R17を使うようにして、Q17に「0」を入力。
・Cells(Target.Row, 14).Formula = _　に続く数式のQ4:R16の範囲を
　Q4:R17に書き換え。
・VLOOKUP関数の第４引数にFalseを追加
　
このような変更で入力した部分だけが（とりあえず）生かされるはずです。
(みやほりん)(-_∂)b

 >あらら、1000字以上のアドバイスを簡単に捨ててしまわれるのですねぇ。

 すみません。
 みやほりんさまにいただいたアドバイスはすべてやってみましたが、
 N/Aとなることは回避できませんでした。追記いただいたこともさせていただいたんですが・・。

 それで、今度は、VLOOK以外の方法を考えたのですが、上のとおりやっぱりうまくいきませんでした。

 三つ選択肢を入力した場合は、#N/A!となりませんが、データを一つでも
 クリアにしてしまうと、#N/A!となってしまいます。
 一つ選んだときは一つだけ出るようにしたいのです。（まくりん）

 少し時間が出来ましたので、先のアドバイス時に作ったサンプルの
提示だけしておきます。
　
 Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Row < 3 Then Exit Sub
     If Target.Column <= 10 Then Exit Sub
     If Target.Column >= 14 Then Exit Sub
     If WorksheetFunction.CountIf(Range("Q4:Q17"), Target.Value) Then
     Cells(Target.Row, 14).Formula = _
         "=VLOOKUP(K" & Target.Row & ",Q4:R17,2,false)&""　""&  " & _
         "VLOOKUP(L" & Target.Row & ",Q4:R17,2,false)&""　""&  " & _
         "VLOOKUP(M" & Target.Row & ",Q4:R17,2,false)"
     Cells(Target.Row, 14).Formula = "'" & Cells(Target.Row, 14).Value
     End If
 End Sub
　
コード以外には、セルQ17に「0」を入力してあります。
	K	L	M	N	O	P	Q	R
[2]								
[3]	d	d	h	4　4　8				
[4]	m	l		13　12　			a	1
[5]							b	2
[6]							c	3
[7]							d	4
[8]							e	5
[9]							f	6
[10]							g	7
[11]							h	8
[12]							i	9
[13]							j	10
[14]							k	11
[15]							l	12
[16]							m	13
[17]							0
　
当方の検証シート入力状況です。
WorksheetFunction.CountIf・・・が増えていますが、リストに
ない入力値があったときの為のトラップです。
(みやほりん)(-_∂)b

 お忙しい中ありがとうございます。迷惑かけてしまい申し訳ありません。
 KからMの間のセルの値を一個でもクリアすると、Cells(Target.Row, 14).Formula = "'" & Cells(Target.Row, 14).Valueというところで「型が一致しません」というエラーになります。0を選ぶとOKなんですが・・・。
 再度、考え直します。すみません（まくりん）

 ＞KからMの間のセルの値を一個でもクリアすると
 と言うのは、具体的な操作として
 　K3に「d」　L3に「d」　M3に「h」
 　が入っている状態（みやほりんさん御提示の表の3行目の状態です）
 　から、L3のセルを選択し、Delete（或いはBackSpace+Enter）を押すと
 　該当個所がエラーになってコードが止まる
 と言う事ですか？

 どのコード＆どの様な表で試して居られるのですかね？
 表は、エクセルから貼り付けると簡単に入力出来るので
 載せてみてはどうでしょう？

 みやほりんさんの最新の書き込みの表を作り
 そのコードを貼り付けて、L3セルの値を消しても
 別にエラーには成りませんが・・・。

 ＞KからMの間のセルの値を一個でもクリアすると、
 と書いて居られるのですから
 複数セルを同時に選んで「Delete」するとエラーになる
 と言う事では無いのですよね？

 (HANA)

 「0を入力」ではなくて「0を選択」ですか？
入力規則のリスト？
(みやほりん)

 HANAさまお返事ありがとうございます。
 ＞ 複数セルを同時に選んで「Delete」するとエラーになる
 と言う事では無いのですよね？
 複数セルを同時にではありません。
 説明不足ですみません。最新のコードで、エラーになるというのは、
 一度、KLMに入力している状態で、クリアするとエラーにはなりませんが、
 N列の文字は消えず、再度Kに文字を入れたときにCells(Target.Row, 14).Formula = "'" & Cells(Target.Row, 14).Valueというところで「型が一致しません」というエラーになり止まります。

 Q4:R17に選択肢用のリストを入れています。
　   K　  L    M             N
 4   ア	イ		#N/A
 >「0を入力」ではなくて「0を選択」ですか？
 入力規則のリストです。KLMは選択できるようになってます。
 説明不足ですみません。　まくりん

 ＞クリアする
 は、具体的にはどの様な操作ですか？
 そのセルを選択して、Delete？
 それとも、BackSpace + Eneter？
 或いは、それ以外の方法で？

 一度、新しいブックを開いてやってみて下さい。

 １．みやほりんさんご提示の表をコピー。
 ２．Sheet1のJ1セルを選択し、右クリック
 　　　→形式を選択して貼り付け→テキスト形式　を選択
 　　で、貼り付けた後、
 ３．Sheet1のシートモジュールに、コードを貼り付け
 ４．L3セルの値を消す。
 　　★１．N3セルの値は変化しますか？
 ５．L3セルに値を入れる。
 　　★２．N3セルの値は変化しますか？
 ６．K3:M3に入力規則のリストを設定
 ７．L3セルの値を消す。
 　　★３．N3セルの値は変化しますか？
 ８．L3セルの値を選択する
 　　★４．N3セルの値は変化しますか？
 以上の事を試して下さい。

 まくりんさんの仰って居られることがこちらで確認出来ません。
 以上の事を行うと、こちらでは　全ての★でN3セルの値は正しく変化します。
 そちらでも変化するかどうか、ご確認下さい。

 データが悪いのか、操作方法が悪いのか　その他の原因が有るのか
 何か分かるかもしれません。

 (HANA)

 HANAさま、すみません。新しいシートに上記の方法で、入れると出来ました。
 複数個所センタクして消した場合は型が一致しませんとエラーになります。　まくりん

 ＞複数セルを同時に選んで「Delete」するとエラーになる
 ＞と言う事では無いのですよね？
 と言う質問に

 ＞複数セルを同時にではありません。
 と言うお答えを頂いていますが

 ＞複数個所センタクして消した場合
 なのですか？

 ゆっくりで良いですので、そちらで何が起きているのか
 本当のことをお書き下さい。

 (HANA)

 説明不足ですみません。
 Cells(Target.Row, 14).Formula = "'" & Cells(Target.Row, 14).Valueのところで
 エラーになるのです。
 新規ブックでHANAさまにご教示いただいたとおりにするとできました。
 選択肢がアイウエオというカタカナだからでしょうか？
 あと、初心者的推測なのですが、同じブック内ですとVLOOKUPがうまく作動しない場合がたまにあり
 なにかブック自体でどこかまちがえているのでしょうか？　まくりん

 追記ですが、新しいシートに入れると、 複数個所センタクして消した場合は、型が一致しませんとエラーになります。　まくりん

 ＞選択肢がアイウエオというカタカナだからでしょうか？
 これは、「仮データとしてカタカナ」と言うのではなく
 「実際のデータも　アイウエオ」なのですか？

 まず、複数ヶ所選択を選択しての操作　には対応してないので
 これは諦めて於いて下さい。（話しもややこしく成りますし。）

 ＞新規ブックでHANAさまにご教示いただいたとおりにするとできました。
 このブックの、Q4～に実際のデータを貼り付けて試して見るとどうなりますか？
 　●セルQ17に「0」を入力
 は忘れず入力して於いてやってみて下さい。

 それと・・・
 ＞同じブック内ですとVLOOKUPがうまく作動しない場合がたまにあり
 これは、
 　「いつも正しい結果が出ている所に入っている
 　　いつもと同じ式が、時々変な結果を返すことがある」
 のでしょうか？　それとも、
 　「有る結果を導こうと思って作った式が
 　　思った結果を導かない事がある」
 のでしょうか？

 (HANA)

 HANAさんフォローありがとうございます。
思いのほか仕事が速く片付いたのでレスを入れたいと思います。
まず、複数セル選択してクリアしたときにエラーとなるのは私の環境では
次のステートメントです。
     If WorksheetFunction.CountIf(Range("Q4:Q17"), Target.Value) Then
　
これはTarget.Valueで配列が取得され、かつ、Countif関数では第2引数に
配列が受け入れられない為です。
　
次に、
 Cells(Target.Row, 14).Formula = "'" & Cells(Target.Row, 14).Value
このステートメントでエラーが出る状況は、次の場合に起こります。
いったんK列からM列に　#N/A!　が表示されるような入力を行い、
なおかつ、その原因となる値をクリアせずに適正な値を別のセルへ入力し
た場合、すなわち、「不正な値が入力されるのを許容している」ためです。
　
「リスト」で選択できる値の入力と「クリア」以外を防ぐ仕組み、
および、（複数のセルを一度にクリアすることが希望ならば）特定の範囲
をクリアするようなマクロをコマンドボタンなどで実行するような仕組み
が必要な感じですね。
　
 Private Sub CommandButton1_Click()
     Dim myRow As Long
     myRow = Me.Cells(Me.Rows.Count, 14).End(xlUp).Row
     Application.EnableEvents = False
     Me.Range(Me.Cells(3, 11), Me.Cells(myRow, 14)).ClearContents
     Application.EnableEvents = True
 End Sub

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim myRng As Range, myLng As Long
     If Target.Row < 3 Then Exit Sub
     If Target.Column <= 10 Then Exit Sub
     If Target.Column >= 14 Then Exit Sub
     For Each myRng In Range("K" & Target.Row & ":M" & Target.Row)
     myLng = myLng + WorksheetFunction.CountIf(Range("Q4:Q17"), myRng.Value)
     Next myRng
     If myLng = 3 Then
         Cells(Target.Row, 14).Formula = _
             "=VLOOKUP(K" & Target.Row & ",Q4:R17,2,false)&""　""&  " & _
             "VLOOKUP(L" & Target.Row & ",Q4:R17,2,false)&""　""&  " & _
             "VLOOKUP(M" & Target.Row & ",Q4:R17,2,false)"
         Cells(Target.Row, 14).Formula = "'" & Cells(Target.Row, 14).Value
     Else
         MsgBox "不正な値が入力されました"
         With Application
             .EnableEvents = False
             .Undo
             .EnableEvents = True
         End With
     End If
 End Sub
　
上記のコードではCommandbutton1にK3:Nnの範囲のクリアの動作を
割り当てています。K:Mの範囲で1行ずつなら複数範囲のデータクリ
アもたぶん大丈夫でしょう。
しかし、不正入力防止は入力規則の方で行うべきであるし、
N列に

=IF(COUNTIF($Q$4:$Q$16,K3),VLOOKUP(K3,$Q$4:$R$16,2,FALSE),"")&"　"& IF(COUNTIF($Q$4:$Q$16,L3),VLOOKUP(L3,$Q$4:$R$16,2,FALSE),"")&"　"& IF(COUNTIF($Q$4:$Q$16,M3),VLOOKUP(M3,$Q$4:$R$16,2,FALSE),"")

 という式を入力しておけば、マクロは不要であるような気もするし。
（Q17への0入力も不要）
もしかしたら、入力規則と関数の組み合わせで実現するのではないでしょうか。
　
＞VLOOKUPがうまく作動しない
エクセルが壊れていない限り、関数は指示されたとおりに作動します。
理解不足で関数の作り方に問題がある、もしくは、検索値とリストの作り方に
問題があるのでしょう。
　
(みやほりん)(-_∂)b

コメント返信：

[ 一覧(最新更新順) ]

[[20080602145300]] 『イベントマクロ』（まくりん） ページの最後に飛ぶ

[[20080602145300]]　『イベントマクロ』（まくりん）　ページの最後に飛ぶ