Excel VBA 質問スレ Part62

2019/06/24(月) 00:21:37.48

!extend:checked:vvvvv:1000:512

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

※前スレ
Excel VBA 質問スレ Part61
http://mevius.5ch.net/test/read.cgi/tech/1556203263/

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

2019/09/06(金) 23:27:12.11

>>892
ソースのリンク先まで貼って頂き、ありがとうございます。
シリアル値の「１」を日付で表示した場合、やはりワークシートとVBAで起点が１日ズレてるんですね

「期限の５日前までのものを抽出する」って操作をワークシートでやってたのですが
これをVBAに置き換えたら何か結果が変で…、しばらく変だったことにも気付かず…

とりあえず、そういうものと思って注意して使うしかないですね
しかし>>848のソートもだけど、いろいろ思いがけないことがあるもんですね

2019/09/07(土) 06:45:32.58

勤務表の作成の相談です。
以前も少しさせていただいたのですが、下記の条件で作ることは可能ですか？

1月の勤務時間160時間週40時間
週休2日
月に1度は2連休を入れる
勤務形態の組み合わせは2種類
（1）昼8、昼12、遅8、夜12
（2）昼8、遅8、夕8、夜8
夜12、夜8の次の日は休みで無くてはいけない
昼12.遅8の次の日は昼8、昼12を入れてはいけない
勤務の希望や休みの希望は必ずその通りにしなくてはいけない
経験年数によって行える業務も違うため、どの日も均等に業務ができるように組まなくてはいけない
業務内容は☆、△、□、◇、●の5種類

2019/09/07(土) 08:49:24.82

>>894
質問は可能かということだから答えるけど可能

コード晒せとか言うのであれば
長くなるのでここでは不向き

どんな形態の勤務表作るのかは知らないけど
ボタン押して勤務表の記載内容をチェックして
ダメならメッセージボックスでその旨表示して
ダメなセルを着色かなんかするようなイメージ？

2019/09/07(土) 09:13:25.23

盆正月GW諸々考慮しないでいいのか心配になる内容
完成してからの修正が本番になる予感

2019/09/07(土) 09:35:53.34

そして1から作り直した方が早い案件に

2019/09/07(土) 11:53:44.36

879
すいません、一つ目はcountfsでやれました。
2つ目が出来ません
直接、☆優先、とだけ文字を書いたところなら先頭だけ赤くできますが計算結果の文字の一部を赤くするにはどうすればいいですか？
関数でもVBAでも何でもいいです。openpyxlでも。

2019/09/07(土) 12:19:08.18

Range("A1").Characters(Start:=1,Length:=1).Font.ColorIndex = 3

2019/09/07(土) 13:03:36.73

>>898
アンカの付け方覚えて

2019/09/07(土) 13:27:28.99

>>898
これは俺が悪かった。
数式に対してはセル内文字列の一部分に対して書式の設定は出来ないようだ。
条件付き書式で出来るかと思ったけど、条件付き書式もセル内文字の一部分に適用出来ない。

つまり、出来ないと思う。

2019/09/07(土) 13:39:34.44

固定値だったらできるのにね

2019/09/07(土) 19:11:14.74

>>895
勤務、休み希望を先に選択し残りの空白部分は自動で組めるようにしたいです。

2019/09/07(土) 19:34:32.24

判定や入力もVBAでやってRange.caractors(1,1)から変えればいいじゃん

2019/09/07(土) 20:03:22.01

>>904
直接数式の入ったセルに対してやりたいんだろ。

2019/09/07(土) 20:04:40.69

数式の入ったセルにRange.caractors(1,1)で書式変更しても反映されない。

2019/09/07(土) 20:05:24.41

>>903
自動部分のパターンが明確に決まってるなら出来るよ

**デフォルトの名無しさん** (ｽﾌｯ Sd62-TE+O) · 2019/09/07(土) 20:07:08.23

>>906
根性が足りないだけでは。

2019/09/07(土) 20:19:51.67

>>907
自動部分のパターンとは？

2019/09/07(土) 20:30:18.66

フツーに行単位でセルの色を変えた方が見やすいんじゃないの

2019/09/07(土) 20:38:50.56

>>909
自動で勤務の振り分けをしたいんじゃないのか？

2019/09/07(土) 20:44:02.71

セル分けれは解決じゃん

2019/09/07(土) 20:48:53.79

（ほぼ）リアルタイムに状況見ながら各自が休みとか入れさせるのか、単に休みの希望だけオフラインで集めて、スケジュール担当者だけがシステム触るのかでも難易度が全然違う

今時やるなら前者で行ってもらいたいものだが、仕様考えながら完成まで一ヶ月でできるかも怪しい気がする

2019/09/08(日) 06:46:04.07

先頭の記号一文字分だけ色をつける仕様が糞仕様だと気付くことが必要だよな

