質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

1回答

24288閲覧

チェンジイベントを複数セル同時に適用する方法について

pararinrinrin

総合スコア26

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2020/05/11 08:49

vbaで特定の列に対しvalidationでドロップダウン(別シートの表を参照)を設定しています。

(例)
A列
1 佐藤
2 鈴木
3 田中
4 (空)

上記リストの中から値を選択すると、チェンジイベントで同じ行の特定のセルに数式が埋め込まれる仕組みです。

ドロップダウンリストから一つ一つ選択するのが面倒な時に、A列のリストと同様の値を外部ファイルか何かから複数コピーして貼り付けをすると以下★の部分に対し、「実行時エラー'13'型が一致しません」と表示されます。

VBA

1' チェンジイベント発生 2Private Sub Worksheet_Change(ByVal Target As Range) 3 Dim x As Long 4 x = Target.Row 5 6 ' イベント範囲を指定 7 If Intersect(Target, Range("H:H")) Is Nonthing Then 8 Exit Sub 9 Else 10 Dim rateFindData As Long 11 ' 選択した値が空でなかったら 12 If Not IsEmpty(Target.Value) Then 13  ' find関数を実行するプロシージャの呼び出し 14 rateFindData = findRateData(Target.Value) ' ★←ここでエラーになる★ 15   ' 戻り値が空でなかったら 16 If Not IsEmpty(rateFindData) Then 17 'K列に数式を埋め込む 18     Cells(x,11).Formula = "=数式" 19    20 End If 21 End If 22 End If 23End Sub 24 25' 別プロシージャ 26Public Function findRateData(ByVal data As String)As Long 27 28Dim rateSheet As Worksheet 29Set rateSheet = Worksheets("担当者") 30 31Dim c As Long 32' find関数でデータを検索 33c = rateSheet.Range("A2:A6").find( _ 34What:=data, LookIn:=xlValues, LookAt:=xlWhole).Row 35 36findRateData = c 37 38 39End Function 40

複数のデータを貼り付けた時のエラーを回避し、貼り付けたセル分一つ一つに上記の処理を走らせることは可能でしょうか?
おそれいりますが、ご回答のほどよろしくお願いいたします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

meg_

2020/05/11 09:34

何故エラーが出ているか分かりますか?
guest

回答1

0

ベストアンサー

複数セルが同時に変更されたとき、Targetには変更されたセル範囲が入ります。
そして、セル範囲を示すRangeオブジェクトのValueプロパティは、Variant型の2次元配列となります。
VBAの2次元配列は文字列に変換できないため、型が一致しません。のエラーが発生します。

対策としては、以下のようにFor EachでTargetを列挙し、列挙されたセル(c)に対して処理をするのがいいでしょう。
Targetが行や列の集合を示すRangeの場合はCellsプロパティでセルの集合を示すRangeに変換が必要ですが、
Worksheet_ChangeイベントのTargetであれば原則セルの集合なので問題ないです。

vba

1Dim c As Range 2For Each c In Target 3 If Not IsEmpty(c.Value) Then 4 '以下略 5 End If 6Next c

その他気になった点としてIf Not IsEmpty(rateFindData) Thenの判定があります。

rateFindDataLong型と宣言されているため、絶対にIsEmpty関数はFalseを返します。
IsEmptyはVariant型の値についてがEmptyという特別な値かどうか、を判定する関数なためです。整数と決まっている時点でEmptyではありません。

上記部分の削除や、親オブジェクトの明示などを行ったコードが以下になります。

vba

1' チェンジイベント発生 2Private Sub Worksheet_Change(ByVal Target As Range) 3 'https://teratail.com/questions/260640 4 5 ' イベント範囲を指定 6 If Intersect(Target, Me.Range("H:H")) Is Nothing Then 7 Exit Sub 8 End If 9 10 Dim c As Range 11 For Each c In Target 12 ' 選択した値が空でなかったら 13 If Not IsEmpty(c.Value) Then 14 15 Dim x As Long 16 x = c.Row 17 18 Dim rateFindData As Long 19 ' find関数を実行するプロシージャの呼び出し 20 rateFindData = findRateData(c.Value) ' ★←ここでエラーになる★ 21 22 'K列に数式を埋め込む 23 Me.Cells(x, 11).Formula = "=数式" 24 25 ' 戻り値が空でなかったら 26 'If Not IsEmpty(rateFindData) Then ''絶対にTrueになる。 27 End If 28 Next c 29End Sub 30 31' 別プロシージャ 32Public Function findRateData(ByVal data As String) As Long 33 34 Dim rateSheet As Worksheet 35 Set rateSheet = Me.Parent.Worksheets("担当者") 36 37 Dim c As Long 38 ' Find関数でデータを検索 39 c = rateSheet.Range("A2:A6").Find( _ 40 What:=data, _ 41 LookIn:=xlValues, _ 42 LookAt:=xlWhole _ 43 ).Row 44 45 findRateData = c 46 47End Function

投稿2020/05/11 09:30

imihito

総合スコア2166

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

pararinrinrin

2020/05/12 01:01

こちらの通りに実装したらうまくいきました! 「複数セルが同時に変更されたとき、Targetには変更されたセル範囲が入ります。」とご説明いただいた部分も大変参考になりました。ご丁寧にありがとうございました!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問