ExcelVBA表の見出しを付ける方法について

質問

ExcelVBAで複数の表に、同じ見出し行を入れる方法を教えて下さい。

ExcelのSheet1に数字だけで構成された表があります。それぞれ意味があり、例えば「4列は市町村コード」「5列は性別」「17列は年齢」「20列は疾病コード」という感じです。

VBAで市町村・男女別で、年齢と疾病コードに一致した場合にカウントする場合の表は回答をいただき作成できました。

ですが50以上×男女分の表を作るのに疾病コードを

　Range("C3,C136").Value = 14500
　Range("D3,D136").Value = 14510

と、していると、入力間違えも発生しかねないですし、きりがなく、他に良い方法があればご教授いただけましたらと思います。

疾病コードの数字は連番ではないためコピーができません。
疾病コードは「132」あります。

よろしくお願い致します。

回答No.3

soixante · ベストアンサー

＞別のパスワードでロックされているファイルを

このファイルには何が入っているのでしょうか。

＞複数のファイルがあるので

複数のブックにまたがる処理ならば、話はガラリと変わり、そもそものコードが変わってきます。
そもそも元データは、複数のブックに散らばっているのですか？
あるいは、疾病コードの一覧はそのパスワードがかかっているブックに既にあるのですか。

ＶＢＡを勉強中、とのことでしたが、このようなコードを自らで書いたことはありますか。
そうするとお分かりだと思いますが、このようなコードを構築する際には、一番最初に、その大枠というか、枠組みをしっかり組んでから取り掛からねば、正しいものはできません。

最初の質問にもありましたが、「マクロで処理するように指示された」とのことなので、全く分かっていない者にこのような指示を出すことはなかろうと、基本的に理解されていることを前提に回答したつもりです。

最初に構築すべき枠組みとはいろいろあります。

今回のように複数のブックにまたがる処理なのか、単一ブックで済むのか、とか、

繰り返し使用するコードなのか、そうでないのか、
（今回の処理が終わってしまえば用済みなのか、あるいは、来月も同じようなＣＳＶを渡されて処理をするのか。もしそうならば、いちいちモジュールをコピペするのもナンセンスなので、あなたのＰＣのPERSONAL.XLS（＝個人用マクロブック）　にコードを書いたほうが使い勝手はよさそう）

あなただけが使用するコードなのか、他者も使用することがありうるのか、
（他者が使用することもあるならば、想定外の操作をした場合など、エラー発生時の対応や注意喚起なども施さねばならない）

など、いろいろあります。

このサイトでの質問ですので、どうしても断片的な情報から推測して回答しましたので、わたしの説明も拙くて申し訳なかったのですが、2回目に回答したDictionaryオブジェクトのコードは、あくまで、132種類の疾病コードの一覧をお持ちではないということを前提に、それを抽出することを目的としています。

Sheet100に書き出したのがそれです。
それが抽出できたら、あとはそれをSheet2以降の集計シートにコピペすれば済むかと思ったのです。

仮に市区町村コードが50種類あったとします。
Sheet2～Sheet51 は、それぞれの市区町村ごとの集計シート。

それぞれの、B1～EC1 と　B134～EC134 に、疾病コードの一覧をコピペすれば、集計シートのフォーマットは完成します。
（それと、縦軸には年齢ですね）

なので、ここはご自身で対応いただけるかと思ったので割愛しました。
シートインデックス（２～51）で、For Next で回せばできますよね。

ただ、このコードもどう書くか皆目見当がつかない、ご自身でままならない、ということですと、残念ながらちょっと厳しいと思うので、この指示を出した上司に報告してください。

************************************************************************
いちおう私の理解では以下のことをされたいのだと思います。

契約者の情報が入っている3万近いレコード。
これがシート１に入っている。
市区町村ごとにシートを分け、それぞれの市区町村ごとに、男女別に分けたうえで、縦軸：年齢、横軸：疾病コードでマトリクス状に集計したい。

そのためには、

シート２以降に、縦軸、横軸を設けた入力フォーマットを作成する必要がある。
132種類ある疾病コードの一覧がないので、それを作成し、シート2以降にコピペして、集計シートを作る。

そのあとに、最初の回答で書いた、集計マクロを実行する。

という流れかと。

なので、他ブックは関連しないと思っていました。

もう少し詳しい記載が必要です。

一部分だけしかお力になれてなくて申し訳ありませんが、いまいちど、コードを書く前に必要事項を整理してみてください。

お礼

お礼が遅くなりまして申し訳ありません。自力で解決できました。ありがとうございました。データーとして何が入っているとか、どうしてVBAに精通していない私がやらなくてはいけないのかは、いろいろ事情があります。ですが，こういった場で全て書ければ苦労はしないで済むと思います。事情を察してただけますと幸いです。いろいろと助言下さりありがとうございました。

回答No.2

soixante

＃１です。
すみません、先ほどの回答、一部訂正です。

＞それを、各集計シートの見出しとして、B1～EC1 および　B134～EC264　にセットしたいということかと理解しました。

誤）B134～EC264　
正）B134～EC134

お礼

ありがとうございました！動作させてみました。 ですが、現在のコードだと同じシート内で行と列に疾病コードが表示されてしまいます。なので、行だけに表示させる方法はありますか？
Sheet100にセットした値が他のSheetにコピーされるわけではないのですね。初めに回答いただいたお礼に書かせていただきましたが、どこかのファイルのシートを参照する方法はありますか？
でも、このコードはとても助かりました！古い分類だと今の疾病コードにはないものがあるので、それが探せそうです！