2019/09/08(日) 10:33:26.83

いやでも実際文字全体をカラーにされると読みづらいから先頭の色付記号だけでアテンション引いてくれる方が見やすくてデザイン性は高いよ

2019/09/08(日) 10:51:40.94

1列追加すればいいだけだわな

2019/09/08(日) 11:08:41.98

俺はセル内改行された文字列のセル内1行目だけを
フォント大きめで色を付けて強調させるマクロをよくつかう

2019/09/08(日) 11:26:28.31

それでも直接文字列でしょ？
IFで選択した文字列に使えないのが雑魚すぎる
VBAマジでアップデートしないかなぁ
今時continueするためにラベル+gotoが必要とかマジでお笑いだわ

2019/09/08(日) 11:40:50.50

>>911
そうです。条件に合う勤務表を自動で作成したいです

>>913
オフラインで休み希望等を確認し担当者が入力。あとは、自動作成というのを作りたいです。

2019/09/08(日) 12:47:36.20

>>919
たぶん既に作ってる表は、一ヶ月分が1シートで横方向がその月の日、縦が氏名。その下に☆△等の個数な感じかな。
賢明なら、月初と月末にかかる一週間もそのシートに含めてあるか…（含めてないなら含めておくとプログラミングが楽になる）

対象が何人かわからないけど、条件を満たす候補が何十（百）もあるときどうするのか
そうなるなら、休み希望だけでなく出勤希望も可能とする。そうすると連休よりバラしたい人は休みの前後に出勤希望を入れればいい

プログラム的には、休みや出勤を本人希望か自動設定かを区別しておくこと（見た目でも）
あとは、粛々と仕様のロジックを組み込むだけかな

学校の時間割り作るのと似てるので、そっちで検索すると参考なることあると思う

2019/09/08(日) 13:14:35.01

>>919
他には、氏名ごとに日のあとに、2連休有無、各週、月の勤務時間なども出しておきたい

まず作るのは、仕様に合った勤務割り振りになってるかチェックするプログラム
それができていれば、自動割り振りできてなくても手作業で始められるし、できたとこから随時自動化を進めていける

仕様へのリンクメモ >>894

2019/09/08(日) 15:55:54.33

>>918
ある程度仕方無いけど、数式とVBAは混在しない方が良い。

2019/09/08(日) 16:42:59.49

素直に条件書式アイコンセット使った方がいいんじゃね

2019/09/08(日) 19:29:36.97

へー数式セルには部分書式変更できないのか

2019/09/08(日) 19:56:10.74

できたきがする

2019/09/08(日) 20:09:38.22

ほんとうですかぜひやりかたおしえてください

2019/09/09(月) 08:28:39.19

>>926
出来ません

2019/09/09(月) 08:32:44.50

はい。。。

2019/09/09(月) 09:03:33.39

セルにシェイプやフォームを重ねてそっちに色を付ける
別シートに値コピーして、そこに色を付ける

2019/09/09(月) 11:05:11.84

数式列を隠しにして横に値持ってきて色付ければいいんじゃないの？
どうせVBAで色付けるんでしょ？

2019/09/09(月) 15:41:30.04

>>921

それもExcelで作れます？

2019/09/09(月) 17:14:48.93

集計とチェックは余裕でしょ

2019/09/09(月) 19:35:42.46

昨日まで動いていたREPLACE関数が仕事しないんだけどおま環？

2019/09/09(月) 19:46:17.72

>経験年数によって行える業務も違うため、どの日も均等に業務ができるように組まなくてはいけない
悩みどころがあるとすればこの均等な業務とやらが明確に決まっているかどうかだな

**デフォルトの名無しさん** (ｽﾌｯ Sd62-TE+O) · 2019/09/09(月) 19:51:51.00

REPLACE関数はREPLACEされました。

2019/09/09(月) 19:52:51.16

>>933
マクロ無効になってるとか式に問題があるとか

2019/09/09(月) 20:14:41.08

再起動して当該部のマクロ記述しなおしたら直った
原因はようわからんかったが

2019/09/09(月) 20:32:52.02

>>937
どういう風に仕事しないのか分からんが、エラーになってデバックでREPLACEが黄色になってるんだったら参照設定が壊れて、基本関数でさえ参照出来なくなる事例かな。

LEFTとかCSTRとか当たり前に使える筈のものが使えなくなった時は一番最初に疑う。

2019/09/09(月) 21:29:13.83

cells.find ""で直るんでしょ

2019/09/10(火) 01:25:54.03

色々試したが数式のセルは部分書式変更できんかった

2019/09/10(火) 02:18:24.39

>>933
yes

2019/09/10(火) 19:21:57.01

どーーしても数式でやりたいなら列分けるぐらいしか

2019/09/10(火) 22:00:56.11

