Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
223 / 3841 ﾍﾟｰｼﾞ ←次へ　｜　前へ→
【78002】Re:別ファイルにあるセル範囲を配列に格...
発言  β  - 16/3/3(木) 11:36 -
引用なし
パスワード
   ▼ayu さん：

>左側で指定したセル範囲に右側のA2のFormulaをコピーし、
>右側のセル範囲の値を左側で設定したシートのセル範囲にコピーする
>ということであってますでしょうか？

A2のFormula というのが、ちょっと変ですけど、以下のようなことです。

たとえばステップ実行はご存じでしょうか。
ＶＢＥ画面で、Sample2 の任意のところをクリックしてF8 を押します。
最初は　Sub Sample2() が黄色くハイライトされます。
で、そのまま F8 を押していきます。
　　shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
ここが黄色くなったら F8を押さず、マクロブックの　"転記先のシート名" をみてください。
この A1:F2 に、元ブックを参照する参照式が入っています。

式と表示されている値を確認したら、ＶＢＥ画面に戻り End Sub まで F8 を押していって下さい。

Sample がうまくいったということですから、同じ環境（フォルダやブック名やシート名）で実行すれば
シートに参照式がセットされ、そこで参照している値がシートに表示されているはずなんですが。
・ツリー全体表示
【78001】Re:別ファイルにあるセル範囲を配列に格...
質問  ayu  - 16/3/3(木) 10:55 -
引用なし
パスワード
   ▼β さん：

こんにちは。先日は色々教えて頂きありがとうございました。あの後実証してみる時間がなくて、最近やっと時間ができましたので、頂いたコードを動かしてみました。最初のサンプルは動作が確認できたのですが、２つ目の方はエラーは出ないのですが、転送先のシートに何も表示されません。そこで、質問があるのですが、

>　　shT.Range("A1:F1").Formula = ref & "A2"
>　　shT.Range("A2:F2").Formula = ref & "A9"
>　　shT.Range("A1:F2").Value = shT.Range("A1:F2").Value

の部分は、左側で指定したセル範囲に右側のA2のFormulaをコピーし、
右側のセル範囲の値を左側で設定したシートのセル範囲にコピーするということであってますでしょうか？
お時間のある時にお返事頂ければ幸いです。
よろしくお願い致します。


>▼ayu さん：
>
>もう１つ。
>転記レイアウトは　アップ済みのものとはかえてあります
>外部参照数式を使い、ブックを開かないタイプ。
>処理上もループをなくしてあります。
>元ブックのフォルダは、アップ済みのもの同様、マクロブックと同じフォルダにしてあります。
>
>Sub Sample2()
>　　Dim fPath As String
>　　Dim ref As String
>　　Dim shT As Worksheet
>　　Dim r As Range
>　　
>　　fPath = ThisWorkbook.Path
>　　ref = "='" & fPath & "\[元のブック.xlsx]該当のシート名'!"
>　　Set shT = ThisWorkbook.Sheets("転記先のシート名")
>　　shT.Cells.ClearContents　　 '転記前にクリア
>　　shT.Range("A1:F1").Formula = ref & "A2"
>　　shT.Range("A2:F2").Formula = ref & "A9"
>　　shT.Range("A1:F2").Value = shT.Range("A1:F2").Value
>　　
>　　With shT.Range("A2:F2")
>　　　　.Replace 0, Empty, xlWhole
>　　　　On Error Resume Next
>　　　　.SpecialCells(xlCellTypeBlanks).EntireColumn.Delete
>　　　　On Error GoTo 0
>　　End With
>　　
>End Sub
・ツリー全体表示
【78000】Re:再び質問
発言  ichinose  - 16/2/28(日) 15:46 -
引用なし
パスワード
   ちょっと訂正です。

>
>標準モジュールに
>
>Sub mk_sample()
　　With Range("a1:b10")
　　　 .Columns(1).NumberFormatLocal = "yyyy/m/d"
　　　 .Formula = Array("=datevalue(""2016/2/1"")+row()-1", "=char(64+row())")
　　　 .Value = .Value
　　End With
>End Sub
書式設定しないと　日付に見えませんでした

