Excel VBA 質問スレ Part77

**デフォルトの名無しさん** (TW 0H42-j7SA) · 2022/07/17(日) 07:07:35.59

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑同じ内容を3行貼り付けること

ExcelのVBAに関する質問スレ
コード書き込みや作成依頼もOK

次スレは>>980が立てること
無理なら細かく安価指定

※前スレ
Excel VBA 質問スレ Part76
https://mevius.5ch.net/test/read.cgi/tech/1651339421/

VIPQ2_EXTDAT: checked:vvvvv:1000:512:: EXT was configured

2022/08/15(月) 18:23:08.68

>>371
ああスマン。なんかへんな一言張り付いた。

2022/08/15(月) 19:15:38.92

Dictionaryでいいと思う。
キーに連番、スタックポインタ代わりに変数一個用意してやって、使い終わったらremove で消して。

2022/08/15(月) 19:36:11.84

やり方は色々あるのにVBAが使いにくいとか言い出す初心者ｗ

2022/08/15(月) 19:39:31.51

だがしかしVBAにDictionaryはないのだよ

WSHはいつまでサポートされるかなぁ
VBAより短い気がしてきた

まあ俺ならどうしてもキューかスタックほしいなら、.NET Frameworkのやつ使うかな

2022/08/15(月) 19:54:05.83

だからVBA使うのやめなよ

2022/08/15(月) 20:17:32.58

VBAでDictionary使えるでしょ

2022/08/15(月) 20:25:53.14

ああ、なるほど。いつ使えなくなるとも知れないとなれば先々を考えて使わないほうがいいかもね。たしかに使い方も特殊だし。>dictionary

まぁ、それなら捨てsheet作って貼り付けるかな。2次元までなら。
そのかわり処理速度は格段に落ちる可能性はあるけど、待ってりゃいいならそれもアリか。

2022/08/15(月) 20:28:32.11

それって findfirst/findnext 一発で済む問題なのでは？いちいちマクロで書くのもウザったいですよね‥‥

2022/08/15(月) 20:41:37.63

使いにくいとか面倒とか愚痴るくらいならEXCEL使うのやめればいいのに

2022/08/15(月) 21:05:11.95

>>371
Dictionaryはとおの昔から使っておったのじゃよ。
しかし使い続けているうちにふと頭をさわるとなんとハゲてしまったのじゃ。
それは困ると思ってDictionaryに変わるいい方法を探求しておるのじゃ。

2022/08/15(月) 21:40:01.71

>>381
なんだ、ただふざけてるだけか。。。

2022/08/15(月) 22:39:37.83

そんなハゲ頭に免じてArrayListを進ぜよう

2022/08/15(月) 22:52:26.96

DictionaryがイヤならCollection使えばいいと思うけど。
まぁ別に普通に配列でもいいんだけどさ。
多少凝った作りにしたって関数化しとけばいい訳だし。

2022/08/16(火) 04:43:22.32

>>384
流石に無知すぎるから少し黙った方がいいよ

**310** (ﾜｯﾁｮｲ 4201-8lLW) · 2022/08/16(火) 05:59:23.35

Collection に Exists メソッドがあれば...

2022/08/16(火) 11:11:29.27

何作ってんだよ

2022/08/16(火) 12:23:01.42

>>385
そうだね
よく分かりもしないで無知とか決めつける無知は
すっこんでればいいよね

2022/08/16(火) 16:58:27.92

あまりマウントの取り合いは止めようぜ
意見交換の場なんだから

2022/08/16(火) 17:40:43.11

どちらかというとけなし合いだな

2022/08/16(火) 18:00:08.72

ここにいると神になった気分になるな
お前らの見苦しさに対してもなんだか優しくなれそう

2022/08/16(火) 18:20:47.96

昔は違ったんよ
いつからか荒れやすいスレになった

2022/08/16(火) 20:19:48.02

>>382
ふざけてるつもりは毛頭ございませぬ。
でもこれからもハーゲマンの名に恥じぬようVBAを探求していきたいです。

2022/08/16(火) 20:50:53.76

毛という毛全部剃っちゃえ！

2022/08/16(火) 21:16:40.24

