Excel VBA 質問スレ Part75

**デフォルトの名無しさん** (ｽﾌｯ Sd7f-wadS) · 2022/02/09(水) 14:24:32.62

!extend:checked:vvvvv:1000:512
!extend:checked:vvvvv:1000:512
↑2行に減ってるけど、同じ内容を3行に増やして貼り付けるナリ

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

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

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

2022/02/13(日) 07:35:55.02

>>44
Excelの総合で質問もして教えて頂けました。
解説ありがとうございます。
勉強になりました。

2022/02/14(月) 12:57:56.87

この画像みたいなExcelファイル(1枚目)をデータベース化する為に、情報を抽出して横並びに整理したい(2枚目)のですが、アドバイスいただきたいです。
行数や列数がそれぞれ異なり、罫線の枠内でも、変に結合や分割されており、関数のみでは対応しきれませんでした。
※元々がPDFでしか入力できないデータの為、それをExcel変換しているせいで構成が崩れています。

罫線の枠構成は崩れていない為、マクロで処理するとしたら、各セルの罫線を判定して、囲われた範囲の値を順番に取得していくしかないのかな？と思っています。
枠内の左上隅(左と上に罫線があるセル)から、右下隅(右と下に罫線があるセル)までの値を取得して連結、次の枠へ進む、という処理しか思いつかないのですが、他にいい案はあるでしょうか？
https://i.imgur.com/oMaEhvs.png
https://i.imgur.com/gnZIoe7.png

2022/02/14(月) 13:06:54.20

>>46
Power Queryでも、このセル結合だらけ(結合されてたりされてなかったり・・・)の
データクレンジングは厳しいかも知れない。
ステップが100超えるかも・・・

2022/02/14(月) 13:10:47.83

一度全ての結合解除するとかは？
同じ項目に相当する入力値の行き先が泣き別れたら終わるけども

2022/02/14(月) 13:31:47.44

>>46
ID以外に空欄がないのなら、データの区切り、たとえばB列の水平の罫線の位置だけ検出すればいい
あとはその範囲を左上から舐めて、空白セルを無視しながらデータを取り出すだけ
結合は全部解除した方がロジックは簡単になると思う
そんなに難しくない

テスト用のデータを用意するのが面倒だから、そのサンプルをエクセル形式かスプレッドシートにでも入れて公開してよ

2022/02/14(月) 13:53:01.79

>>46
すげぇな！ここまでのは見たことないぞ
行数が同じなら、
C6:C11を文字列で結合すれば良いかと思ったけど
行数も違うのか

セル内改行したり、セルをわけたりで行数がバラバラなのね

やるなら、
C6　11111-11
c7　11111-12
C8　11111-21
C9　11111-22
・・・
みたいにすれば、まぁなんとか行けるかなと思う
今のままじゃ無理だ

2022/02/14(月) 13:58:47.62

と思ったら罫線を基準にすればいいのか

2022/02/14(月) 14:07:53.31

CSVで吐き出して
それを加工したほうが早くね？

2022/02/14(月) 14:16:38.09

csvにしたら大惨事だろう

2022/02/14(月) 14:28:40.98

いくつも返信ありがとうございます。
サンプルは後程公開しようと思います。

セル結合解除したらこうなります。
罫線範囲の左上に値があればまだマシなのですが、途中だったり分割されてたりがネックです。また、空白のセルも多々存在します。

VBA初心者の為、合理的な処理かは分かりませんが、今はこの矢印の順でセルを移動しながら、罫線の判定と値の取得する流れで考えています。
https://i.imgur.com/raK9Xyw.png

2022/02/14(月) 14:44:13.39

https://atmarkit.itmedia.co.jp/ait/articles/2002/27/news011.html
　↑　ここでは一旦Word経由で取り込めと勧めているが、果たして

にしても、 > ※元々がPDFでしか入力できないデータ　　とは？
PDFの原稿があって、その枠の中にテキスト貼り付ける方式なのか？

2022/02/14(月) 14:53:57.21

