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

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

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

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

Q&A

解決済

4回答

5550閲覧

ExcelのVBAで他のシートのデータを検察し、値を置換したいです。

lovelydai

総合スコア38

VBA

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

0グッド

0クリップ

投稿2018/11/01 05:09

こんにちは、簡単な変換ツールをExcelで作っていますが、vba知識がなくて困っています。

やりたいのは、他のシートの値を検索し、当てはまる場合値を置換するマクロです。
例えば、学部シートと学科シートが二つあるとします。

「学部シート」

ID学部名
文学部
工学部
法学部

「学科シート」

ID学部名学科名
文学部英文学部
文学部古文学部
工学部電気工学部
工学部環境工学部
法学部法学科

ここで、学科シートにあるボタンを押すと、マクロが実行され、学科シートにある学部名に基づいて学部シートを検索し、学部シートのID値に学部名セルの値を置換したいです。望ましい結果は以下の通りです。

「学科シート」

ID学部名学科名
英文学部
古文学部
電気工学部
環境工学部
法学科

Excelだと、VLOOKUP関数を用いてできる、とのことですがVBAは全く知識がなくて困っています。
どのようにすればできるか、ヒントがあれば教えてください。現在書いているコードはこんな感じです。
よろしくお願いします。

VBA

1 For Each row In region.Rows ' 行のループ 2 3 For Each cell In row.Columns 4 5 item = cell.Value 6 7 '空欄チェック、文字が無ければエラーメッセージを出して止める 8 If item = "" Then 9 MsgBox "空欄があります、確認してください。" 10 cell.Activate 11 End 12 Else '文字があれば、学部シートの学部名から一致する値を探す 13 14 '一致する値がある場合、セルに学部ID値を入れて値を置換する 15 cell.Value = "学部ID値" 16 17 '一致する値がない場合、エラーメッセージを出す 18 'MsgBox "紐付いている値がありません、確認してください。" 19 20 End If 21 22 Next

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

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

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

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

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

guest

回答4

0

ごっそり書き換えてしまいましたが、こんな感じでできるかと思います。

VBA

1Dim gakubu As Worksheet 2Dim gakka As Worksheet 3Dim result As Range 4Dim r As Long 5 6Set gakubu = Worksheets("学部") 7Set gakka = Worksheets("学科") 8 9r = 2 10Do While gakka.Cells(r, 2).Value <> "" 11 Set result = gakubu.Range("B:B").Find(gakka.Cells(r, 2).Value) 12 If Not result Is Nothing Then 13 gakka.Cells(r, 2).Value = gakubu.Cells(result.Row, 1).Value 14 End If 15 r = r + 1 16 DoEvents 17Loop 18

投稿2018/11/01 05:24

ttyp03

総合スコア16996

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

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

0

ベストアンサー

Excelだと、VLOOKUP関数を用いてできる、とのことですが

Index関数とMatch関数の組み合わせの間違いでは?
VLookup関数だと2列目を索引に出来ないので、
他のアプローチが必要かと思います。

どのようにすればできるか、ヒントがあれば教えてください。

VBAなら作業を自動でしてもらうように出来るので、
一時的にVLookup関数が使えるように、
一時的なシートを用意して一覧表を作っても全然OKです。
ただ、他の関数でも処理できるのでそっちも覚えておいて損はないでしょう。
ただし、今回のように計算結果を元の値と置き換えることは数式では出来ないので、
一旦別の空いたセルに書き込んで、後で上書きすることになると思います。
(他のアプローチももちろんあります。)
この操作を自動で行うよう、マクロを考えたらいいと思います。

変に、「プログラミングをするんだ」と、肩肘を張らず、
エクセル君に任せられるところは任せて、
自分は作業の手順だけを指示してやるように考えてみてはいかがでしょうか?

流れとしては、

1)表の右の列に値を検索する数式を表す文字列をセル範囲に一括で入力(つまり、計算等はエクセル君に任せる)
2)その列をコピーして値のみ2列目に貼付
3)数式列をクリア

で、いいのでは?

その前に、操作対象のセル範囲、あるいは、参照するセル範囲を
特定しておくことも肝要です。
あと、変数と同様に、一時的な作業列や作業シートを利用することが出来ると、
考え方が楽になると思います。

それから、データ数がどれくらいあるかわからないですが、
個別にセルの値を読み書きすると処理が遅いので、
一括で入力(数式も含む)や出力できると、高速化がのぞめます。
なので、エクセル君に任せられるところは任せた方が、
コードも短くなるし高速化も期待できると思います。