>
>
>上記のmk_sampleで作成したデータを日付で検索することを考えると・・・、
>
>
>Sub test()
>　　MsgBox Application.VLookup("2016/2/5", Range("a1:b10").Value, 2, False)
>　　MsgBox Evaluate("vlookup(datevalue(""2016/2/6""),a1:b10,2,false)")
>End Sub
>
>こんな方法があります。
・ツリー全体表示
【77999】Re:再び質問
発言  β  - 16/2/27(土) 19:59 -
引用なし
パスワード
   ▼勤怠システムを自作したい さん：

コード自体のアドバイスは ichinose さんにおまかせして。
やはり　？？？　です。

たとえば今日、2/27 に操作したとします。
で、コンボボックスで年月日を自由に指定できるということなので
2/24 を指定したとします。
きっと、2/24分の　出勤時刻や退勤時刻等を入力したいんでしょうね。

で、一方、リアルタイム時刻が表示されていますね。
その時刻が 10:00 だったとします。10:01 、10:02、・・と、どんどん変わっていっていますが
2/24の出勤時刻は 9:00 だったとします。
そうすると、操作する人は　リアルタイムが 9:00 になるまで、ず〜〜っと、23時間ほど待つのでしょうか。
で、9:00になった、よし、入力　というところで、もたもたして 9:01 になった。
わぁ、だめだということで、また 24時間待つのですか？
・ツリー全体表示
【77998】Re:印刷が交互に出ます。
発言  γ  - 16/2/27(土) 6:25 -
引用なし
パスワード
   >日にち毎に順番にプリントアウトしたいのですが
>交互に出ます。
不思議ですね。
本当に交互に出るのですか？
たいていは順番で印刷されるが、
時々、順番に従わないケースがある、
といったことならありそうに思います。

それは、印刷命令実行は順序通り行われているが、
プリンター側の事情で順序が狂うことはありうるのかな、と思うからです。

少し時間がかかりますが、印刷命令実行のあとに遊びの時間をいれてみたら
どうでしょうか。

本当はAPIのSleepのほうがよいのですが、
Application.Waitで5秒程度待ちをいれてみてはどうですか？

ちなみに、コードはインデントしないという方針でも採っているんですか？
これが気にならないのは、よほどあなたの能力が高いのですね。
・ツリー全体表示
【77997】印刷が交互に出ます。
質問  room2  - 16/2/26(金) 8:58 -
引用なし
パスワード
   日報印刷を作りました。
日にち毎に順番にプリントアウトしたいのですが
交互に出ます。何処をどの様にすれば良いか教えてください。

Sub 日報印刷()
Dim firstday As Date
　　firstday = InputBox("作成したい月の最終日を入力するんだお！", "1ヶ月分印刷しゅます", Date)
　　Worksheets("a").Range("s1") = firstday


Dim N As Integer
Dim M As Integer
With Range("s1")
If IsDate(.Value) Then
M = Month(.Value)
Do
ActiveSheet.PrintOut Preview:=False ' False <--プレビューOn/Off
.Value = .Value - 1
Loop Until Month(.Value) <> M
MsgBox "印刷完了しました。", vbInformation
Else
MsgBox "日付が認識できません。", vbExclamation
End If
End With
End Sub
・ツリー全体表示
【77996】Re:couintifの高速化
発言  β  - 16/2/25(木) 16:52 -
引用なし
パスワード
   ▼ゆきみち さん：

これぐらいのデータであれば、どんなやり方でも固まるということはありえないですねぇ。
もちろん、【正しい】処理をすればという前提ですが。


>COUNTIF(k2:k2,K2)を最終行までドラッグして、

COUNTIF(K$2:K2,K2) じゃないですか？

動かしているコードをアップされてはいかがでしょうか？
・ツリー全体表示
【77995】couintifの高速化
質問  ゆきみち  - 16/2/25(木) 14:10 -
引用なし
パスワード
   初めまして。
ちょっと仕事で行き詰ってしまったのでこちらで質問させていただきます。

