Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


2429 / 13645 ツリー ←次へ | 前へ→

【68071】検索して一致した隣の列を返す ののか 11/1/28(金) 17:02 質問[未読]
【68073】Re:検索して一致した隣の列を返す UO3 11/1/28(金) 17:34 回答[未読]
【68075】Re:検索して一致した隣の列を返す UO3 11/1/28(金) 17:50 回答[未読]
【68085】Re:検索して一致した隣の列を返す ののか 11/1/31(月) 11:16 質問[未読]
【68086】Re:検索して一致した隣の列を返す UO3 11/1/31(月) 12:57 回答[未読]
【68087】Re:検索して一致した隣の列を返す ののか 11/1/31(月) 13:29 質問[未読]
【68088】Re:検索して一致した隣の列を返す ののか 11/1/31(月) 13:42 お礼[未読]
【68076】Re:検索して一致した隣の列を返す UO3 11/1/28(金) 17:56 回答[未読]
【68084】Re:検索して一致した隣の列を返す ののか 11/1/31(月) 8:56 お礼[未読]

【68071】検索して一致した隣の列を返す
質問  ののか  - 11/1/28(金) 17:02 -

引用なし
パスワード
   いつもお世話になってます。
初歩的なプログラムで申しわけありません。
要するにVLOOKUPをプログラム化したいです。
ですが、VLOOKUPだと昇順に並べ替える必要があるため、
ループさせる必要があるかと思います。

条件です。
シート名 レポート のJ列に取引先コードがあります。
シート名 マスタ  のA2〜A1000列に取引先コード
シート名 マスタ  のB2〜B1000列に取引先名があります。
マスタは空白もありますが、今後増えてもいいように1000までとってます。
レポートとマスタの取引先コードが一致すれば、レポートK列に取引先名を返すというプログラムです。
それをデータがあるまで繰り返す感じです。
宜しくお願いします。

【68073】Re:検索して一致した隣の列を返す
回答  UO3  - 11/1/28(金) 17:34 -

引用なし
パスワード
   ▼ののか さん:
>ですが、VLOOKUPだと昇順に並べ替える必要があるため、
>ループさせる必要があるかと思います。

とも限らないですね。通常は指定しない第4引数にFalseを指定すれば
昇順じゃなくても実行できます。
ただ、関数を使うにせよ、VBAでコードを準備するにせよ、検索系の
処理は関連のデータが昇順になっているほうが、処理効率は格段に向上
しますが・・・

皆さんから様々なコードが提示されると思いますが、あえて(?)
VLOOKUPを埋め込む案です。

Option Explicit

Sub Sample1()
  Dim x As Long
  With Worksheets("マスタ")
    x = .Range("A" & .Rows.Count).End(xlUp).Row
  End With
  With Worksheets("レポート")
    With .Range("K1").Resize(.Range("J" & .Rows.Count).End(xlUp).Row)
      .Formula = "=VLOOKUP(J1,マスタ!A$2:B$" & x & ",2,False)"
      .Value = .Value
    End With
  End With
End Sub

【68075】Re:検索して一致した隣の列を返す
回答  UO3  - 11/1/28(金) 17:50 -

引用なし
パスワード
   ▼ののか さん:

一応、ループの中でVLOOKUPを使うものもアップしておきます。