>>50
この画像の範囲にはセル内改行はないぞ
よく見ると背景と同じ色の白い罫線が隠れてる

2022/02/14(月) 14:55:08.92

>>55
最新のPDFには入力フォームを重ねる機能があるんよ

2022/02/14(月) 14:59:16.98

>>54
基本それでいいと思うよ
いちいち判定せずとも空白セルも含めてどんどん文字列を足してくだけで目的のデータが自動的に残る

2022/02/14(月) 15:02:51.71

>>56
J21セル

2022/02/14(月) 15:14:55.89

>>51
罫線を検知するVBAは、このあたり使うのかしら？

例えば「実線の下線縛り」だと

If Range("A5").Borders(xlEdgeBottom).LineStyle = xlContinuous Then
　MsgBox "下線あり"
　Else
MsgBox "下線なし"
　End If

セルの上下左右も調べることになる？

http://chaichan.lolipop.jp/vbtips/VBMemo2009-01-072.htm

2022/02/14(月) 15:21:11.73

>>59
見落としてたごめん

2022/02/14(月) 15:47:34.87

PDFからExel変換がまともに出来てないから元のPDFからテキストデータ抜き出して処理した方が早い気がするなぁ

2022/02/14(月) 15:52:40.41

>>60
下だけでいい
ってかはよサンプルくれ。サンプルなしでコード書くのあんまり好きやないんや
とりあえず下線の判定部分だけ置いとくぞ

Function 下線あり(target)
If target.Borders(xlEdgeBottom).LineStyle <> xlLineStyleNone Then
下線あり = True
Else
下線あり = False
End If
End Function

2022/02/14(月) 16:25:13.19

>>63
>>>>60
>>下だけでいい

2個分入っている「例参考3・4」と
1個分だけの「例参考5」を
どう見分けるか、嫌らしいなぁ・・・
と思って。

2022/02/14(月) 16:50:56.63

>>55
word経由だったり、何種類かの変換ソフトを試したけど出来はほぼ変わりませんでした。

PDFの経緯ですが、他社のWEB上でのサービスにて個人情報等を登録管理しており、選択して出力する際にはPDF形式でしか出力できず、今回の悩みになっています。

2022/02/14(月) 16:52:00.24

サンプルです。
よろしくお願いします。
https://docs.google.com/spreadsheets/d/1KXHOGpa6GFBfFflosWycM_IsNS5P9gnk9m2eGG0uXLs/edit?usp=drivesdk

共有の仕方合ってるか不安ですが。。

2022/02/14(月) 17:08:51.74

>>65
そのサービスを提供している会社に相談しろよ
馬鹿なの？

2022/02/14(月) 17:56:38.45

画像見た感じだとそうめんどくさい話ではないかね
住所欄と連絡先欄の住所と電話番号をどういうルールで分解するかがわかれば変換は余裕かと
ただこの手の内容までこのスレで答えるのはいいように使われている感があっていやだなぁｗ

2022/02/14(月) 18:16:24.84

住所の下に下線ない事に気づき、無事死亡

2022/02/14(月) 18:16:43.53

>>66
DL出来たよ。
Power Queryでどこまで出来るか
やってみる。

2022/02/14(月) 18:19:08.21

更にこれ氏名でセルわかれてるんかーい

2022/02/14(月) 18:55:37.73

>>55
混乱させてすみません、PDFでしか「入手」できないデータの誤りでした。
サービス元からは、それ以外の形式での出力はできないとの回答の為、このExcelファイルをなんとか処理して使わざるを得ない状態です。

2022/02/14(月) 18:58:42.50

指定した日付から、その日付が１年間の第何週かを求めそれを引数にして、その週の月曜日と金曜日の日付を返す式を教えてください。

i = WeekNum(指定日）
date1 = 開始日（月曜日）
date2 = 終了日（金曜日)

2022/02/14(月) 19:33:13.83

>>73
・週の初めは何曜日基準？日曜日？月曜日？別の曜日？
・それを引数のそれって何？開始日（月曜日）の関数にiは使ってないのでは？指定日ではだめなの？
・仮に日曜日基準とした場合、2022年1月1日(土)の月曜日は2021年12月26日？それとも別の日？