20行、18列のデータをエクセル2010で処理しております。
Ｋ列にＩＤ、Lに日付け、Ｍに時間データが入っており
Ｋ列のIDをキーにして、各ＩＤごとに一番遅い時間を抽出したいと思っております。

考え付く方法として、まずこれらの列をデータの並べ替えで大きい降順にならべ
COUNTIF(k2:k2,K2)を最終行までドラッグして、その後にオートフィルタ、という方法に挑戦しようとしましたが、何度やっても途中で固まってしまいます。

記録のＶＢＡにしてみましたが、やはり途中で固まってしまいました。
なにか、良い方法があれがご教示いただけると幸いです。

宜しくお願いします
・ツリー全体表示
【77994】Re:再び質問
発言  ichinose  - 16/2/25(木) 2:11 -
引用なし
パスワード
   一例です。

新規ブックにて

標準モジュールに

Sub mk_sample()
　　With Range("a1:b10")
　　　 .Formula = Array("=datevalue(""2016/2/1"")+row()-1", "=char(64+row())")
　　　 .Value = .Value
　　End With
End Sub


上記のmk_sampleで作成したデータを日付で検索することを考えると・・・、


Sub test()
　　MsgBox Application.VLookup("2016/2/5", Range("a1:b10").Value, 2, False)
　　MsgBox Evaluate("vlookup(datevalue(""2016/2/6""),a1:b10,2,false)")
End Sub

こんな方法があります。
・ツリー全体表示
【77993】Re:再び質問
質問  勤怠システムを自作したい  - 16/2/24(水) 11:53 -
引用なし
パスワード
   たくさんの返答を頂きありがとうございます。
やりたいことを分かりやすく伝えるというのは難しいことですね。

ユーザーフォームがないと説明しにくいのでフォームの画像を用意してみました。
://ur0.link/sfUQ

各種ボックスやラベルの配置がこのようになっており、目的は勤怠システムの構築です。
左上から順番に解説していくと、
日付→任意で変更できるが起動時に今の日付を自動取得
名前の選択→操作する人を取得
操作オプションの選択→打刻操作の制御
タイマー→今のリアルタイム時刻を表示しているだけ
実行ボタン→操作オプションに応じた操作を実行しそのデータを名前に一致したシートのセルに格納していく（例えばシート名でAさんというものがあり、Aさんが操作で出勤を選択し、実行を押した場合、日付ボックスに該当する日付をシートAさんから検出し、操作オプションに合わせたところに現在時刻を格納していきます。）
わかりにくいのでシートの配列も画像にしました→://ur0.link/sfXT

そして例としてC3、D3、E3、F3にデータが格納されていますが、今回質問しているのはこのデータを日付及び名前をフォーム上のコンボボックスで選択することで、シート上に該当するデータが有った場合はそのデータをlabel15,16,17,18（画像ではラベルをコピーしたので全部15になってますが）にそれぞれ表示するということですね。データがなければ空白が出力されます。
なので過去の日付が選択されていればその日のその人のデータをフォーム上に表示することが出来るようになります。

伝わりますでしょうか。
・ツリー全体表示
【77992】Re:再び質問
発言  β  - 16/2/24(水) 7:36 -
引用なし
パスワード
   ▼ichinose さん：

>日付の検索は　書式によって難しいことがありますから、
>書式のを確認しながら行ってみてください。
>

そうなんですよね。日付検索はフィルター関連も含めやっかいですね。
私の経験でいえば効率は悪いのでしょうけど、ワークシート関数のMATCHを使い
検索値にValue2を与えれば、検索できると思います。
・ツリー全体表示
【77991】Re:再び質問
発言  ichinose  - 16/2/24(水) 6:55 -
引用なし
パスワード
   ▼勤怠システムを自作したい さん：
>今回やらなければならないことはコンボボックスの内容に応じてユーザーフォームにあるラベルの表示内容を変えることなので、やはりvlookupが必要なのではないでしょうか。
私間違えてました。
名前で検索するのではないのですね！！
名前は、シートの特定ですね　これでシート分け・・・、vbaを使うなら、
必要ないと思いますけどね！！　VBAを使うなら、データベースの構造は簡単な方が良いと思います。


