質問編集履歴

2 画像を分かりやすく変更し、問題点の説明も分かりやすく追加しました 退会済みユーザー 2019/11/12 23:39 投稿

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

お世話になっております。

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

### 【やりたいこと】

**①Sheet1**

![イメージ説明](230ecbcd711e7d697bb0ab08ae93170d.png)

**②Sheet2**

![イメージ説明](~~74114b4722e29d56ca45f4ef054a0281~~.png)

![イメージ説明](acbbf583c831a3ab23600d3a3c8cf485.png)

**③結果**

![![イメージ説明](91ed977dfebae9d47458a700bc75d12f.jpeg)説明]

### 【プログラム】

１：①のSheet1のA列に「Total」を結合させた文字列を作成

２：１の文字列をキーにしてVlookupでSheet2のA列を探し、見つかったらB列の値を①のD列にセット

### 【悩んでいること】

![イメージ説明](4af72591f2166e9ca89416b9ef8be0b5.jpeg)

上記A列に「Total」を結合させた文字列をメインキーとして、

下記の図A１～B６の範囲をVlookupの検索範囲としたいが、

A列にスペースが入っているためきちんと参照できていない

![~~![イメージ説明](da7007a510ccb26ff63ccd2ffdd3f4da.jpe~~g)

![イメージ説明](934fa9f403bb5f579bc85b6b7784241f.png)

### 【コード】

```ここに言語を入力

Option Explicit

Private Sub Test()

'++++ファイル

Dim wb As Workbook 'ファイルのワークブック格納

Dim ws_MainSheet As Worksheet 'Sheet１シート名格納

Dim strMain_Lastcol As String '最終行格納

Dim ws_SubSheet As Worksheet 'Sheet２シート名格納

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

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列にスペースが入っているのでちゃんと動きません。

（スペースは全角、半角、または半角を２つなど色々なパターンがあります）

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

もしSheet2のA列にスペースがなくて、例えば「1月Total」と言うデータになっていれば

Sheet1のA列に「Total」を結合させた文字列をキーにしてすぐにVlookupができます。

subTblに対してスペースを削除できればいいのですが、subTblはA列だけでなくB列も入っているのでできないようです。

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

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

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

**でもSheet2の方でA列のデータをコピーし、新しくC列に貼り付けて、そこでスペースを削除する、

その後、C列に対してVlookupを使うというのは不可、です。**

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

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

どなたかご存知の方教えていただけると幸いです。

それではどうぞよろしくお願いいたします。

VBA

5530 questions

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

Excel

4456 questions

Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

1 勘違いしている人がいるので、もっと詳しく書きました！ 退会済みユーザー 2019/11/12 18:02 投稿