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

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

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

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

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

Q&A

解決済

3回答

236閲覧

【VBA】文字を区切り、その文字をキーにして別ブックへ必要項目をコピー

ff.kk

総合スコア17

VBA

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

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

0グッド

0クリップ

投稿2019/01/21 09:40

VBAで以下のような【購入者履歴.xlsx】のシリアルをキーにして、イメージ説明

【在庫.xlsx】のB列に自動的にiphone7が記載させる方法を教えて頂きたいです。
イメージ説明

※在庫.xlsxのシリアルは既に記載済みのブックです。

発生している問題・エラーメッセージ

シリアルを【Alt+Enter】で区切ることはできましたが、 Findしようとすると以下の文で「型が一致していません」と表示されます。 Set c = wb.Sheets("sheet1").Cells.Find(what:=tep, LookAt:=xlWhole) (その後の処理もかけていません・・) よろしくお願いします。

該当のソースコード

VBA

1Sub Sample() 2 3 Dim wb As Workbook 4 Dim i As Integer 5 Dim myselect As Variant 6 Dim tmp As Variant 7 Dim c As Range 8 9 Application.ScreenUpdating = False 10 Set wb = Workbooks.Open("C:\Users\在庫.xlsx") 11 12 For i = Selection(1).Row To Selection(Selection.Count).Row 13 myselect = Cells(i, 3) 14 tmp = Split(myselect, vbLf) 15 Set c = wb.Sheets("sheet1").Cells.Find(what:=tep, LookAt:=xlWhole) 16 17 18 Next i 19 20 wb.Close (False) 21 Application.ScreenUpdating = True 22 23End Sub

試したこと

ここに問題に対して試したことを記載してください。

補足情報(FW/ツールのバージョンなど)

ここにより詳細な情報を記載してください。

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

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

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

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

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

guest

回答3

0

エラー回避のためのアドバイス

エラーの原因はおそらくlazybones2000さん指摘の通り、空欄セルの参照が原因と思われます。

For i = Selection(1).Row To Selection(Selection.Count).Row ~ Next

の部分がどんなループとなるのかは把握されていますでしょうか?

これは(アクティブなシートで)「選択されているセル範囲」の中で「先頭セル」の行から「最終セル」の行までをループする処理になっています。

購入履歴のブックで、対象範囲を選択した状態からマクロを開始し、その範囲を対象に処理したいものと思います。
しかしコードを進めていくと、ループの直前で開いた在庫のブックがアクティブとなるため、想定した動作となっていないのではないかと思います。

購入履歴のブックをアクティブ化にしてからループを行うことで意図したループとなると思います。
まずはここらへんからご確認ください。

tmp変数に関するアドバイス

Splitの結果を入れる変数tmpDim tmp As VariantのようにVB特有の何でも屋Variant型で宣言しているので、型の意識が薄くなっているのかもしれません。

Splitした結果は、要素ごとの配列として返されます。
型を正しく意識して宣言するならDim tmp() As Stringとなります。

ここまでで察しがついたかもしれませんが、tmpには結果として
tmp(0) = "AABBCC1100"
tmp(1) = "VVCCSS1100"
のように格納されてきます。

というわけで、
Set c = wb.Sheets("sheet5").Cells.Find(what:=tmp, LookAt:=xlWhole)
の部分も本来は
Set c = wb.Sheets("sheet5").Cells.Find(what:=tmp(0), LookAt:=xlWhole)
のように添え字をつけるのが正解なのですが、これもVB特有の冗長解釈で「添え字がなければ先頭の添え字」として処理されるものと思います。

今回は先頭だけでなく分解したすべての要素を処理したいものと思います。
lazybones2000さん回答にあるコードのようにSplitの結果要素をFor~Eachでループしてもいいですし、要素数を調べるUBound関数を利用してFor~Nextしてもいいです。

Sample1:For Eachの場合

Sub Sample1() Dim wb As Workbook Dim i As Integer Dim myselect As Variant Dim c As Range Dim tmp() As String '文字列配列で宣言 Dim t As Variant 'Split結果の要素取出し用。ForEachで取り出す要素はVariantで宣言する必要がある Application.ScreenUpdating = False Set wb = Workbooks.Open("C:\Users\在庫.xlsx") ThisWorkbook.Activate For i = Selection(1).Row To Selection(Selection.Count).Row myselect = Cells(i, 3) tmp = Split(myselect.Value, vbLf) For Each tmp In Split(myselect.Value, vbLf) Set c = wb.Sheets("sheet1").Cells.Find(what:=tmp, LookAt:=xlWhole) '~後続の処理~ Next tmp Next i End Sub

Sample2:UBoundの場合