参考になれば。。。

書いていて思いつきました。
置換のリストがあるんだから、
それを順に見て行って、
エクセル君の置換機能で置換るのもありですね。
検索機能だと、個々にループする処理が余分に時間が掛かりますね。

あと、提示されるコードは、
sub~end subまで書いてあると、
どんなことを考えているのか全体像が見えていいと思います。
一部のだけだと、一般論しか言及出来ないので、
それで事足りるならいいのですが、個別の具体的な例として、
質問者も回答側も同じデータやコードで話ができると、
動作確認ができますし、
いろいろな案も出てくるでしょう。また、それを実際に動かして比べられますし、
高速化などの話もいろいろ話が出来るかなとは思いますが、
そこまでは望んでないのでしょうかね。

時間が出来たので、コードを追記しておきます。

VBA

1'関数入力案 2Sub test1() 3 Dim rngTable As Range '更新対象の表 4 Dim rngKey As Range '検索の索引になるセル範囲 5 Dim rngTemp As Range '数式を一時的に入れるセル範囲 6 Dim rngList As Range '変更の元となる一覧表 7 Dim strFormula As String '数式とする文字列 8 9 'セル範囲特定 10 With Sheets("学科").Range("A1").CurrentRegion 11 Set rngTable = .Cells 12 Set rngKey = Intersect(.Offset(1), .Columns(2)) 13 Set rngTemp = Intersect(.Offset(1).EntireRow, .Columns(.Columns.Count + 1)) 14 End With 15 With Sheets("学部").Range("A1").CurrentRegion 16 Set rngList = Intersect(.Cells, .Offset(1)) 17 End With 18 19 '1行目の数式の作成(2行目以降はエクセル君に任せる) 20 strFormula = "=INDEX(" & rngList.Address(, , , True) & ",MATCH(" & _ 21 rngKey(1).Address(False, False, , external:=True) & "," & _ 22 rngList.Columns(2).Address(, , , True) _ 23 & ",0),1)" 24 25 '数式の入力 26 rngTemp.Formula = strFormula 27 '値のみ転記 28 rngTemp.Copy 29 rngKey.PasteSpecial xlPasteValues 30 '作業列のクリア 31 rngTemp.ClearContents 32End Sub 33 34'繰返し置き換える案 35Sub test2() 36 Dim rngList As Range 37 Dim rngTable As Range 38 Dim r As Range 39 40 With Sheets("学部").Range("A1").CurrentRegion 41 Set rngList = Intersect(.Cells, .Offset(1)) 42 End With 43 Set rngTable = Sheets("学科").Range("A1").CurrentRegion 44 45 For Each r In rngList.Rows 46 rngTable.Columns(2).Replace r.Cells(2).Value, r.Cells(1).Value 47 Next 48End Sub

Vlookup関数の話があったのでそちらに引きずられましたが、
単に置換の話なので置換機能を使った方がコードが簡便ですね。
(数式の場合は細かくセルを特定しなきゃいけないのと、
数式を示す文字列を作るのが面倒)
無かった時とかの例外処理も不要だったと思うけど、
一応デバッグとかしてみてください。
速度はどれが速いか興味深いですが、
そこまでテストする時間がないのでこの辺で^^;

投稿2018/11/03 01:10

編集2018/11/03 05:20
mattuwan

総合スコア2136

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

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

0

学科シートの[学部名]列と[学科名]列の間に1列追加して、以下の関数を入れてやれば実現できませんか?
各シートの列名(A,B…)は、各テーブルがそのシートの左上端に寄せて作られているものと想定して書いています。

Excel

1=VLOOKUP(B2, 学部シート!$A$2:$B$4, 2, False)

投稿2018/11/01 05:27

shun_kuwa

総合スコア187

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

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

0

VLOOKUP関数を用いてできる、とのことですがVBAは全く知識がなくて困っています。

VLOOKUPはワークシート関数です。VBAでの利用が前提ではありません。

単に置換したいだけなら、VLOOKUPで名称に変換した結果を、値の貼り付けで置換すれば良いのではないでしょうか

投稿2018/11/01 05:24

編集2018/11/02 01:21
sazi

総合スコア25138

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

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

退会済みユーザー

退会済みユーザー

2018/11/02 01:05

恐れながら確認です。VBAからでもWorksheetFunction.VLookup()で呼び出すことが出来るので「VBAでは使用できません」という表現だと語弊を招く恐れがあると思いますが如何でしょうか。
sazi

2018/11/02 01:20

仰る通りです。表現を変えました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問