Sub Sample2()
  Dim x As Long, z As Long, k As Long
  Dim c As Range
  Dim v() As Variant
  Dim tbl As Range
  
  With Worksheets("マスタ")
    Set tbl = .Range("A2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
  End With
  With Worksheets("レポート")
    z = .Range("J" & .Rows.Count).End(xlUp).Row
    ReDim v(1 To z, 1 To 1)
    For Each c In .Range("J1").Resize(z)
      k = k + 1
      v(k, 1) = Application.VLookup(c.Value, tbl, 2, False)
    Next
    .Range("K1").Resize(z).Value = v
  End With
  
  Set tbl = Nothing
  
End Sub

【68076】Re:検索して一致した隣の列を返す
回答  UO3  - 11/1/28(金) 17:56 -

引用なし
パスワード
   ▼ののか さん:

もう1つだけ。
なんとなくVBAらしい(?)コードを。

Sub Sample3()
  Dim dic As Object
  Dim z As Long, k As Long
  Dim c As Range
  Dim v() As Variant
  
  Set dic = CreateObject("Scripting.Dictionary")
  
  With Worksheets("マスタ")
    For Each c In .Range("A2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
      dic(c.Value) = c.Offset(, 1).Value
    Next
  End With
  
  With Worksheets("レポート")
    z = .Range("J" & .Rows.Count).End(xlUp).Row
    ReDim v(1 To z, 1 To 1)
    For Each c In .Range("J1").Resize(z)
      k = k + 1
      v(k, 1) = dic(c.Value)
    Next
    .Range("K1").Resize(z).Value = v
  End With
  
  Set dic = Nothing
  
End Sub

【68084】Re:検索して一致した隣の列を返す
お礼  ののか  - 11/1/31(月) 8:56 -

引用なし
パスワード
   U03さま
返信遅くなって申しわけありません。ありがとうございました。
どれかを参考にさせていただきます。
本当に有難うございました。

【68085】Re:検索して一致した隣の列を返す
質問  ののか  - 11/1/31(月) 11:16 -

引用なし
パスワード
   Sub Sample2()
  Dim x As Long, z As Long, k As Long
  Dim c As Range
  Dim v() As Variant
  Dim tbl As Range
  
  With Worksheets("マスタ")
    Set tbl = .Range("A2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
  End With
  With Worksheets("レポート")
    z = .Range("J" & .Rows.Count).End(xlUp).Row
    ReDim v(1 To z, 1 To 1)
    For Each c In .Range("J1").Resize(z)
      k = k + 1
      v(k, 1) = Application.VLookup(c.Value, tbl, 2, False)
    Next
    .Range("K1").Resize(z).Value = v
  End With
  
  Set tbl = Nothing
  
End Sub


で実行しましたが、アルファベットが含まれるコードは入るのですが、
数字だけのコードが#N/Aのエラー値が返されます。
どうしてでしょうか?

【68086】Re:検索して一致した隣の列を返す
回答  UO3  - 11/1/31(月) 12:57 -

引用なし
パスワード
   ▼ののか さん:

こんにちは

こちらでは、英数字混在、数字だけ いずれも、マスタに登録あれば
レポートシートに反映します。
#N/Aとなった数値コードですが、マスタ、レポート、いずれも【全く同じもの】でしょうか?
・たとえば一方が02000、一方が2000 であればアンマッチになります
・また一方が全角、一方が半角でもアンマッチ。
・たとえば一方が2000b(bは空白)、一方が2000でもアンマッチ。

【68087】Re:検索して一致した隣の列を返す
質問  ののか  - 11/1/31(月) 13:29 -

引用なし
パスワード
   レポートとマスタのは同じものですが、計算式の入ったセルです。
計算式と言っても、7桁コードを右4桁表示【=RIGHT(H2,4)】の計算式が
入った列のセルとマスタのコードとを比べています。
英字混在や0542などはOKですが、1473や3077などの4桁数字のみがやはりエラーに
なります。なにか初歩的な所で間違っているのでしょうか・・・?
エクセルのバージョンが2000では古いのでしょうか?

【68088】Re:検索して一致した隣の列を返す
お礼  ののか  - 11/1/31(月) 13:42 -

引用なし
パスワード
   解決しました。
なぜかわかりませんが、マスタをもう一度再入力して試した結果、
マッチするようになりました。再入力しなかったところはエラーになります。
見た目にはわからないですが、不思議です・・・。
お騒がせしました。
有難うございました。

2429 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free