で、日付でその日のデータを検索するということですか？

日付の検索は　書式によって難しいことがありますから、
書式のを確認しながら行ってみてください。

もっともInitializeイベントうんぬんは、ここで何をしたいのかわかりませんが・・。

ひょっとして、既定Ａさんの本日の日付だから、そのデータを
表示させたいということでしょうかね！！

だとしても、日付の書式の問題が影響しそうです。
・ツリー全体表示
【77990】Re:指定セル条件により別シートにコピー...
お礼  もょもと  - 16/2/23(火) 20:43 -
引用なし
パスワード
   回答は下記を参照願います。
エクセルの学校
[[20160214174530]]　『条件付き書式で塗潰したセルに入力値が無いと保存』


▼もょもと さん：
>[Sheet1]
>　　　A　　　　B　　　　C
>1　りんご　　購入　　100円
>2　みかん　　交換　　120円
>3　ばなな　　購入　　150円
>
>[Sheet2]
>　　　A　　　　B　　　　C
>1　なし　　　廃棄　　130円
>2　ぶどう　　返品　　200円
>3　りんご　　購入　　100円
>4　ばなな　　購入　　150円
>
>Sheet1のB1に「購入」が選択され、必要箇所を
>入力(必要箇所はセルを変色し、変色したセルを
>全て入力しないと保存不可：別投稿で質問中)し、
>保存すると、Sheet1のA1、B1、C1のデータが
>同ファイル上のSheet2のデータが入力されて
>いない行に各々コピペされるようにしたいです。
・ツリー全体表示
【77989】Re:任意のセル値を消したら指定マクロ実...
お礼  もょもと  - 16/2/23(火) 20:43 -
引用なし
パスワード
   回答は下記を参照願います。
エクセルの学校
[[20160214174530]]　『条件付き書式で塗潰したセルに入力値が無いと保存』

▼もょもと さん：
>1)任意セルに値を入力するとロックさせたい。
>2)ロックした後に編集したい場合はボタンを押して
>別のイベントを起動してロック解除したい。
>3)ロック解除後は手動で上記2)とは別のボタンを押して
>イベントを起動し、再ロックしたい。
>
>1)〜3)の動作確認は出来ているのですが、
>3)の手動で再ロックするのを忘れてしまいがちなため
>2)でロック解除した後に任意のセル値を消した時点で
>3)が自動起動するようにしたいです。
>
>連続投稿して申し訳ございませんが、
>宜しくお願い致します。
・ツリー全体表示
【77988】Re:条件付き書式で塗潰したセルに入力値...
お礼  もょもと  - 16/2/23(火) 20:42 -
引用なし
パスワード
   回答は下記を参照願います。
エクセルの学校
[[20160214174530]]　『条件付き書式で塗潰したセルに入力値が無いと保存』

▼もょもと さん：
>申し訳ございません。
>どちらのサイトで質問したら良いか分からず、
>マルチポストとなってしまいました。
>引き続き宜しくお願い致します。
>
>▼β さん：
>>▼もょもと さん：
>>
>>質問箱IVは、マルチポストを許容しています。
>>また、エクセルの学校もマルチポストを容認しています。
>>
>>しかしながら、それぞれの掲示板で、マルチポストに関するルールというか扱い方が明記されていますので
>>目を通しておいてください。質問箱IVでは、上部の
>>
>>【本サイトの基本方針をまとめました。こちら をご一読ください。】の「こちら」をクリックするとでてきますし
>>エクセルの学校も　【初めての方へ】-->【初めての方へ（詳細版）】をクリックするとでてきます。
>>
>>それらを読んだうえで、それぞれの掲示板に定められている適切な措置をとってくださいね。
・ツリー全体表示
【77987】Re:再び質問
発言  yuto  - 16/2/23(火) 20:16 -
引用なし
パスワード
   >▼勤怠システムを自作したい さん：

