連想配列が１つのセルにまとめれません！ 500行500例の値があります！ 一行に何の値が何件あるか集

連想配列が１つのセルにまとめれません！

500行500例の値があります！
一行に何の値が何件あるか集計したいのです。

A列に一行一行 1が1件2が２件3が３件4が４件5が5件
↓の行A列に 10が1件20が2件30が3件40が4件50が5件
以下この処理を500行を左端のA列にまとめて置きたいのです。


ご教示頂けたら幸いです。

下のソースだと、A列にまとめれません！
よろしくお願いいたします。

ソース
Sud countNumbers()
Dim i As Integer
Dim sline As Integer

sline = 1
Set sd = CreateObject(″Scripting.Dictionary″)

Application.ScreenUpdating= False

For i = 5 To 500
sNo=Worksheets(1).Cells(sline,i).Value
sd(sNo) = sd(sNo) ＋ 1

Next

Application.ScreenUpdating = True

i = 1
For Each sn In sd
Worksheets(1).Cells(1,i).Value = sn & ″が″ & sd(sn) & ″件″

i = i + 1
Next
Set sd = Nothing

End Sub

この値を配列に貯めて、１つのセルに参照したいのですが！
上手くいきません…

宜しくお願いします。

No.5 ベストアンサー 回答者： めぐみん_ 回答日時： 2017/11/07 14:43

コードから推測してデータはＥ列以降を使用し、且つＥ１～Ｅの最終行までのカウントを

Ａ列の同行に書き出すものと推測します。

Sub try()
　Dim myDic As Object
　Dim key As Variant
　Dim st As String
　Dim r As Range, rr As Range
　Dim v As Variant

　Set myDic = CreateObject("Scripting.Dictionary")

　Application.ScreenUpdating= False

　For Each r In Range("E1", Cells(Rows.Count, 5).End(xlUp))
　　For Each rr In Range(r, Cells(r.Row, Columns.Count).End(xlToLeft))
　　　If Not myDic.Exists(rr.Value) Then myDic.Add rr.Value, Array(rr.Value, "が", 0, "件")
　　　　v = myDic(rr.Value)
　　　　v(2) = v(2) + 1
　　　　myDic(rr.Value) = v
　　Next

　　st = ""

　　For Each key In myDic.Keys
　　　st = st & " " & Join(myDic(key), "")
　　Next
　　r.Offset(, -4).Value = Trim(st)
　　myDic.RemoveAll
　Next

　Application.ScreenUpdating = True

　Set myDic = Nothing
End Sub

ご参考になれば。

この回答へのお礼

すいません、ありがとうございます。今週ためしてみます！

図々しいのですが参考書のオススメってどういったのがいいかご存知でしょうか？

お礼日時：2017/11/08 01:22

No.4 回答者： めぐみん_ 回答日時： 2017/11/04 19:01

＞１つのセルに参照したい

改行して５００行の長いセルA1を作るのには、その後に意味があるのでしょうか？
もしないなら集計元のデータのある同じ行のA列に値を入れていってはと思います。

この回答へのお礼

書き方がかなりごちゃごちゃになってしまい、分かりずらかったようです。すいません！

やりたい作業は、作業台を使わずに！

列に商品名

１行１行に店舗があり。

１商品に何のロット数が何件あるかを知りたいのです。

お礼日時：2017/11/07 12:31

No.3 回答者： WindFaller 回答日時： 2017/11/04 18:17

サンプルデータを見ていないからはっきり言えませんが、内容の印象からすると、度数を求めているように思えます。

まあ、Dictionary オブジェクトはあえて使いたいならともかく、それほど使う必要性はあるとは思えません。

Excelをお使いのようですから、COUNTIF で計算してしまえばよいと思います。
例

度数
0　 分布度数は任意
20
40

B2
数式　　iData : 名前登録で、A5:～データの末尾まで

=COUNTIF(iData,"<="&A3)-COUNTIF(iData,"<"&A2)

No.2 回答者： めぐみん_ 回答日時： 2017/11/04 06:08

あと気になるのは

＞ 1が1件2が２件3が３件4が４件5が5件

取得した値を昇順に並び替える必要の有無ですね。
VB.NETならSortedDictionary クラスがありますので気にはしませんが、
Dictionary オブジェクトではそれは無理になります。

どうしても順序を並べ替えるというのであれば、その部分を追加になるでしょう。

No.1 回答者： めぐみん_ 回答日時： 2017/11/03 05:21

＞左端のA列にまとめて置きたいのです。

セルの内容を書き換えてしまいたい？
⇒同一のSheetを指定してますよね？
それに書き出す前に現在あるデータをクリア（削除）してないですし。
A列に纏めると書きながら

＞Worksheets(1).Cells(1,i).Value

列は変更してますよ？
これだとA列ではなく『１行目に』書き出すって事になりますが？


基本的にDictionaryに『そのキーが既に存在するか存在しないか』で、
処理は変わりますよ。
あとDictionaryに放り込む際には

＞Application.ScreenUpdating= False

は、いらないでしょ。
通常Sheetに書き出す際に止めたいのですし。

＞For Each sn In sd

色々変数の宣言を飛ばしている点も気になりますけど、
DictionaryからKeyを取り出すなら sd.Keys ではないかな？


元のSheetの状態と期待する結果のSheetの状態がわかれば、
さほど問題ではないように思います。