2022/02/14(月) 19:34:32.34

>>73
そもそもVBAの回答が知りたいの？それとも関数使ってなの？関数ならスレチですよ

2022/02/14(月) 19:35:02.10

>>46
2時間半、思ったより早くできたけど、まぁキツイな
変数名も終わってる

https://jsfiddle.net/5vuatdrq/1/

2022/02/14(月) 19:36:56.50

別に関数でもいいんじゃないの？
俺もコードの中にworksheetfunction使ってるし、気にしたらキリないわ

2022/02/14(月) 19:41:40.69

>>76に追記

「シート1」というシートに元データ
「転記先」というシートを作っておいて下さい

2022/02/14(月) 19:45:27.38

>>66
ひとまず
元ファイルのセル結合を解除しないまま、
別ファイルからPower Queryでsheetをそのまま読込んで、
不要な行/列、空白、改行コードを削除したり
列を合体したりして、ある程度整理してみた。

https://i.imgur.com/8wCdbbJ.png
https://i.imgur.com/LvCak5M.png

やっぱ、どの行までが1個体のデータなのか判別がつかないので、
ここから先は難しい。
それが特定できればsheet上のテーブルの右側に列足して
M言語でなく、使い慣れたExcelのIF関数や文字列関数使って
拾っていくことも出来そうなんだけど・・・

先にVBAで罫線判別して各行にフラグでも立てることが必要っぽい。

>>63 氏に期待

2022/02/14(月) 19:46:56.80

あ、
>>63 氏、出来たのね。

2022/02/14(月) 19:47:44.47

pdfからの変換で毎回セル位置が変わるのか？
変わらないなら一回参照シート作るだけじゃね？

2022/02/14(月) 19:53:08.06

>>73
関数の方が早い
https://i.imgur.com/gJTY8OT.png

vbaに落とし込むなら、worksheetfunctionを使ってくれ

2022/02/14(月) 19:54:22.25

>>73
DatePart("ww", Date, vbMonday, vbFirstFullWeek)

あとはその日が何曜日が調べて
プラスマイナスすれば月金も出るよ

2022/02/15(火) 02:26:45.53

Excel板の住人が発狂して荒らしてくるから関数はダメかも

2022/02/15(火) 03:38:15.06

Fomuraでvbaでセルに
関数放り込んでやれば

2022/02/15(火) 08:44:43.33

>>79
powerqueryである程度処理は可能なんですね。ありがとうございます。
実際は1000件以上のデータで項目も更に多く、行も列もバラバラなので手作業はやはり限界がありそうです。

2022/02/15(火) 08:49:44.20

>>76
本当にありがとうございます。
サンプルの方では問題なく処理されるのを確認できました。
自身が書いたらもっと長々しくぐちゃぐちゃになっていたと思うので、すっきりまとまっていて素晴らしいと思います。

For Each 個人列 In Array(3, 4, 9, 10, 11, 15)
ここは列は固定の指定になっていたのですが、ファイルによっては列もバラバラなので、最初に文字列検索で列を調べてから処理に入るように変えていきたいと思います。

皆さん本当にありがとうございました。

2022/02/15(火) 13:36:45.93

レセコンのデータだろ

2022/02/15(火) 16:04:21.44

A～K列にデータを入力しているシートで、C列にデータがある場合のみその行をコピーする、という動作をさせるには
どう記述すればいいでしょうか？

2022/02/15(火) 17:39:01.90

if文でC列に入力があるかを判定すれば良い
コピーする行はC列に入力がある全ての行か？それとも一番最初のものだけか？またはカーソルがある位置だけを対象にするのか？
もう少し細かく仕様を書いてくれた方が導きやすいぞ

2022/02/15(火) 17:39:49.61

>>89
If Range("C1").Text <> "" Then Range("A1:K1").Copy

2022/02/15(火) 18:55:31.55

>>89
オートフィルタで空白以外にして、コピーペースト