>>394
アホ。
そしたらハゲてまうやんけ。

2022/08/16(火) 21:54:28.52

え？
別にいいじゃん、アンタ元から禿なんだから。

2022/08/17(水) 01:45:03.16

ソルのはムダ毛だけにしとけ

2022/08/17(水) 01:57:01.99

頭髪は生えないのに髭は生える

2022/08/17(水) 04:59:55.38

>>396
コラ、われわれハゲ族は、
励まし合って生きていかねばならぬ。
ハゲ増し合うことはするな。

2022/08/17(水) 07:35:41.89

頭髪はハゲても、耳毛は濃くなる

2022/08/17(水) 11:39:32.10

禿ではないのだけど、40歳にして髪は真っ白よ。

2022/08/17(水) 12:34:16.41

好きな色に染めようぜ

2022/08/17(水) 12:58:33.86

Excelらしく緑だな

2022/08/18(木) 12:18:45.39

なんという体たらく
もうハゲはこのスレ出入り禁止でいいな

2022/08/18(木) 13:11:18.15

2つの表があって、表Aと表Bとして
表の作りは二つとも同じで、
コードと数字を持ってます。
二つの表のコードが一致してる場合は

コード　　A B
101 50 60
102 40 100
103 30
104 50

といった感じで片方にしか存在しない場合は片方だけの行を追加して行くってvbaを作りたいんですが、何かいい案はありますか？
AとBの表で数字の比較をしたいです
for nextでやろうとしてますがうまくいいやり方が見つからずです

2022/08/18(木) 13:24:26.87

まずVBAを忘れます
SQLで FULL OUTER JOIN します
(ﾟдﾟ)

2022/08/18(木) 14:31:25.04

>>405
テーブルのリレーションシップを設定したらいいんじゃないかな

2022/08/18(木) 14:35:35.98

例えば以下の様なデータがあるとします

開始～終了：消費量
1月1日 0時～1月1日16時：500
1月1日16時～1月2日10時：1000
1月2日10時～1月5日 0時：800
1月5日 0時～1月5日0時：300
1月5日0時～1月6日0時：2500
1月6日0時～1月7日12時：1200

このように時間の区切りがバラバラ(かつ日跨ぎや0分間もある)のデータに対して、1月1日、2日、3日にそれぞれ合計いくらかを出したいです
なお、例えば20時～翌6時で1000の様な場合は4h:6h=400:600として計上したいです

どう計算するか方針すら思い浮かばないのですが何かアイディアありましたら教えてください

2022/08/18(木) 15:01:03.74

vlookupでできない？
普通にforループでも難しいことなさそうだけど、どんなコード書いたの？

2022/08/18(木) 15:04:36.75

>>409は>>405へのレスね

2022/08/18(木) 15:14:31.63

>>408
まず問題を分割しよう
まず(step1)日別にレコードを分割し、(step2)それを日付でグループ化して合計を出すと考えればいい
step2は典型的なグループ化と小計なんで楽勝として、step1はちょっと面倒だから更に分割しようか
例えば、入力として一行の内容を渡すと、日別分割されたレコードの配列を返す関数を作って、その結果を結合すればいいだろう

2022/08/18(木) 15:53:00.45

>>405
深く考えなくていいなら
①2つの表（シート）をそれぞれ参照して作業用のシートAを作る
コード　シート　値
101　A　50
102　A　40　
・・・
101　B　60
102　B　100
②シートAをソートする
③シートAの内容を読み込んで質問にあるようなシートBを作る

2022/08/18(木) 16:12:23.70

できた！
二重ループでできました。

**411** (ﾌﾞｰｲﾓ MM85-Ufop) · 2022/08/18(木) 16:21:20.45

日別分割関数について補足しておこう
この関数を更に分割すれば、下記のようになるだろう
(1) 開始日時と終了日時の間の日付を全て取得する
(2) 終了日時と開始日時の差を時間単位で求め、結果をHとする
(3) (1) で取得したそれぞれの日付(dとする)について、下記を実施する
(3.1) 開始日時と終了日時の間において、日付dに属する時間が何時間あるかを計算し、結果をhとする
(3.2) 消費量*h/Hを計算し、結果をxとする
(3.3) 日付dと消費量xのペアを分割結果の1レコードとする

