Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
Matchの検索範囲を可変に
投稿日時: 22/01/14 10:18:35
投稿者: rinahana

いつも お世話になっています。拙いVBAを使って、何とか地域の役員会で利用・活用しています。
役員の人数が辞めたり、入ったりと大変不規則に増減します。当初はRange("A2:A1000")ほどに設定して
いたのですが、今は定員の48をコードにしています。これを”役員表”のA列の最端行をみて、コードにできないでしょうか?
教えて下さい。これをもとに色々な計算(出席率、会費支払い額など)をしています。
シート関数ではoffset関数を使って可能であることは、ネットで勉強しましたが
--------------------------------------------------------
Set match_data = Sheets("役員表").Range("A2:A48")
〜〜〜
〜〜〜
m = Application.WorksheetFunction.Match(koreda, match_data, 0)
---------------------------------------------------------
宜しく お願いします。

回答
投稿日時: 22/01/14 10:49:44
投稿者: taitani

VBA を使うより、テーブルを使用するか、範囲を名前で設定して関数を利用する方がよいでしょう。
どうしても、VBA を利用するなら、範囲を名前で設定してから、
Set match_data = Sheets("役員表").Range("名簿")
とかでしょうか。
※ A2:A48 を選択して、名簿 と名前を設定した場合。

回答
投稿日時: 22/01/14 11:07:02
投稿者: 半平太

こう言うのは定番化したものが色々あります。
 
>Set match_data = Sheets("役員表").Range("A2:A48")
  ↓
  With Sheets("役員表")
      Set match_data = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
  End With

回答
投稿日時: 22/01/14 11:31:51
投稿者: WinArrow
投稿者のウェブサイトに移動

役員表のA列のセル範囲(可変)に「名前定義」する方法
 
名前の定義
名前(N):役員名簿
範囲(S):役員表
参照範囲(R):=OFFSET(役員表!$A$1,1,0,COUNTA(役員表!$A:$A)-1,1)
これで、データ件数に対応して可変になります。
 
使い方例:
データ入力の場面で
役員名を選択するセルに入力規則を設定すれば、
「入力する」手間を「選択する」手間に変更できます。
 
「役員名を入力させ、それを検索キーとして名簿を検索し、行位置を取得する」操作が

入力規則で選択
になります。
 
 
入力規則の例
入力値の種類:リスト
元の値:=役員名簿
 
 
 
 

回答
投稿日時: 22/01/14 11:33:02
投稿者: Suzu

rinahana さんの引用:
これをもとに色々な計算(出席率、会費支払い額など)をしています。

 
役員 の 変動ごとに 数式を変えたり、VBA を使うよりも
リレーションシップを設定し、ピボットテーブルを使ってはどうでしょうか。
 
Excel ピボットテーブル をキーワードにWEB検索を行えば 使い方は出てきます。
 
Ex
-----------------------------------------------------------------------------
テーブル1
ID	名前
1	い
2	ろ
3	は
4	に
5	ほ

 
テーブル2
FLD1	FLD2	FLD3	FLD4
1	1月10日	1	1
2	1月10日	2	10
3	1月11日	1	100
4	1月13日	2	1000
5	1月14日	2	10000
6	1月15日	2	100000

 
FLD3 の値が テーブル1の ID を表し、リレーションを設定の上、ピボットテーブルを使えば
 
FLD1	FLD2	FLD3	名前	集計
1	2022/1/10	1	い	1
2	2022/1/10	2	ろ	10
3	2022/1/11	1	い	100
4	2022/1/13	2	ろ	1000
5	2022/1/14	2	ろ	10000
6	2022/1/15	2	ろ	100000

 
こんな結果を得られます。
 
ピボットテーブルですから
合計 / FLD4	列ラベル		
行ラベル	い	ろ	総計
2022/1/10	1	10	11
2022/1/11	100		100
2022/1/13		1000	1000
2022/1/14		10000	10000
2022/1/15		100000	100000
総計	101	111010	111111

こんな事は朝飯前ですね。

投稿日時: 22/01/14 12:42:37
投稿者: rinahana

taitaniさん、半平太さん、 WinArrowさん、Suzuさん ありがとうございます。
色々なご教授有難うございました。
出来ない事あると、それしか考えないので視野が狭くなることに気づかされました。