Sub Macro1()
最終行 = Cells(Rows.Count, 1).End(xlUp).Row
'オートフィルタの後にコピー
Range(Cells(1, 1), Cells(最終行, 11)).AutoFilter
ActiveSheet.Range("$A$1:$K$6").AutoFilter Field:=3, Criteria1:="<>"
Columns("A:K").Select
Selection.Copy

'mに貼り付けてフィルタ解除
Range("M1").PasteSpecial
Application.CutCopyMode = False
Selection.AutoFilter
End Sub

2022/02/16(水) 09:01:55.23

>>90
確かにそうですね　別ファイルにC列にデータがある行のみをエクスポートするようなものを作ろうとしていたので
C列にデータがあるかの判断→あればコピー→エクスポート先との差分のみを貼り付け
としようとしています
差分の貼り付けは参考になりそうなコードがあったのでそれと組み合わせて考えようと思っていました

>>91
ありがとうございます、シンプルで応用が利きそうで助かります

>>92
その発想はありませんでした、ありがとうございます

2022/02/16(水) 21:42:26.46

選択した範囲のセルから、「セルの色」と「文字」で絞った数を調べたいのですが、どのように書けば宜しいでしょうか？
（色に関しては、指定したセルの色を参照したいです。）

例：A1～D10の中で、E1と同じ色で、「テスト」と書かれているセルの数
みたいなことです。

出来ればグーグルのスプレッドシートでも使いたいです…

2022/02/16(水) 22:02:32.25

>>94
GASは全然書けないんだよなぁ

Sub foo()
Set 検索範囲 = Range("a1:d10")
Set 検索 = Range("e1")
カウント = 0
For Each test In 検索範囲
If test.Interior.Color = 検索.Interior.Color And _
test.Value = 検索.Value Then
カウント = カウント + 1
End If
Next
MsgBox カウント
End Sub

2022/02/16(水) 22:17:26.65

>>94
べたに指定範囲のセルをループ回して比較すればいいんじゃね
Sub test()
MsgBox CountX(Range("A1:D10"), Range("E1"), "テスト")
End Sub

Function CountX(target As Range, colorCell As Range, str As String)
Dim count As Integer
Dim c As Range
For Each c In target
Debug.Print c.Interior.color
If c.Text = str And c.Interior.color = colorCell.Interior.color Then
count = count + 1
End If
Next
CountX = count
End Function
こんな感じか

2022/02/16(水) 22:19:34.63

ああ、ほぼ被った上にDebug消し忘れてる

2022/02/17(木) 15:22:29.06

fileSystemObjetを使うときに下のコードのようにインスタンスを格納する変数を省略できるのはなぜですか？
ひょっとしてwithでくくるときに一時的に作られる変数にでもいれているのでしょうか？

with New fileSystemObject
ステートメント
End With

2022/02/17(木) 15:49:10.52

>>98
> ひょっとしてwithでくくるときに一時的に作られる変数にでもいれているのでしょうか？
まあそう言うこと
RegExp とかでも使える

2022/02/17(木) 16:14:35.03

>>98
どんなオブジェクトもwithの一時変数に入れられるよ
ほかによく使われるのはブラウザコントロールとか

2022/02/17(木) 16:21:30.38

このwithが参照してるオブジェクトって確認する方法ありますか？

Sub foo()
With Cells(1, 1)
Debug.Print .Value
End With
End Sub

とりあえずウォッチ式に「.value」入れてみてもエラーで見れなかった
with　と入れてもエラー
どこに格納されているんだろう

2022/02/17(木) 17:02:27.81

>>101
VBだとGetTypeで取得できるんだけど、VBAにはないね

2022/02/17(木) 19:16:24.68

>>101
内部的に一時変数に保存されているが、ユーザからは直接参照できない
ウォッチ式はどうもWithのスコープ外の扱いっぽいな

>>102
暗黙的に参照されているものが自分自身を返すようなメンバを持ってないかぎり
Withで暗黙的に参照してる変数そのものを確認する方法はなかったはず
おそらくVBにもなかったはずだけど