ここまで分ければ簡単だろう。もしまだ難しければこのように適宜分割するとよい。

2022/08/18(木) 16:23:33.57

初歩的な質問なんだけど、上下一致してる場合だけ転記して行くという場合

1行目は例外として省く処理いれて
cells（r-1,1）＝cells（r,1）とかってやってくと、不一致のものが現れた時に一致してた行の最初の値が転記できなくなるんだけどどうしたらいい？

100
100
101
101
102

とかって並びの時に、100の一番上が転記されないとか
102とかも転記されない
上下比較の時の最適解の比較方法を知りたい

2022/08/18(木) 16:23:56.43

>>405
結構面倒、前提条件として「表A, 表Bのコードは各々ソートされてる」かつ「表A, 表Bのコードは各々の表内で重複してない」のであれば...
Type T
S As WorkSheet: L As Long: R As Long: C As Long: D As Long: F As Boolean
End Type

Dim TA As T, TB As T
Set TA.S = WorkSheets("表A"): Set TB.S = WorkSheets("表B")
TA.L = TA.S.Cells(TA.S.Rows.Count, 1).End(xlUp).Row: TB.L = TB.S.Cells(TB.S.Rows.Count, 1).End(xlUp).Row
TA.R = 1: TB.R = 1
TA.F = False: TB.F = False
Dim S As WorkSheet: Set S = WorkSheets("結果")
Dim R As Long: R = 1
Do
G TA: G TB
If TA.F And TB.F And TA.C = TB.C Then
S.Cells(R, "A").Value = TA.C: S.Cells(R, "B").Value = TA.D: S.Cells(R, "C").Value = TB.D: TA.F = False: TB.F = False
ElseIf (TA.F And TB.F And TA.C < TB.C) Or (TA.F And Not TB.F) Then
S.Cells(R, "A").Value = TA.C: S.Cells(R, "B").Value = TA.D: TA.F = False
ElseIf (TA.F And TB.F And TB.C < TA.C) Or (Not TA.F And TB.F) Then
S.Cells(R, "A").Value = TB.C: S.Cells(R, "C").Value = TB.D: TB.F = False
Else
Exit Do
End If
R= R + 1
Loop

Private Sub G(T As T)
If Not T.F And T.R <= T.L Then
T.C = T.S.Cells(T.R, "A").Value: T.D = T.S.Cells(T.R, "B").Value: T.F = True: T.R = T.R + 1
End If
End Sub

2022/08/18(木) 16:34:05.48

>>416
ありがとぅざいます！
そうなんです
考え出したらこれかなり面倒だなと思って、、

同じような発想かもしれないですが

外のループはA表のコード番号の縦列の値を固定して、
内側ループでB表を縦にコードが一致するまで検知して
一致した場合フラグを立てて、一致したコードの時は一致してるコードの隣に値を転記。
内側ループを抜けた時点でフラグが立っていない場合はBにしか存在しない値なので、A表の最終行以降に転記

って考え方でいけました！

2022/08/18(木) 16:37:32.42

あ.A表に転記ではなくてC表ですね

2022/08/18(木) 18:49:57.27

>>411,414
ありがとうございます、そのヒント元に自己解決できました

1. 対象となる期間を配列に格納
2. データを1つずつ0:00で分解、按分は都度[その日の時間/全体の時間]で実施
3. 配列内の一致する日付に割振り
4. 2-3ループ

2022/08/18(木) 20:46:21.25

>>415
102は一致する相手がいないけど、転記される必要があるのかな？
ぱっと思いつくのは

ポインタをrとsの2つを用意してr=1, s=1から始める
1. Cells(s, 1) == Cells(r, 1) の間だけ s を進める。
2. Cells(s, 1) の値が変わったところで
a) s - r > 1 なら Cells(r, 1) を転記しながら s に追いつくまで r を進める
b) s - r == 1 なら転機せずに s に追いつくまで r を進める
3. 1からの手順を表の最後まで繰り返す

2022/08/18(木) 22:39:41.60

