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

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

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

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

Q&A

解決済

2回答

1431閲覧

ExcelVBAでVlookupを使って値を取得する際、検索範囲の中にスペースが入っていると正しく取得されない

退会済みユーザー

退会済みユーザー

総合スコア0

VBA

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

0グッド

0クリップ

投稿2019/11/12 07:54

編集2019/11/12 14:39

お世話になっております。
現在、ExcelVBAの勉強をしているのですが以下の問題で色々と考えております。

【やりたいこと】

①Sheet1
イメージ説明

②Sheet2
イメージ説明

③結果
![イメージ説明説明]

【プログラム】

1:①のSheet1のA列に「Total」を結合させた文字列を作成
2:1の文字列をキーにしてVlookupでSheet2のA列を探し、見つかったらB列の値を①のD列にセット

【悩んでいること】

イメージ説明

上記A列に「Total」を結合させた文字列をメインキーとして、
下記の図A1~B6の範囲をVlookupの検索範囲としたいが、
A列にスペースが入っているためきちんと参照できていない
イメージ説明

【コード】

Option Explicit Private Sub Test() '++++ファイル Dim wb As Workbook 'ファイルのワークブック格納 Dim ws_MainSheet As Worksheet 'Sheet1シート名格納 Dim strMain_Lastcol As String '最終行格納 Dim ws_SubSheet As Worksheet 'Sheet2シート名格納 Dim strSub_Lastcol As String '最終行格納 Dim subTbl As Range 'データを参照する範囲 Dim MainKey As String 'データのメインキー Dim cnt As Integer 'For ~ Next のカウント変数 '++++定数 Const strPath As String = "C:\" Const strFileName As String = "Vlookuptest.xlsm" Const ws_Main_SheetName As String = "Sheet1" Const ws_Sub_SheetName As String = "Sheet2" Const Main_col As String = "A" Const Assin_col As String = "D" 'ファイルを開く Set wb = Workbooks.Open(strPath & "\" & strFileName) 'ファイル内のメインシート(Sheet1)取得 Set ws_MainSheet = wb.Worksheets(ws_Main_SheetName) ws_MainSheet.Activate 'ファイル内のサブシート(Sheet2)取得 Set ws_SubSheet = wb.Worksheets(ws_Sub_SheetName) ws_SubSheet.Activate '最終行をセット Call lastcol(ws_SubSheet, strSub_Lastcol) Set subTbl = ws_SubSheet.Range("A1:B" & strSub_Lastcol) 'メインシートSheet1の最終行セット Call lastcol(ws_MainSheet, strMain_Lastcol) 'ループ開始 cnt = 2 For cnt = 2 To strMain_Lastcol MainKey = ws_MainSheet.Range(Main_col & cnt).Value + "Total" On Error GoTo ErrHandl 'Vlookup Dim ret As String     '★★★ ret = WorksheetFunction.VLookup(MainKey, subTbl, 2, False) ws_MainSheet.Range(Assin_col & cnt).Value = ret Next Exit Sub ErrHandl: ret = "該当なし" Err.Clear Resume Next End Sub Function lastcol(ws_Sheet As Worksheet, strCnt As String) '最終行を取得 With ws_Sheet strCnt = .Cells(Rows.Count, 1).Row 'Excelの最終行を取得 strCnt = .Cells(strCnt, 1).End(xlUp).Row '目的の列の最終行を取得 End With End Function

【問題点】

上記のコードだと、Sheet2のA列にスペースがない場合は問題なく動きます
しかし、今回はSheet2のA列にスペースが入っているのでちゃんと動きません。
(スペースは全角、半角、または半角を2つなど色々なパターンがあります)

もしSheet2のA列にスペースがなくて、例えば「1月Total」と言うデータになっていれば
Sheet1のA列に「Total」を結合させた文字列をキーにしてすぐにVlookupができます。

でも、Sheet2のA列にスペースがあるからそれが難しいということで困っています。

なお、ワイルドカードを使えば?とおっしゃる方もいると思いますが、今回は検索範囲をあらかじめsubTblにセットしているのでどうやればいいのかも分かりません。

Vlookup関数を動かす前にあらかじめSheet2のA列からスペースを削除しておけばいいのだと思いますが、どうやればいいのかなと悩んでいます。

でもSheet2の方でA列のデータをコピーし、新しくC列に貼り付けて、そこでスペースを削除する、
その後、C列に対してVlookupを使うというのは不可、です。

どうやればSheet2のA列のスペースを削除した上でVlookup関数を動かすことができるのでしょうか?

ちなみに、別の列にあらかじめスペースを削除した値を貼り付けるというのはしないものとします

どなたかご存知の方教えていただけると幸いです。
それではどうぞよろしくお願いいたします。

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

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

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

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

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

mdj

2019/11/12 08:28