個人的には参照できないWithはつかわないのが作法じゃないかと
俺はWith New否定派だ

2022/02/17(木) 20:01:56.20

>>102>>103
ありがとう
with、どうにもモヤッとした感じが取れないな

Meかparentでメモリ追っかけられそうな気もしたけど、うーん難しそう

2022/02/17(木) 20:37:40.67

配列に値を保存しておいて、別シートの特定セル範囲に値を入れたいんだけど、
Worksheets("シート名").Range("A1:A10").Value =配列
だとエラーが出ます。

仕方なく、シート移動してから
Range("A1:A10").Value =配列
で値を入れてから元シートに戻る処理をしてるんですが、他にいい方法ありませんか？

2022/02/17(木) 20:39:35.05

何てエラーが出る？

2022/02/17(木) 20:43:06.54

なんのエラーか分からんから手っ取り早いのは
先にWorksheets("シート名").activateしておけば

2022/02/17(木) 20:52:49.10

曖昧な情報ですみませんでした。

WorkSheets(”シート名”).Range(cell(変数,1),cell(変数,20)).Value =配列
だと

実行時エラー'1004':
アプリケーション定義またはオブジェクト定義のエラーです。

となります。

Worksheets("シート名").activate
Range以下同じ

だと正常に動作します。
数百のデータ取得転記なので、可能であればシート移動は減らしたいと考えています。

2022/02/17(木) 20:56:15.04

Rangeの後のCellsにもシート指定が必要

2022/02/17(木) 21:03:41.49

それ案外忘れやすいよな

2022/02/17(木) 21:06:37.67

>>108
>>109 の言う通りで
With WorkSheets(”シート名”)
.Range(.Cells(変数, 1), .Cells(変数, 20)).Value =配列
End With
が定番

2022/02/17(木) 21:19:48.40

>>111
setの方が好きだわ

set ws = WorkSheets(”シート名”)
ws.Range(ws.Cells(変数, 1), ws.Cells(変数, 20)).Value =配列

2022/02/17(木) 21:56:36.84

>>109-112
正しくそれが原因でした。
ありがとうございました。

先日>>46で質問させて頂いた者ですが、教えていただいたものをベースにかなり形になってきました。
数値を1個ずつ転記するのと、配列に入れて転記するのって処理時間にかなり差があるんですね。

2022/02/17(木) 22:14:24.38

>>113
シートへの読み書きはかなり遅いからデータが大量にある時は配列に一気に読出して配列上で処理して一気にシートに書出すのは定番の高速化手法

2022/02/17(木) 22:24:21.56

1人30項目×数百人だったので、まずは1人分を配列に入れて転記するようにしただけで数倍早くなりました。
このようなケースだと、さらに30×人数全てまとめて配列に入れて転記するものなんでしょうか？

2022/02/17(木) 22:29:04.03

ケースバイケースだけど30列×数百行程度なら全体を一気に読み書きしてもいいと思う

2022/02/17(木) 23:14:43.35

>>115
数倍といっても、1秒が0.1秒になっても、実質何も変わってないのと同じ
そのためになれない配列を使ってコードを書く時間が5分長くなるようなら、そんな最適化はせんでいいってことになる
30分が3分になるなら、やったほうが良いだろう

要は総合的に見て、全体的な時間が減るなら最適化したほうが良い
死ぬほど暇なら、暇なときにどんどんやっておけば、忙しくなったときに余裕が出る

2022/02/18(金) 00:01:31.11

１秒と０．１秒の体感時間の差は意外とでかいぞ
短くできるならしたほうがいいわ

2022/02/18(金) 03:42:39.82

>>115
プログラムを大幅に書き換えなくても、再描画と再計算とイベントを止めるだけで速くなることもあるんでいっぺん試してみ

2022/02/18(金) 06:33:19.64

>>104
Withはそういうもんだと割り切るしかない。
VB系以外で見たことないからVB系特有なもんだと思う。
昔、旧VB6のスレでWith NewってやるとC#のUsingみたいにEnd Withで勝手にObjectの開放してくれるので、
そういう使い方をするコードが書かれたとき賛否両論だったのを覚えてる。

