Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2007)
2つの項目での検索方法
投稿日時: 18/12/25 15:02:07
投稿者: FILETUBE

こんにちは。
1つ教えて頂けないでしょうか。
 
日付  品番 カラー マスターコード
2018/12/25 AA SIL
2018/12/26 AA GOL
 
という内容の入力.xlsmと
 
品番 カラー マスターコード
 AA SIL 365
 AA GOL 145
 
という内容のマスター.xlsxがあります。
 
入力.xlsmで日付を入力ボタンをクリックすると
日付が等しいもののみ、マスターからマスターコードを
セットするようにVBAのコードで処理したいのす。
(以前はINDEX,MATVH関数を教えて頂きました)
 
vDATEは画面で入力した日付になります。
 
Set rng = thisws.Range("A:A").Find(What:=vDATE, LookIn:=xlFormulas)
 
If Not rng Is Nothing Then
   Adres = rng.Address    
 Do
     
  thisws.Cells(rng.Row, 2)
  thisws.Cells(rng.Row, 3)
   
  ※ここでマスター.xlsxからマスターコードをセットする処理を入れたい
    
  Set rng = thisws.Range("A:A").FindNext(rng)
  If rng Is Nothing Then Exit Do
 Loop Until rng.Address = Adres
End If
 
比較は品番、カラーの2つの項目になります。
 
Find(What:では1つの項目ですし、何か良い方法はないでしょうか?
分かる方おられましたら、教えて頂けないでしょうか。
どうぞ宜しくお願いします。
 

回答
投稿日時: 18/12/25 15:34:37
投稿者: WinArrow
投稿者のウェブサイトに移動

2つの項目を比較するのではなく
2つのセルを結合した作業列を用意すれば、いかがでしょうか?
目障りならば、非表示にすれば・・・

投稿日時: 18/12/25 16:35:20
投稿者: FILETUBE

WinArrowさん、いつもありがとうございます。
 
ループしてIF文で比較する方法と、結合して比較項目を作成する以外で
できれば実行したいのですが。
 
申し訳ありません、今一度何か良い方法はないでしょうか?
宜しくお願いします。

回答
投稿日時: 18/12/25 18:37:18
投稿者: WinArrow
投稿者のウェブサイトに移動

>結合して比較項目を作成する
とVLOOKUP関数が使えます。
 
結合する方法では、どのような弊害があるのでしょうか?

投稿日時: 18/12/25 19:59:30
投稿者: FILETUBE

回答ありがとうございます。
マスターの方は実務者が登録する為
なるべくシンプルにと思いまして。
ずっと結合の式を設定しておけばいいのかとも思いますが。
 
結合項目を作成しFindで検索する方法が
1番シンプルでしょうか?

回答
投稿日時: 18/12/25 20:43:24
投稿者: WinArrow
投稿者のウェブサイトに移動

「FIND」は、参照範囲の中のセルを取得するだけです。
お望みのデータは、別のコードで取得することになります。
 
検索は、あいまい検索と同じになるので
 
例えば
マスター側キーが、
AAA1234
AAA123
AAA1
と並んでいる状態で
AAA1
を検索すると、最初の「AAA124」にヒットします。
 
「VLOOKUP」は、一つの命令で、お望みのデータを取得することができます。
ただ、VLOOKUPは配列を伴うので、参照範囲の中のデータの並びを工夫(キーを昇順に)
すれば処理時間を早くすることが期待できます。
 

投稿日時: 18/12/26 08:10:40
投稿者: FILETUBE

WinArrowさん、回答ありがとうございます。
 
Findの件良く分りました。
そこでVLOOKUPですが
VBAのコードの中で記述したことが無く
  
 If Not rng Is Nothing Then
    Adres = rng.Address    
 Do
       
   thisws.Cells(rng.Row, 2)
   thisws.Cells(rng.Row, 3)
     
   ※ここでマスター.xlsxからマスターコードをセットする処理を入れたい
     
   Set rng = thisws.Range("A:A").FindNext(rng)
   If rng Is Nothing Then Exit Do
  Loop Until rng.Address = Adres
 End If
 
の中で記述は出来るのでしょうか?

回答
投稿日時: 18/12/26 10:06:27
投稿者: WinArrow
投稿者のウェブサイトに移動

できますよ
 
マスター側のデザインは、
A列を挿入して
=B2&C2
という数式を入れて文字列結合したキーを作成しておきます。
 
方法は2つあります。
(1)データ側シートの「マスターコード」セルに、数式を代入する方法
(2)データ側シートの「マスターコード」セルに、VBAで取得した値を代入する方法
 
(1)は、マスターコードのセルに、次のような数式をVBAで編集して、代入します。
Cells(行,"D").Formula = "=IF(COUNTIF([マスタ-.xlsx]Sheet1!$A:$A,A2&B2)>0,VLOOKUP(A2&B2,[マスター.xlsx]Sheet1!$A:$D,4,FALSE),"""")"
 
※数式は、ヒントです。
 
(2)は、マスターコードのセルに、次のような数式をVBAでワークシート関数を使って求めた値を、代入します。
If WorksheetFunction.Countif(Workbooks(マスター.xlsx").Sheets("Sheets1").COlumns("A:A"),Cells(行,"B").Value & Cells(行,"C").Value) > 0 Then
    Cells(行,"D").Value = WorksheetFunction.Vlookup(Cell(行,"B").value & Cells(行,"C").value,Workbooks("マスターxlsx").Sheets("Sheet1").COlumns("A:D"),4,False)
End If
 

回答
投稿日時: 18/12/26 10:15:17
投稿者: WinArrow
投稿者のウェブサイトに移動

マスター側のデザイン変更は
手操作であらかじめ作成してもよいし、
VBAの中で、列挿入〜数式代入を実行してもよいです。
VBAで中で編集した場合は、上書き保存せずに閉じれば、実務者に負担はかけることはありません。

投稿日時: 18/12/26 11:17:54
投稿者: FILETUBE

WinArrowさん、ありがとうございました。
VLOOKUPでデータを取得する方法、1つ勉強になりました。
    
  result = Application.VLookup(Cells(i, 2) & Cells(i, 3), myRange, 4, 0)
  If Not IsError(result) Then
     Cells(i, 4) = result
  End If
    
また、よろしくお願いします。