コンボボックスで指定された人ごとにシートが違うのか？
月ごとにシートが違うのか？といったようなシートのレイアウト
がないと話が進まない気がしています。

ちなみに質問とは関係ありませんが、月末日を算出するなら
以下のような記述もあります。

' 月末日を算出するための式
LastDate = CInt( Day( DateSerial ( Year(Now(), Month( Now() +1), 0)))
・ツリー全体表示
【77986】Re:再び質問
発言  β  - 16/2/23(火) 19:52 -
引用なし
パスワード
   ▼勤怠システムを自作したい さん：

で、質問は何でしょうか？

名前をコンボボックスで選択したら、シートのどこかにある AさんやBさんの、何をどうしたいのですか？
コードを一生懸命おいかけて理解しようとしましたが、要件がまったくわからないのでやめました。
現在時刻を表示しながら　年月日を選択する意味もよくわかりませんし。
・ツリー全体表示
【77985】Re:再び質問
発言  β  - 16/2/23(火) 19:24 -
引用なし
パスワード
   ▼勤怠システムを自作したい さん：

失礼します。

ichimoseさんが言っておられるのは、コンボボックスで選ばれた値を元に
シートをVKOOKUPする必要はさらさらなく、コンボボックスのリストとして
名前とともに、別の情報をセット（つまり見た目は1列のコンボボックスだけど実際は2列）しておけば
その2列目を直接参照できますよということだと思います。

MsgBox云々とは全く関係のないところです。
（その コード中のMsgBox の意味もわかりませんが）

ただ、この2列目の情報は、実行タイミングで、値が刻々変化しているものなのかもしれませんね。
であれば、コンボボックスの2列目にシート上の行番号をセットしておく手もあります。
そうすれば、やはり VLOOKUPを使わず、直接、選ばれた名前の行の特定セルの参照ができますので。
・ツリー全体表示
【77984】Re:再び質問
発言  勤怠システムを自作したい  - 16/2/23(火) 12:06 -
引用なし
パスワード
   ▼ichinose さん：
回答有り難うございます、しかしこれだとユーザーフォーム上にデータが残らずに、メッセージボックスが出たままになってしまいますよね。
今回やらなければならないことはコンボボックスの内容に応じてユーザーフォームにあるラベルの表示内容を変えることなので、やはりvlookupが必要なのではないでしょうか。
・ツリー全体表示
【77983】Re:再び質問
発言  ichinose  - 16/2/23(火) 8:28 -
引用なし
パスワード
   ▼勤怠システムを自作したい さん：

>ここをVlookupで解決しようとしましたが訳がわからなくなってしまいました。
コンボボックスって優れものです。


新規ブック（Sheet1というワークシートがある）にて


Sheet1のセルA1からB5に以下のようなデータがあったとします。

　Ａ　　　　　　　　　Ｂ
白岡あさ　　　　 あさが来たの主人公
白岡新次郎　　　 あさの夫
白岡千代　　　　 おさの娘
白岡榮三郎　　　 新次郎の弟
眉山はつ　　　　 あさの姉


ユーザフォームを一つ作ってください（UserForm1）

コンボボックス（ComboBox1）を一つ配置してください

Userform1のモジュールに

Option Explicit
Private Sub ComboBox1_Change()
　　With ComboBox1
　　　 If .ListIndex >= 0 Then
　　　　　MsgBox .List(.ListIndex, 0) & vbCrLf & .List(.ListIndex, 1)
　　　 End If
　　End With
End Sub
Private Sub UserForm_Initialize()
　　With ComboBox1
　　　 .Style = fmStyleDropDownList
　　　 .List = Worksheets("sheet1").Range("a1:b5").Value
　　　 .ColumnCount = 1
　　End With
End Sub


標準モジュールに


Option Explicit

Sub test()
　　UserForm1.Show
　　
End Sub

testを実行し、UserForm1を表示させ、コンボボックスを選択してください。

対応したメッセージも表示されます。

このように使うと　検索機能が不要です。
・ツリー全体表示
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
223 / 3841 ﾍﾟｰｼﾞ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free