質問編集履歴

2 画像を分かりやすく変更し、問題点の説明も分かりやすく追加しました

退会済みユーザー

退会済みユーザー

2019/11/12 23:39  投稿

ExcelVBAでVlookupを使って値を取得する際、検索範囲の中にスペースが入っていると正しく取得されない
お世話になっております。
現在、ExcelVBAの勉強をしているのですが以下の問題で色々と考えております。
### 【やりたいこと】
**①Sheet1**
![イメージ説明](230ecbcd711e7d697bb0ab08ae93170d.png)
**②Sheet2**
![イメージ説明](74114b4722e29d56ca45f4ef054a0281.png)
![イメージ説明](acbbf583c831a3ab23600d3a3c8cf485.png)
**③結果**
![![イメージ説明](91ed977dfebae9d47458a700bc75d12f.jpeg)説明]
### 【プログラム】
1:①のSheet1のA列に「Total」を結合させた文字列を作成
2:1の文字列をキーにしてVlookupでSheet2のA列を探し、見つかったらB列の値を①のD列にセット
### 【悩んでいること】
![イメージ説明](4af72591f2166e9ca89416b9ef8be0b5.jpeg)
上記A列に「Total」を結合させた文字列をメインキーとして、
下記の図A1~B6の範囲をVlookupの検索範囲としたいが、
A列にスペースが入っているためきちんと参照できていない
![![イメージ説明](da7007a510ccb26ff63ccd2ffdd3f4da.jpeg)
![イメージ説明](934fa9f403bb5f579bc85b6b7784241f.png)
### 【コード】
```ここに言語を入力
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つなど色々なパターンがあります)
ワイルドカードを使えば?とおっしゃる方もいると思いますが、今回は**検索範囲をあらかじめ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  投稿

ExcelVBAでVlookupを使って値を取得する際、検索範囲の中にスペースが入っていると正しく取得されない
お世話になっております。
現在、ExcelVBAの勉強をしているのですが以下の問題で色々と考えております。
### 【やりたいこと】
**①Sheet1**
![イメージ説明](230ecbcd711e7d697bb0ab08ae93170d.png)
**②Sheet2**
![イメージ説明](74114b4722e29d56ca45f4ef054a0281.png)
**③結果**
![![イメージ説明](91ed977dfebae9d47458a700bc75d12f.jpeg)説明]
### 【プログラム】
1:①のSheet1のA列に「Total」を結合させた文字列を作成
2:1の文字列をキーにしてVlookupでSheet2のA列を探し、見つかったらB列の値を①のD列にセット
 
### 【悩んでいること】  
![イメージ説明](4af72591f2166e9ca89416b9ef8be0b5.jpeg)  
 
上記A列に「Total」を結合させた文字列をメインキーとして、  
下記の図A1~B6の範囲をVlookupの検索範囲としたいが、  
A列にスペースが入っているためきちんと参照できていない  
![![イメージ説明](da7007a510ccb26ff63ccd2ffdd3f4da.jpeg)  
### 【コード】
```ここに言語を入力
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列にスペースがない場合は問題なく動きます**
しかし、今回はSheet2のA列にスペースが入っているのでちゃんと動きません。
(スペースは全角、半角、または半角を2つなど色々なパターンがあります)
**ワイルドカードを使えば?とおっしゃる方もいると思いますが、今回は検索範囲をあらかじめsubTblにセットしているのでどうやればいいのかも分かりません。**
ワイルドカードを使えば?とおっしゃる方もいると思いますが、今回は**検索範囲をあらかじめsubTblにセットしている**のでどうやればいいのかも分かりません。
subTblに対してスペースを削除できればいいのですが、subTblはA列だけでなくB列も入っているのでできないようです。
Vlookup関数を動かす前にあらかじめSheet2のA列からスペースを削除しておけばいいのだと思いますが、どうやればいいのかなと悩んでいます。
どうやればSheet2のA列のスペースを削除した上でVlookup関数を動かすことができるのでしょうか?
ちなみに、別の列にあらかじめスペースを削除した値を貼り付けるというのはしないものとします
ちなみに、**別の列にあらかじめスペースを削除した値を貼り付けるというのはしないものとします**
どなたかご存知の方教えていただけると幸いです。
それではどうぞよろしくお願いいたします。
  • VBA

    5530 questions

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

  • Excel

    4456 questions

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

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る