回答No.1

soixante

前回に続き回答します。
ちょっといくつか不明点があります。

Sheet1が元データのシートとして、Sheet2以降に集計するシートを作る、ということかと思います。
集計用シートは、縦軸に年齢、横軸に疾病コード、という前回質問と同様でよいのでしょうか。

この私の認識が正しければ、集計用シートは市町村コード別に、市町村コードの数だけ作る、ということかと思うのですが、「50以上」としているのは、市町村コードの数（＝種類）ということでしょうか。

今回の質問文としては、3万近いデータ（レコード）から、132種類ある疾病コード（連番ではない）ものの一覧を作り、そして、それを、各集計シートの見出しとして、B1～EC1 および　B134～EC264　にセットしたいということかと理解しました。

だとすれば、まず、疾病コードの重複のないリストを作り、それを所定のところにセットすればよいです。
元データがSheet1、重複なしリストを書き出すのをSheet100とします。
Sheet1の20列（＝Ｔ列）に疾病コードがあるとします。

*********************************************************************************
Sub xxx()
Dim r As Long, i As Long
Dim LstRow As Long
Dim Dic As Object, Mykeys As Variant, MyItem As Variant
Dim Rng As Range
Dim Scode As Long
Dim Ws1 As Worksheet, Ws100 As Worksheet

Set Ws1 = Worksheets("sheet1")
Set Ws100 = Worksheets("sheet100")
Set Dic = CreateObject("Scripting.Dictionary")

r = 2
Do While Ws1.Cells(r, 20).Value <> ""
Scode = Ws1.Cells(r, 20).Value
If Not Dic.exists(Scode) Then
Dic.Add Scode, Scode
End If
r = r + 1
Loop

Mykeys = Dic.keys

For i = 0 To Dic.Count - 1
Ws100.Cells(i + 2, 1).Value = Mykeys(i)
Next i

Ws100.Select
LstRow = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Range(Cells(2, 1), Cells(LstRow, 1))
Rng.Sort _
Key1:=Cells(2, 1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=True, _
Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin

Rng.Copy
Cells(1, 2).PasteSpecial Transpose:=True
Application.CutCopyMode = False

Set Dic = Nothing
Set Ws1 = Nothing
Set Ws100 = Nothing

End Sub
*********************************************************************************
（簡単に補足）
変数r,i を整数型で宣言
変数Lstrow を整数型で宣言
変数Dicをオブジェクト、Mykeys、MyItemをバリアント型で宣言
変数RNgをレンジ型で宣言
変数Scodeを整数型で宣言
変数Ws1,Ws100 をワークシートで宣言

Ws1にSheet1をセット
Ws100にSheet100をセット
CreateObject関数で、ディクショナリオブジェクトDicを作成する

r は２　（＝レコードの2行目から開始）

Ws1のＴ列が空白でない間はループさせる
　　Scode は、Ｔ列の値
　　もし、Dic に　このScode が存在しない場合、
　　　　キーにScode、アイテムにScode を追加する

　　r に1を足して
ループ

Mykeys に、Dic　のキーをセット

0から（キーの数ひく１）回繰り返す
　　Ws100 のＡ２から下へ、キーを書き出す
繰り返し

Ws100を選択
LstRowに、最終行を取得する
Rngに、A2から下へ最終行までの範囲を設定
この範囲を昇順並び替え

Rngをコピー
B2セルに、行列を入れ替えて貼付け
カットコピーモードの解除

変数の開放
終わり。
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

Dictionaryオブジェクトに関しては以下を。
http://officetanaka.net/excel/vba/tips/tips80.htm

書き出すところでi=0 to Dic.Count-1 としているのは、配列の要素は0から始まるからです。
5個のデータがあれば、Dic(0)～Dic(4)に入っていることになりますので、Dicの要素数マイナス１です。

上記でSheet100のA列に、疾病コードの重複しないリストが出来上がります。
おそらく疾病コードは順不同というのも実用的でないので勝手に昇順並び替えしました。

そのうえで、B2～EC134に疾病コードを振る（＝横に設ける）と思ったので、行列入替での貼付けをしました。

市町村コードの数だけ作るならば、この範囲をFor-Nextで回して貼り付けるのでよいと思います。
その前に、市町村コードの重複しないリストを作るならば上記と同じ方法を取ればよいです。

そのリストを基に、For-Nextで回しながら、シート名に市町村コードを振り、同時に、A1セルにもそのコードを振れば楽でしょう。

シート名を市町村コードでなく、市町村名にするならば、別のシートにその対応表を作って対応すればよいと思います。

以上です。

もしご意向と異なるようでしたら申し訳ありません。

お礼

再びありがとうございます。何とお礼を申し上げて良いのやら…まだ試していないのですが、多分、うまく動くと思います。よろしければ1点追加でアドバイスを頂ければ幸いなのですが、Sheet100の代わりに、別のパスワードでロックされているファイルを開かずに参照することは可能でしょうか？お忙しい中恐縮ですが、もし可能であればアドバイス頂けますと幸いです。複数のファイルがあるのでモジュールだけコピーしてマクロ実行でシートが生成されるようにできれば…と思っているため、このような欲を出してしまいました。ご気分を害されてしまいましたら申し訳ありません。