素直にVBAで計算しな。
そしたらTextオブジェクトにもアクセスできる。式の部分文字列には色付けられないって、最初に言ったろ？

2019/09/10(火) 22:07:47.05

ワークシート関数で評価した結果をセルに書き込んでから書式変えるのが一番良いよ

2019/09/11(水) 22:39:41.11

勤務表作成のコードが分かりません。
どのセルにどのようなコードを入れればいいですか？

2019/09/11(水) 22:45:58.07

>>945
どういう動作をするコード？

2019/09/11(水) 23:24:32.65

>>945
エスパーじゃないので何がどう分からないのかすら分からない
全部分からないってパターンなら正直ここでは解決しないと思う

2019/09/12(木) 01:26:23.25

>>946
>>894

>>947
シートを２枚作成し
１枚目（勤務表）
A列に名前
仮に20人所属としA列2行目～A列21行まで名前を入力
B列～AFまでに日付

２枚目（参照）
勤務形態
ABC列にそれぞれの勤務形態のグループ（元々仕様では２種だったが、３種必要だった）
A列：昼８、昼12、遅8、夜12
B列：昼８、遅8、夕8、夜8
C列：昼８、遅8、夕8

A列に名前B～J列10行目にそれぞれの行える業務について
A列の名前を入れ、B～J列10行目に業務項目を入力し11行目以降にそれぞれの行える業務については○を入力しその情報を元に
勤務表の(B2:AF21)に勤務を表示させたいのですが、(B2:AF21)それぞれにコードを入力すれば良いのでしょうか？
また、どのようなコードを入力すれば良いのですか？

2019/09/12(木) 10:44:45.50

壊滅的にセンスと知識がないからやめたほうがいいよ

2019/09/12(木) 12:00:14.99

CSVからxlsmに一括変換するコードがネットに落ちてない…わかる人いますか？

2019/09/12(木) 12:01:46.10

全部の問題を一気に片付けようとするな

2019/09/12(木) 12:18:23.71

馬鹿ばっかになった

2019/09/12(木) 12:24:16.91

>>948
ちょっと何言ってるか分からない。

2019/09/12(木) 12:32:48.27

>>948
スマンがヒント下さいって程度の話なら答えられると思うけど
やり方全部分かりません全部教えて下さいって話だと他所を当たった方が良いよ

2019/09/12(木) 18:27:47.01

>>950
なぜCSVを、xlsmに？
なにをさせたいのかよくわからん。

2019/09/12(木) 19:26:34.35

ググって出来なかったら諦めろ

2019/09/12(木) 19:55:19.21

>>948
隣の席の事務のオバさんに聞けば？

2019/09/12(木) 20:27:05.99

>>948
VBAはセルではななくコードエディターに入力して動かすもの

**デフォルトの名無しさん** (ｽﾌｯ Sd9f-yzII) · 2019/09/12(木) 20:33:53.75

csvを列幅自動調整してxlsxに一括変換するやつなら作ったことあるけど

2019/09/12(木) 20:44:23.48

CSVそのままだと数字しかない列を数字と読んじゃうからイマイチなんだけど
0001とか1になってまうやつね
そういうのうまく文字列として読む方法ないですかVBAで

**デフォルトの名無しさん** (ｽﾌｯ Sd9f-yzII) · 2019/09/12(木) 20:54:02.93

>>960
Excelで開いて上書き保存するとセルの値がそう書き換えられる仕様だったような。

2019/09/12(木) 21:07:54.40

>>960
読み込むセルの書式設定を文字列にする

2019/09/12(木) 21:37:35.27

>>960
普通はExcelで開かない。
テキストとして開く。
で、独自処理。

2019/09/12(木) 21:44:44.92

>>960
エクセルCSVデータが勝手に変わるのを防ぐ文字列読み込み法
https://www.excelspeedup.com/csvmojiretsu/

『1 csvファイルをそのままの形で取り込む３つの方法
1.1 「外部データの取り込み」を使って取り込む
1.2 拡張子を「.txt」に変更して開く
1.3 テキストエディタで開きエクセルにコピー貼り付け』

2019/09/12(木) 21:52:57.62

どんな形で開いても書式設定を文字列にするか頭に ' 付けなきゃ0消えた気がするが

2019/09/12(木) 23:20:39.35

いや、VBAでテキストファイルならOpen、Line Input使うとかFileSystemObject使うとかでしょ。

2019/09/13(金) 00:48:49.27

文字列として読み込めば0は消えない
あとLineInputよりクエリのがずっと速い

サンプル