Sub Sample2() Dim wb1 As Workbook '購入履歴ブック Dim ws1 As Worksheet '購入履歴シート Dim wb2 As Workbook '在庫ブック Dim ws2 As Worksheet '在庫シート Dim i As Integer Dim myselect As Variant Dim c As Range Dim rngSel As Range '選択されているセル範囲 Dim tmp() As String '文字列配列で宣言 Dim cnt As Integer 'Split結果の要素数カウンタ Application.ScreenUpdating = False '購入者履歴ブック Set wb1 = ThisWorkbook 'Set ws1 = wb1.Worksheets("Sheet1") Set ws1 = wb1.ActiveSheet '在庫ブック Set wb2 = Workbooks.Open("C:\Users\在庫.xlsx") 'Set ws2 = wb2.Worksheets("Sheet1") Set ws2 = wb2.ActiveSheet 'Selectionはアクティブなシートのみ取得できるため、対象シートをアクティブ化する ws1.Activate '選択範囲を変数に格納(※処理中のシート変更や別ブックのオープンによる誤動作を防止するため) Set rngSel = Selection '選択範囲をループ処理 For i = rngSel(1).Row To rngSel(rngSel.Count).Row myselect = ws1.Cells(i, 3) tmp = Split(myselect, vbLf) For cnt = 0 To UBound(tmp) 'シリアルの一致するセルを検索 Set c = ws2.Cells.Find(what:=tmp(cnt), LookAt:=xlWhole) If Not c Is Nothing Then '一致するセルが見つかったら転記 ws2.Cells(c.Row, "B") = ws1.Cells(i, "B") End If Next cnt Next i 'ブックのクローズ wb2.Close (False) '解放 Set wb1 = Nothing Set ws1 = Nothing Set wb2 = Nothing Set ws2 = Nothing Application.ScreenUpdating = True End Sub

(※2019/01/22 15:00 対象シートの明示、および後続処理についてコード修正しました)

その後の処理ですが、Findの結果は見つからない場合もあるので、lazybones2000さんのアドバイスのようにNothingの判定をしてから使用したほうが確実です。

参考になれば幸いです。

投稿2019/01/22 01:14

編集2019/01/22 06:09
jawa

総合スコア3013

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

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

ff.kk

2019/01/23 02:38

とても参考になり、ベストアンサーにするかどうか迷いましたが、今回は参考になったかつ回答の早かった方を優先にしました。 本当にありがとうございました。無事動きました。
jawa

2019/01/23 02:47

私からのアドバイスはlazybones2000さんの内容と重複する部分や引用も多いので、BA頂いてしまったらそれはそれで恐縮ですのでお気になさらず(^-^; ループの動きやSplit結果の型、対象ブックや対象シートを意識したコーディングについて理解を深めていただければなによりです。
guest

0

ベストアンサー

条件として、下記であるものとして回答します。
・検証に使用された2ファイルのデータは、提示されたもののみ
・提示のVBAは「購入者履歴」(きっと.xlsmの誤記)に記述されている
・「在庫」で複数のセルが選択された状態で保存されている
・Findメソッド内のtepはtmpの誤記

Findメソッドで発生しているエラーは、tmpがEmptyだからでしょう。

  • WorkBookOpen後のSelectionは、「在庫」の選択セルの参照

Forループはi=2とi=3の2回実行されることになります。

  • myselectでのCellsは自分、つまり「購入者履歴」のアクティブシートの指定セルの参照

ループ2回目で空欄であるCells(3,3)を参照

本来の意図としては、購入者履歴の選択セルでループさせたいのではないでしょうか。
Selectionはアクティブウィンドウにしか使えない(はず)なので、手抜き感満載ですが、Openの次の行に、ThisWorkbook.Activateを追加することで、自分のブックをアクティブにしてしまうのも手です。

ブックやシートの指定を省略してセル参照をされているので、それぞれどのシートのセルが参照されているのか、確認しながらでも考えてみてください。ブレークポイントとウォッチ式の使い方がわかると、この手のデバッグもイメージできると思います。

しかし、これだけでは本来の目的は果たせませんね。
Find結果のセルの左側に、検索データの左側のデータを入れること、そして、「在庫」を検索するtmp変数は複数の文字列を持った配列ですから、そのそれぞれについてFindする必要があります。
なるべく元のコードをいじらずにやるならばこうでしょうか。元のコードのFor文内だけの記載です。

VBA

1'検索元データ(購入者履歴シリアル)の入ったセル参照 2Set myselect = Cells(i, 3) 3'検索元データを行分割し、要素毎にループ 4For Each tmp In Split(myselect.Value, vbLf) 5 '在庫シートからシリアルを検索 6 Set c = wb.Sheets("sheet1").Cells.Find(what:=tmp, LookAt:=xlWhole) 7 '対象のシリアルが見つかったか 8 If Not c Is Nothing Then 9 '見つかったシリアルセルの左側のセルに、購入者履歴シリアルセルの左側のセルの値をセット 10 c.Offset(0, -1).Value = myselect.Offset(0, -1).Value 11 End If 12Next

要素毎のループはFor Eachで、セルの左側のセル参照にはOffsetを使用しています。詳細はヘルプなどをご覧ください。

投稿2019/01/21 15:48

編集2019/01/21 16:15
退会済みユーザー

退会済みユーザー

総合スコア0

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

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

ff.kk

2019/01/23 02:37

とても参考になりました。ありがとうございました。
guest

0

Set c = wb.Sheets("sheet1").Cells.Find(what:=tep, LookAt:=xlWhole)

tepはどこから出てきたのでしょうか?
この場合のエラーはよくあるタイプミスによるものでしょう

まずそこから修正してみてください

投稿2019/01/21 13:54

koryuohproject

総合スコア29

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

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問