>>420
相手がいない場合も転記です
そのやり方試してみます
単純そうでむずかしい…

2022/08/19(金) 01:11:58.45

>>415
そこからどういうデータを求めたいんだ？
単に重複データ削除するだけで良い気がするんだが

2022/08/19(金) 07:58:47.24

>>422
上下一致してるものだけ足し込んでいくということをしたいです
一致してなくても一個だけのはそのまま転記したい
空白は無視で
単純なコードでいけるかなと思ったら難しくて、、

2022/08/19(金) 09:33:47.59

まずどういう出力か書けよ
頭悪すぎだろ

2022/08/19(金) 10:55:40.42

>>424
>>423で説明すているだろ
これで分からないってお前白痴か？

2022/08/19(金) 11:00:49.57

>>415の入力例で言うと
100は上下で一致してるから転記する
101は上下で一致するからこれも転記する
>>421の説明だと102単独でも転記する
転記しない入力例ってどんなのよ？

2022/08/19(金) 11:05:43.32

423の足しこんでいくというのは？もう何もわからない

2022/08/19(金) 11:07:12.98

転記？今日の天気？
Excel必要ですか

2022/08/19(金) 11:32:50.63

うるせえ！この禿！

2022/08/19(金) 13:14:47.90

まず髪の毛緑に染めてこい

2022/08/19(金) 13:37:04.21

質問する側がおかしいと（どういう結果にしたいかが不明の場合）回答する気も失せるよね上下一致と言われたら
A
B
C
D
ならBを基準にA＝BかつB=Cって自分はするんだが違うんだろ？

2022/08/19(金) 16:42:31.05

重複除去したいって解釈してた

2022/08/19(金) 17:14:26.61

>>423
最終的に欲しい形を書け

エクセルの機能に重複データの除去ってあるのはしってるのか？

2022/08/19(金) 19:41:03.51

知らないのでわ？

2022/08/19(金) 19:54:20.05

足しこむとか転記とか何がしたいか意味不明なんだよね
単純にいま記載されている列の値について
１．同じ値が続く場合は最初の一つだけ残す
２．ただし途中にある空白は無視する（１、空白、１は同じ値が続いてるとする）
３．最終的に残した値で詰める　または　残した値は別の列なりシートなりに転記する
みたいな事をやりたいのか質問見ただけではわからんのよね
この場合なら検索する行とセットする行を用意すれば簡単にできる話なんだけどね
仕事もそうなんだけど説明が下手な人ってやっぱり仕事もできない人が多い

2022/08/19(金) 20:23:56.98

足し込むというのが加算という意味であれば必然的に数値ということになるけど、、、

上の行と同じ数値なら足し算する＝2倍するってことか?
それとも上と下の行が同じ数値なら現在行と上下の３行の和にするってことか?

なんかいろんな解釈ができる要望なんよね。。。

2022/08/19(金) 22:10:12.43

まぁ重複したデータを足し込むのか削除するのかはさした問題ではない。
ディクショナリやコレクションに元にあった値をキーにして
まずキーと同じ値をキーに該当する値として設定して、
同じキーが次回来たときに無視するか足し込むかすればいいだけの話だからな
最後にそれをシートに展開すればいいだけの話。
そうしとけば足し込もうが重複削除だろうが簡単に対応出来る。
SQLを知っているならSQLでやってもいい。

問題は同じ値が飛び石で来る可能性があるのかということ。
例えば101、101、102、101の順序で来ることがあるのか？
この場合、要件としては「上下で一致しているものだけ足し込む」ということなので上の例で言えば202、102、101と設定されるのが正ということになる。
これは上記した方法でやるのはかえってまどろっこしい。
そうしたら上から舐めてコレクションに値を格納、前回の行と次の行の頭が同じなら無視するか足し込むかすればいいし、値が違うならそのままコレクションに追加すればいい。
最後にそれをシートに展開する。

このように同じ数値が飛び石で来るかで作りがガラッと変わってしまう。せめてそこだけでもハッキリさせてくれれば。

2022/08/20(土) 00:10:31.82