Withも、もしVB系の言語が将来生き続けることになっていたら負の遺産みたいに言われていたかも知れないけど、
Visual Studio CoreでマイクロソフトもVB.Netはもう発展させないことを明言しているから
そっちの開発している人は適当なところで別言語やるようになるだろうね。

2022/02/18(金) 08:19:03.35

>>120
＞そういう使い方をするコードが書かれたとき賛否両論
そうなるだろうね
割り切って使う人には便利かもしれないけど、捉えきれない部分があるのはかなり気持ち悪い

ありがとう、スッキリした

2022/02/18(金) 09:18:41.84

>>120
> VB系以外で見たことないからVB系特有なもんだと思う。
Pascal からの輸入だろ

2022/02/18(金) 15:45:36.50

>>99>>100
すっきりしました。ありがとうございました

2022/02/18(金) 16:04:07.81

C++系でクラスメンバーに _ 付けたりするよりは . 強制のVB のがわかりやすい

2022/02/18(金) 16:12:26.42

VBとVBAを混同してしまう

2022/02/18(金) 17:12:34.16

VB更新止めるならビジュべー7作ったらVBAからの乗り換えで大人気になるかも
本職の人は発狂しそうだけど

2022/02/18(金) 17:40:13.70

ならんよ

2022/02/18(金) 18:34:46.08

>>127
4文字で撃破笑

2022/02/18(金) 21:31:15.96

イベントでメッセージボックス起動するだけじゃ
進む戻るの履歴は消えないんだな
良かった良かった

2022/02/19(土) 00:17:14.09

RelaxTools AddinからTortoiseSVNに繋げる方法が
バージョン管理の手法としての最善策ですか？

2022/02/19(土) 01:15:29.08

そういやvbaでバージョン管理っていい方法あるのかな
ドロップボックスに丸投げしてるからよく知らないや

2022/02/19(土) 02:51:28.34

昔はOFFICE DEVELOPERとかいうのがあって、たしかVSSだったと思うがバージョン管理できた
ACCESSとか2007ぐらいまではTFSにつなぐアドインもあった

この辺の仕組みって、基本的にはソース管理プロパイダで差し替えできるようになってたはずなんだがなぁ
あんまり情報もないんだよな

2022/02/19(土) 05:57:33.40

Microsoft製バージョン管理システム
VSS(ソース管理はVSS)
↓
TFS(ソース管理はTFSまたはVSS)
↓
AzureDevOps(ソース管理はGitまたはTFS)

2022/02/19(土) 07:12:39.37

TFS(TeamFoundationServer)は製品名
ソース管理はTFVC(TeamFoundationVersionControl)

2022/02/19(土) 07:37:52.31

>>134
間違った理解で適当なこと書き込んですまなかった
確かにTFVCと呼ぶ方が正しいな
https://i.imgur.com/m6FXGvC.png

https://docs.microsoft.com/ja-jp/azure/devops/repos/tfvc/comparison-git-tfvc?view=azure-devops-2020

2022/02/19(土) 09:03:14.67

SVNで間に合ってる

2022/02/19(土) 09:23:14.09

時々のマクロファイル保存で間に合ってる

2022/02/19(土) 10:11:08.00

vbaでそんな巨大プロジェクト作らないから、ドロップボックスでいいかな・・・

2022/02/19(土) 10:47:12.21

マイクロソフト製品なんだから
One Driveでいいだろ

2022/02/19(土) 11:07:16.61

ちゃんとやるのは最初だけ

2022/02/19(土) 11:11:58.36

One Driveはバグが多すぎるから絶対に無理ｗ

2022/02/19(土) 11:33:30.74

バージョン管理なんてエクセルファイル名にver1.0とか含めておけばいいんじゃないの？

2022/02/19(土) 11:37:15.90

売上報告_最新_最終版コピー（2）_old決定版.xlsm

2022/02/19(土) 11:54:53.81

>>141
どんなバグがあるの？