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/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
どんなバグがあるの？

2022/02/19(土) 12:05:18.04

>>144
復元できないという致命傷
onedrive　バグ　などで検索すると出てくるよ
先週ぐらいもあった

2022/02/19(土) 14:20:31.32

回答を待たずに見切り発車でTortoiseSVNを導入しようとして
サーバーエラーの解決ができずに数時間のたうち回っている

バージョン管理なんておいらには高等技術過ぎたかもしれねぇ…

2022/02/19(土) 14:35:17.05

>>145
同期関連か、まあそれを信用するのは時期尚早やな

2022/02/19(土) 15:26:08.63

>>146
TortoiseSVNは割とガチ環境。導入のハードル高いからマジでやめたほうがいいぞ
ドロップボックスか、googleドライブに同期させておくぐらいがちょうど良い

バージョン管理って大層なものを導入しなくても、
コードの中に「ｘｘまで実装した。残りｙｙ」みたいに書いておけば
それなりに使いやすいバックアップになる

どうせVBAなんてブランチ切るとかそんなことしないから一本道でヘーキヘーキ

2022/02/19(土) 15:26:52.07

なお俺もTortoiseSVN挑戦して挫折した一人
かなり癖あった気がする

2022/02/19(土) 15:41:34.88

まあいまどきならgitが楽でいいんじゃね
サーバ側でなにもしないならファイル共有さえすればバージョン管理サーバとして使える
VBAはエクスポートしてバージョン管理だな。そこまでする必要があるものは少ないけど

>>142
エクセル丸ごとで管理するだけならな
ソースの修正箇所の履歴とか差分とかとるときにバージョン管理が必要なんだよ

2022/02/19(土) 16:13:02.46

>>146
自分一人で使うならサーバーなんて要らんよ
リポジトリをローカルファイルシステムに作ればいいだけ
外部参照とかの一部の機能が使えなくなるけど
具体的な操作は流石にスレチだから聞きたいならこちらで

バージョン管理システムについて語るスレ10
https://mevius.5ch.net/test/read.cgi/tech/1393147031/

2022/02/19(土) 16:22:07.18

TortoiseSVNはクライアントだよぉ
サーバーわかんないならgit hub使えば良いんでない？

2022/02/19(土) 16:40:13.61

gitとsvnはなんか概念違うから最初にsvnから入るとgitが分かりにくくなるって聞くな

2022/02/19(土) 16:57:02.53

概念というかポリシーが違うから馴染めないというのはあると思う
Linux みたいな巨大なアプリでなきゃ SVN の速度でもたいして困らんし

2022/02/19(土) 17:18:28.82

Excel板覗いてきたけど、関数を入力するときに＝+って入力している人がいた。それはマニアな人だよね？

2022/02/19(土) 17:37:22.02

>>155
それ俺なんだけど、
・「=」の売り間違えが嫌い
・「+」は絶対に打ち間違えない
ので、
数式は
+A1-B1
と入力してる
excelの仕様上、+で始まっている数式には更に＝が追加される模様

2022/02/19(土) 17:56:27.92

世の中に絶対など無い・ある　どっち？

2022/02/19(土) 17:59:47.25

ない

2022/02/19(土) 18:11:04.25

たまに+で入力している人見るから。ちょっと思い出して

**ハノン** ◆QZaw55cn4c (ﾜｯﾁｮｲ 6f66-864q) · 2022/02/19(土) 18:48:24.57

>>148
私はドロップボックスに RCS のレポジトリを置いていたのですがユーザー認証が厳しくなったときにレポジトリ全部がおじゃんになってしまいました、これは痛い、痛すぎる…
もう懲りてバージョン管理システムを使わなくなってしまった…

そろそろ git をはじめようかと思案中です

2022/02/19(土) 18:58:23.53

絶対など絶対にない！

2022/02/19(土) 21:45:46.48

絶体絶命でも死なない

2022/02/21(月) 01:45:01.56

絶対領域ならある

2022/02/21(月) 02:37:10.15

絶対可憐

2022/02/21(月) 02:52:14.84

絶対音感の自分が通りますよ

2022/02/21(月) 06:15:17.92

絶対運命黙示録