もう終わった話なんだと思うけど正直質問する人はこういうデータをいじりたいという事は伝えても
結果としてこういうものが欲しいっていう事を伝える人が少ないんだよね
だからいろんな解釈ができるからその辺もちゃんと情報を伝えてほしいわ

2022/08/21(日) 19:54:19.68

そういうの含めて個人の能力

2022/08/24(水) 18:51:09.99

Unique関数の引数って二次元配列じゃないとだめなの？

2022/08/24(水) 19:20:17.01

Range型を渡すんじゃダメなの？

2022/08/24(水) 20:13:49.71

実際に試してみれば答えが出ると思うのだが

2022/08/24(水) 22:08:51.37

マニュアル読め

2022/08/24(水) 22:19:47.85

MSヘルプですね～

2022/08/28(日) 22:27:57.16

excel2019
win10

設定シートに各項目を入力させたらそれに応じて別シートに計算結果を入力させるVBAを書いてる途中なのですが、
VBAソースを書いてる途中に設定シートにこんな数値も持たせたい、
入力画面のレイアウトをあれこれ変えたいと行や列の挿入をしたくなってしまうのですが
そうするとVBAソースの方がずれたままになってしまうのですが、シートに挿入をしたら
それにともなって既にかけている部分だけでもソースコードのcellsやrangeの値が更新されてくれたりしないでしょうか？

2022/08/28(日) 22:36:45.40

>>445
参照先は自動更新されない
セルに名前を付けてその名前で参照すればよい

2022/08/28(日) 22:37:28.82

名前付きセル範囲
https://thom.hateblo.jp/entry/2016/07/08/001933

2022/08/29(月) 20:34:19.97

おまいらが毛がどうのこうので喜ぶがらマジで頭ツルッツルにしてきたぞ

**デフォルトの名無しさん** (ｽﾌｯ Sd22-2Ehw) · 2022/08/29(月) 22:48:42.98

>>445
コレクションに格納するのが攻守最強

2022/08/31(水) 02:50:20.75

>>449
そのコレクションの中身はどうやって保守するのかね？

2022/08/31(水) 19:22:22.41

>>448
ツルツルがいいのは下の毛です

2022/08/31(水) 22:52:05.08

>>451
お、おうw
しかし真夏にはツルッツルもなかなか良いわ涼しい

2022/09/01(木) 03:46:18.56

win10
excel2019

Sub y()
Dim st As Range
Set st = Worksheets("参加者").Range("A1:BA80").Find(What:="氏名")
If st is Nothing Then
else
Debug.Print st.Row
end if
End Sub
これをすると"氏名"文字列に一致したセルがrangeオブジェクトで貰えますけど
本当に欲しいのは氏名のx個下のrangeオブジェクトなのです。
氏名のセルと参加者の開始セルの相対位置は固定的です。
氏名はいろいろな名前があるのでマッチングしづらいので氏名で位置を探って取り込んでいます。

rowやcolumnくらいしか使いませんけど、すっきり感としては本当に欲しいのは氏名の開始セルのrangeオブジェクトなのです。

返り値のrangeオブジェクトをずらすということはできますでしょうか？

**453** (ﾜｯﾁｮｲ 4666-JjaS) · 2022/09/01(木) 03:53:25.83

Set st = Worksheets("参加者").Range("A1:BA80").Find(What:="氏名").offset(1,1)
こんな感じで解決しました。
動いてるのはまぐれでしょうか？

2022/09/01(木) 06:49:22.79

>>453
何やってるかわからんままコピペして動かしてる感をヒシヒシと感じるので
Rangeオブジェクト
Findメソッド
Offsetプロパティ
について理解できるまで解説サイト読んでください

あと、>>445についても回答付いてるんだからなんかリアクションしてください

2022/09/01(木) 09:42:20.83

451
おまわりさんこいつです

2022/09/01(木) 10:01:37.99

VIO脱毛する人が増えてる

2022/09/01(木) 10:51:28.10

>>455
何度か書き込もうとしたんですけど、書き込みエラーになってしまっていました。
なんだかレスアンカーが不正とか出てきてたのでわからなくなりました。
レスアンカーをつけちゃいけないみたいな制約？

>>445に回答いただいた方ありがとうございました。
これも書き込めないかもしれませんが。

