EXCEL・ＶＢＡで、範囲名を参照したい。

質問

またまたお世話になります。
タイトルの通りなのですが、ＥＸＣＥＬで作成している出勤表（sheet毎に月別にし、縦軸に社員名、横軸に日付を入れてあります。社員名は同一のブックの別シートにまとめてあり、参照で各月に表示してあります。）で、出勤した日に”１”を入れるのですが、今は手で月ごと、社員名毎に範囲名を作成し、一ヶ月分をまとめて入力しています。

今はまだ40名程なので、月が変わった時に範囲名を手で付け替えているのですが、人数が増えてきたり、入力担当者が変わったりした時に、この方法は大変になってくると思います。

そこで、現在の範囲名登録状況を確認し、（配列で良いと思います。）社員名と照合し、選択シートに自動的に割り振るように出来たら良いと思い、挑戦しているのですが、『現在の範囲名登録状況を確認』するところが、検索しても中々出てこなくて困っております。

予め、何という『範囲名』が登録されているか分からない、それを取得したい、といった場合、そういうことは可能なのか、範囲名を付け替えるには、DIMにREDIMがあるように一つのコマンドで可能なのか、それとも、削除→シート変更→作成を繰り返さなければ不可能なのか、また、方法は在るのか等知りたいです。よろしくお願い致します。

回答No.3

imogasi

質問を全く誤解していたらすみません。
質問者の（仕事関連）業務（人事・勤怠）で、名前定義を使っているらしいが、このアイデアは一般的でないと思うので、少し具体的に質問で例をあげて説明してもらわないと、読者にわからない。質問者は、自分の聞いていることだけに答えろと、なりがちだが、そのやりかたがベターかどうか、読者には納得できない。他社か自社のシステムの経験者がそういう方法を選んだのか。
それにインプットの質問らしいが、インプットに使う原書（出勤状況記録書類）はどんなものか書いてないのは片手落ちだろう。
ステップを踏んで文章化してみてください。
年月を指定すると、特定（年月別？）のシートが開き、その社員の出勤状況コード１を入れるセルは、どうして探しているのか？目視で探すのか？普通は簡単な方の社員コードを入れたりするが、（社員数が多くてコードが多くて覚えきれない場合は読み仮名先頭を入れて、該当の氏名を見て人間が選択するとかするように思うが？
こういうのはＶＢＡを使うとか、Ａｃｃｅｓｓ程度のデータベースを使うなどが必要になるように思うが、そういうのは使ってないのか？
私なら
（１）その業務を選択。その入力（基準）年月を入力
（２）シートを割り出し、そのシートをＳＥＬＥＣＴ
（３）（Ａ）シートの列の氏名から探す
　　　（Ｂ）かな市名先頭１，２文字指定＝＝＞候補社員をィストに出すーー＞選択
　　　当該社員の行と列をシステムが探してＳＥＬＥＣＴ
　（４）入力　ドロップダウンからもできるが、１択しかないなら手入力　　　
次月の表はシート追加と数式、見出しなどコピーして作ればよい

回答No.2

ushi2015

こんにちは
どうされたいのか良く分からないのですが、イミディエイトウィンドウに列挙すると、
Sub test()
　　Dim v As Variant
　　For Each v In ThisWorkbook.Names
　　　　Debug.Print "名前：" & v.Name & " :範囲: " & v.RefersTo
　　Next
End Sub
どうなりますか？

補足

ありがとうございます。遅くなってしまい申し訳ございません。
上記を試したところ、
『
名前：_xlfn.COUNTIFS :範囲: =#NAME?
名前：_xlfn.DAYS :範囲: =#NAME?
』
と出た後、
『
名前：社員名１ :範囲: ='１０月'!$F$4:$AJ$4
名前：社員名２ :範囲: ='１０月'!$F$5:$AJ$5
』（社員名１、社員名２は実名です。）

が人数分出ます。

この社員名の部分を”作業用”シートにある≪社員名≫と比較し、存在していれば、”!$F$4:$AJ$4”の部分は変えずにシート名だけを変更し、”作業用”シート上の≪社員名≫が範囲名に存在しなければ、その不足分を追加し、適切に範囲設定をしたいと思います。

この際、月を指定しシートを選択するのではなく、シートを選択した時点で何月が選択されたのかを判断し、自動的に範囲設定が出来ればと思います。
よろしくお願い致します。

回答No.1

ushi2015

こんにちは
範囲名は一定にして、範囲参照数式で、月や社員名で参照先と範囲が
変更されるようにした方がいいのでは？
INDIRECT、COUNTA、MATCH、INDEX 等を駆使して範囲参照数式を
作り上げる感じです。

補足

ありがとうございます。
書き忘れていたのですが、範囲名は社員名とイコールにしてあります。あと、年度毎に別ブックにしてあるので、年度途中で社員が増えることはあっても、減らすことは参照がおかしくなるのでありません。

この場合においても可能ですよね？

例
　1月シート選択時
範囲名Ａ　1月.C4:C35
範囲名Ｂ　1月.D4:D35
範囲名Ｃ　1月.E4:E35

　２月シート選択時
範囲名Ａ　２月.C4:C35
範囲名Ｂ　２月.D4:D35
範囲名Ｃ　２月.E4:E35