EXCEL VBA COLLECTIONオブジェクトについて教えてください。

質問

教えてください。

EXCELで3万件の商談情報があり、その情報に顧客情報をセットするようにマクロを作成しています。

顧客情報にある顧客コードをキーにしてCollectionオブジェクトを作成し、商談情報にある顧客コードを元にセットしているのですが、この顧客コードが英数の大文字と小文字が混在しています。

例えば、60000AABBと60000AaBbは違う顧客コードということになり、Collectionオブジェクトのキーに指定するとエラーとなります。
Collectionオブジェクトのキーを大文字・小文字を区別する方法はありますか？

もし、区別する方法がなければ、Collectionオブジェクト以外に何か良い方法はありますか？

回答No.5

n-jun · ベストアンサー

n-junです。

ご参考程度として新規Ｂｏｏｋで試してみて下さい。

Sub try_next()
Dim myDic As Object
Dim s1 As String, s2 As String
Dim s1t As String, s2t As String
Dim s1n As String, s2n As String
Dim r As Range

Set myDic = CreateObject("Scripting.Dictionary")

s1 = "60000AABB": s1t = "鹿児島店": s1n = "001-0789-0123"
s2 = "60000AaBb": s2t = "神奈川店": s2n = "001-0123-4567"

'顧客コードをキーに、顧客名と電話番号を項目として
myDic.Add s1, Array(s1t, s1n)
myDic.Add s2, Array(s2t, s2n)

'Ａ１～Ａ２に顧客コードを代入
Range("A1").Resize(2).Value = Application.Transpose(Array(s1, s2))

'Ａ列の顧客コーｔどに対して、Ｂ・Ｃ列に顧客名と電話番号を代入
For Each r In Range("A1:A2")

r.Offset(, 1).Resize(, 2).Value = myDic.Item(r.Value)

Next

Set myDic = Nothing

End Sub

例えばこんな感じでしょうか？

お礼

すごいです!!!!!

思ったとおりの動きをしてくれました。
何ヶ月も悩んでいたのですが、すっきりしました。

早速使用させて頂きますm(_ _)m

回答No.6

imogasi

FSOなどでＤｉｃｔｉｏｎａｒｙを使う方法が出来るわけですが、そういう高等な（初心者には内部の仕掛けが不明な）ものを使わずに
顧客コードは大文字小文字・全角半角などを強制的に統一した（修正）顧客コードを作る
修正顧客コードで商談情報データと顧客情報データをソートする。
顧客コードをマッチングキーにして、マッチングのロジックで情報を統合すするプログラムを組む。
（注）マッチングのロジックは基本情報処理試験やアルゴリズムの教科書を見てください。
総体的な処理完了時間が少ない（早い）はずです。

補足

ありがとうございます。

ただ・・・顧客情報と商談情報はセールスフォース(SFA)を使用していて、そこからデータを抽出してエクセルにて処理しています。
セールスフォースの方でも大文字・小文字の制御を問い合わせてもらったのですが、基本的に制御はおこなっていないということで回答されました。

各販売会社と共有しているシステムツールなので、こちらで勝手にコードを修正することができません。

処理時間が早いというのは、とても魅力的なので、使用できる方向性がないか、検討してみたいとおもいます。

回答No.4

bluecampus

勘違いしていました。
No.1さんのDictionaryオブジェクトでもよいし、
単にエラーをキャッチすればいいだけのような気もします。

回答No.3

n-jun

n-junです。

違う顧客コードとしては、いけなかったんですね。
であれば＃１はスル～して下さい。

回答No.2

bluecampus

登録時に大文字で登録
キーの指定時に大文字に変換したものに

とすればいいのでは？

補足

言葉足らずですみません。

これは、大文字・小文字違いの顧客は別の顧客になります。
従って、別の顧客として情報をセットしたいので、大文字に変換することができないのです・・・

回答No.1

n-jun

Excel(エクセル) VBA入門：Dictionaryオブジェクトを利用する
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_dictionary.html

一例：
Sub try()
Dim myDic As Object
Dim s1 As String, s2 As String

Set myDic = CreateObject("Scripting.Dictionary")

s1 = "60000AABB"
s2 = "60000AaBb"

myDic.Add "60000AABB", Empty 'キー：60000AABB、項目：Empty

Debug.Print myDic.Exists(s1), myDic.Exists(s2) '判定結果

Set myDic = Nothing

End Sub

等は如何でしょう？

補足

ありがとうございます。
Dictionaryオブジェクトを利用すれば、思った処理ができそうです。

で、もうひとつ質問ですが。
キーに対してセットしたい項目が複数ある場合は、どのように処理すればいいですか？

【顧客情報】
顧客コード　　　顧客名 　　　　　　連絡先
6000AaBb 　　神奈川店　　　　　　001-0123-4567
6000AABB 　　鹿児島店　　　　　　001-0789-0123

この顧客情報の顧客名と連絡先を　　　　　　↓↓↓↓↓↓↓↓ここにセットしたい。
【商談情報】
顧客コード 　商品 　　　売上日 　　　　　顧客名 　　　　　　連絡先
6000AaBb 　オレンジ 　2009/5/1 　　　　　神奈川店　　　　　001-0123-4567
6000AABB アップル 　2009/5/15 　　　　　鹿児島店　　　　　001-0789-0123
6000AaBb 　アップル 　2009/5/25 　　　　　神奈川店　　　　　001-0123-4567

この場合、Dictionaryオブジェクトで顧客情報を作成すると思うのですが、キーは顧客コードで、項目を複数（顧客名と連絡先）にするにはどうしたらいいですか？（そんなことができますか？）