trim関数かSUBSTITUTE関数ではどうでしょうか。ソースは見てませんが。
退会済みユーザー

退会済みユーザー

2019/11/12 08:35 編集

例えば、Vlookupの検索キーを1行ずつVlookup関数で参照するならそれもアリだと思います。 でも今回の場合は、検索キー(Sheet1シートのA列)ではなくて検索範囲(Sheet2シート)の方にスペースが入っているのです。 また、検索範囲は丸ごと変数にセットしてあります。 なので、検索範囲に対してTrimやSUBSTITLE関数は使えないのです。 その上で何かほかに方法はないのかを知りたいなと思って投稿させていただきました。
mdj

2019/11/12 08:35

trim(ws_MainSheet.Range(Main_col & cnt).Value)じゃだめですか?
退会済みユーザー

退会済みユーザー

2019/11/12 08:48

ありがとうございます。 すみませんが、これは MainKey = ws_MainSheet.Range(Main_col & cnt).Value + "Total" これを Mainkey = trim(ws_MainSheet.Range(Main_col & cnt).Value) + "Total" にしたらどうか?という意味ですよね? 質問を読んでいただければ分かると思うのですが、ws_MainSheet, つまりSheet1の方は空白は入っていないのです。 サブシート(Sheet2)のA列に空白があって、 そのサブシートが検索範囲となっています。 そしてこの検索範囲はsubTblに代入してあります。 そして、 ret = WorksheetFunction.VLookup(MainKey, subTbl, 2, False) このコードでVlookupするのですが、 subTbl内に空白が入っているので正しくデータが取れないのが問題となっているのです。
moh1ee

2019/11/12 12:01

subTblに入れる前にSheet2・A列を上から下までループするなりで空白を削除した値で書き換えればよいのでは?
guest

回答2

0

ベストアンサー

VLOOKUPにこだわらず、Sheet2のA列を検索してはいかがでしょうか。
Private Sub Test()内の
MainKey = ws_MainSheet.Range(Main_col & cnt).Value + "Total"

MainKey = ws_MainSheet.Range(Main_col & cnt).Value
に変えます。
ret = WorksheetFunction.VLookup(MainKey, subTbl, 2, False)

ret = get_week(ws_SubSheet, strSub_Lastcol, MainKey)
に変えます。
そして、以下の関数を追加します。

VBA

1Private Function get_week(ws As Worksheet, lastrow As String, key As String) As String 2 Dim wrow As Long 3 Dim result As Variant 4 For wrow = 2 To CLng(lastrow) 5 result = Cells(wrow, "A").Value Like key & "*Total" 6 If result = True Then 7 get_week = ws.Cells(wrow, "B").Value 8 Exit Function 9 End If 10 Next 11 get_week = "該当なし" 12End Function

投稿2019/11/12 13:34

編集2019/11/12 14:55
tatsu99

総合スコア5424

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

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

退会済みユーザー

退会済みユーザー

2019/11/12 14:30

申し訳ございません、私の説明が足りませんでした。 Sheet2のA列は、「1月 Total」「2月Total」・・・だけでなく、 例えば Sheet2 A列 1月 1月 1月Total 2月 2月 Total 3月  Total ・・というふうに、Totalがない値も存在します。 なので、教えていただいたコードを動かすと、A列の値が「1月」しかないものの列の B列の値がコピーされてしまいます。 なので、やはりSheet1のA列に「Total」を結合させた文字列をキーにしないといけないようです。 せっかく考えていただいたのにすみませんがよろしくお願いいたします。
tatsu99

2019/11/12 14:58

Private Function get_weekの内容を、変えました。 1月について言えば "1月*Total"にマッチすれば、OKとなるようにしました。(*は任意のn文字)
退会済みユーザー

退会済みユーザー

2019/11/12 15:23

ありがとうございます。先ほどやってみたところ無事動きました。 すごいですね! でも、このコードがどうして望む動きをしたのかまだよく分かっていません。。。 明日、もっとコードをよく見て解析してみます。 まずは、解決していただきありがとうございました!
guest

0

Replace関数で空白を空文字にしてどうでしょうか?

vba

1Sub a() 2 3 Dim a As String 4 5 a = Cells(1, 1).Value 6 Debug.Print a 7 'あ    う 8 9 a = Replace(a, " ", "") 10 a = Replace(a, " ", "") 11 Debug.Print a 12 'あう 13 14End Sub

投稿2019/11/12 12:13

meg_

総合スコア10577

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

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

退会済みユーザー

退会済みユーザー

2019/11/12 13:22

申し訳ございません。 ちゃんと質問読んで、プログラムも実際に動かしてみていただけますか??
meg_

2019/11/12 13:29

sheet2のセルを上記コードのように編集すればvlookpが動くのではないでしょうか?
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問