Sub CSVread()
　　With Worksheets("Sheet1")　'読み込むシート
　　With .QueryTables.Add(Connection:="TEXT;C:\tmp\test.csv", Destination:=.Range("A1"))　'ファイル名とセル位置
　　　　.TextFilePlatform = 932　'文字コード指定
　　　　.TextFileCommaDelimiter = True　'カンマ区切り指定
　　　　.RefreshStyle = xlOverwriteCells　'セルに上書き
　　　　.TextFileColumnDataTypes = Array(xlTextFormat, xlTextFormat, xlTextFormat)　'各列を文字列に指定
　　　　.Refresh　'シートに読み込んで表示
　　　　.Delete　'クエリを切断
　　End With
　　End With
End Sub

2019/09/13(金) 06:06:06.50

csvやjsonのような構造化されたデータの読み書きならLineInputよりクエリかな

2019/09/13(金) 19:00:24.08

>>967
LineInputもクエリも表示形式設定しなきゃ文字列として扱ってくれないと思うんだが、そうでない読み込み方があるなら教えてほしい

2019/09/13(金) 19:12:23.50

schema.ini書いてADOでいいよ、そんなもん。

2019/09/13(金) 22:45:51.27

>>967
これだとUTF8で読み込めるんだなー
ありがと

2019/09/14(土) 21:23:34.27

VBA出来る人はSQLも普通に使える人が多いのでしょうか？

**デフォルトの名無しさん** (ｽﾌｯ Sd9f-yzII) · 2019/09/14(土) 21:32:42.15

Sの字もわかりません

2019/09/14(土) 22:28:34.89

>>972
ACCESS VBAを使ってる人なら多いと思うがExcelはそれほどでもない

2019/09/15(日) 00:02:54.14

>>977
使える人もいるが、VBAerはSQLや他のプログラミング言語の知識がなく他の選択肢を持たない極めて低スキルな層が大多数を占める

2019/09/15(日) 00:04:51.24

>>977

2019/09/15(日) 06:56:48.00

低スキルで悪かったな（怒

2019/09/15(日) 07:04:00.27

勉強すればいいのに

2019/09/15(日) 07:09:56.72

>>972
全く違うので関連性はありません

2019/09/15(日) 08:30:42.63

世の中にはSQLが書ければコマンド一発でできることをVBAで猿のようにループ回してる糞コードが山ほどあることを考えると決して無関係ではない
無関係と思いこむことで自らの無能を正当化するのはやめなさい

2019/09/15(日) 09:42:56.34

だれもSQLとVBAが無関係とか言ってないのに何を言ってるんだよ…
VBAが使える人とSQLが使える人の話な

2019/09/15(日) 09:57:44.01

イメージは975
excel から逃れられないひとが使うイメージ

2019/09/15(日) 10:12:56.75

スレチで申し訳ないがM言語を使える人ってどれくらいいるんかね
上司の謎采配でDB導入に待ったがかかってるからExcelでPowerQueryを組んでるんだが誰にも引き継ぎ出来なくなりそうで怖い

2019/09/15(日) 10:19:11.53

PowerQueryは謎な挙動が多すぎる

2019/09/15(日) 15:08:13.09

VBAよりSQLの方が簡単だからSQL覚えておくと良いと思う。
SQLはどう使うかで難しくなるけど。

2019/09/15(日) 15:09:44.24

>>983
M言語は全く分からん。
やってみても良いんじゃない?
様子見もありだけど。

2019/09/15(日) 15:30:29.07

ExcelのM言語ってMUIMPSのことなの？　それとも全くの別物？

2019/09/15(日) 16:59:17.31

M言語の仕様
英語だけど。

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-language-specification

2019/09/15(日) 17:58:13.89

どうしても今すぐ仕事で使いたく、無知なまま質問することをお許し下さい。
ワンクリックでセルに色が付き(塗りつぶし)、色を付けたところを再度クリック(こちらはダブルクリックが理想)すると元に戻る(白くなる)ようにしたいです。
知恵袋で近い回答を見つけたのですが範囲の指定方法が分からず。ただ、こちらは白に戻すのをワンクリックで行うため理想とは若干異なります。一応載せておきます。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub '※１
Select Case Target.Interior.ColorIndex
Case Is = xlNone
Target.Interior.ColorIndex = 3 '※２
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End Sub
※１：数字の 1 はA列を意味します。C列なら 3 です。
※２：数字の 3 は赤で、4=緑、5=青、6=黄、7=ピンク、8=水色、です。

難しくなってしまうようでしたら元に戻す動作もワンクリックで大丈夫です。ご教示頂けると嬉しいです。

2019/09/15(日) 18:10:30.21

>>972
高速化について調べていったら、最終的に行き着くのがSQLって感じ。
あとは、会社のPCにAccessが入ってないとか。

2019/09/15(日) 18:16:17.99

>>989
SelectionChangeは選択範囲を変更した場合のイベントだね
BeforeDoubleClickに変えればいいんじゃない

2019/09/15(日) 18:22:27.35

アクセスをDBと思ってる人は少数だろう