RangeオブジェクトのOffsetメソッド？は結合セルがやっかいなのですね。
結合セルでない時のエラー処理を加えると結局コードが汚くなっていくジレンマでした。

オブジェクト型がなかなか理解できません。
dim wf as object
set wf = worksheetfunction
とやってもwf.でエクセル関数を使えるようになるわけではないみたいですし。

ありがとうございました。

2022/09/01(木) 11:00:01.20

Function wf() As WorksheetFunction
Set wf() = WorksheetFunction
End Function
こっちで呼び出し短縮は出来ました。初めて使いましたfunction

2022/09/01(木) 14:07:42.09

名前付きセル範囲を使うためにnamesコレクションを理解しようとしてるのですが、
activeworkbook.namesをfor eachに入れると現在は1個しか出て来なくて
値が
=#NAME?
こんなのです。エクセルの方から名前の管理を開いてもこの名前は出てきませんし、何も出てきません。
何なのでしょうか？この値は？
なにも設定されてない時の値かと思ってワークシートのあるセルに名前の設定をしみたのですが、
やはりこの値は残ったままでした。
RefersToでもvalueと同じく
=#NAME?

**460** (ﾜｯﾁｮｲ 4666-JjaS) · 2022/09/01(木) 14:14:01.95

わからないまま、vbaから非表示を表示させるコードをコピペして削除しておきました。
こんな名前作った覚えはないですが

2022/09/01(木) 14:38:52.05

そっか、うんうん、非表示を表示ね、良かったね

はい次の方

2022/09/01(木) 15:07:23.09

ニートならロン毛にしろよ

2022/09/01(木) 19:17:21.60

Range型と配列の違いが後一歩わからないのですが、
Sheet1にA1=10,A2=20,A3=30,B1=40,B2=50,B3=60....みたいにしてar(2,2)と近い感じにしてます。
Sub Sample1()
Dim ar(2, 2) As Variant
ar(0, 0) = 10
ar(1, 0) = 20
ar(2, 0) = 30
ar(0, 1) = 40
ar(1, 1) = 50
ar(2, 1) = 60
ar(0, 2) = 70
ar(1, 2) = 80
ar(2, 2) = 90
Debug.Print TypeName(ar)
Debug.Print TypeName(Worksheets("Sheet1").Range("A1:C3"))
End Sub
arはvariant型の配列でWorksheets("Sheet1").Range("A1:C3")はrange型のオブジェクトになるんですよね。

感覚的にはどちらも配列を渡している気分なのですが、セル範囲参照のrangeだからrange型オブジェクトになるってだけですか？

2022/09/01(木) 19:18:06.47

馬鹿過ぎてやばい奴が来てる

**464** (ﾜｯﾁｮｲ 4666-JjaS) · 2022/09/01(木) 19:23:31.46

for each のinの後に配列をおいても、range型オブジェクトをおいても
同じように中身を取り出してくれるから混乱してしまったのかもしれません。

2022/09/01(木) 19:28:08.64

もし Dim ar(2, 2, 2) As Variant なら3次元の配列としか言いようがない

2022/09/01(木) 20:43:50.82

本人は切実なんだろうけどイマイチ何を理解しようとしているのかが汲み取れない。

とりあえず、シートは二次元配列みたいなものと思えばいいんじゃないかなぁ。
バリアント型の変数に範囲指定したrangeを代入したら配列が出来上がるんだから。。。

って、こういうの説明するのって余計に混乱させそうで怖いのよね。

2022/09/01(木) 22:31:39.49

なんでそんな事やっているんだろう。。？？

2022/09/01(木) 23:45:17.73

Rangeオブジェクトと配列、全く異質のものなのに同じ代入インターフェースがつかえるのが不思議なのでしょう
女学生は貧困で給食費すら払えないのに上級国民は交通費で200万円貰えるのですから

2022/09/02(金) 00:10:13.04

>>464
配列には数字しか入っていませんが、
Ｒａｎｇｅオブジェクトには数字以外に色や罫線やハイパーリンクなど色々な情報が入っています。というかワークシートのセルそのもの（オブジェクト）です。