指定した時刻にセルの値をほかのセルにコピー蓄積

質問

よろしくお願いします。

各シートのＡ99に（個別に）外部から取り込んだＤＤＥのデータがあります（常に動いているデータ）シート1のＤ3セルに　=TODAY()　　　　　　が入っています。（一応）
　　　隣のＥ3セルに　=NOW()-TODAY()　日付を引いた時計が入っています。

この時計を使って（別にほかの方法でもよいのですが）各シートのＡ99にあるセルの値を
Ａの列の指定されたセルに振り分けたいのですが。　
朝のＥ3のセルの値が6：05ジャストになったら
　　　　　　　Ａ99データをＡ100に貼り付け
　　　Ｅ3のセルの値が6：10ジャストになったら
　　　　　　　Ａ99データをＡ101に貼り付け
　　　Ｅ3のセルの値が6：15ジャストになったら
　　　　　　　Ａ99データをＡ102に貼り付け　　

　　　以後続く
　　　　　　　Ａ387（翌朝の5：55まで）まで

プログラムスタートした時点で、その時間から蓄積していく感じにしたいんですけど、
例）プログラムスタートしたのが、途中からの場合、そこまでの間のＡ列は空白のまま

こんな処理なんですけど、可能でしょうか。
一応、今使ってるプログラムでは、
（各シート別）Ａ99データを　
プログラムスタート　　　　　
　　↓
Ａの100に記憶
　　↓
5分置きに下（Ａ列いちばん下のセルだと思うんですけど）に蓄積していく（途中から始めた場合は、自分でＡ99データをコピー時間の指定したセルに貼り付けて、あとはプログラムを走らせている）

と、こんな感じになっています。
私事ですが。各シート（11枚ほどあります。増える予定）に、一日4回も5回も調整するのが面倒なもので、途中のＰＣの動かしてないときのデータは別にいらないので、出来れば簡単にできないものかと、質問させていただきました。

何方か、お詳しい方よろしくお願いします。

回答No.5

ki-aaa · ベストアンサー

とりあえず、シートは一つ"Sheet1"としています。

先に時間を記入します。
A100 6:05
A101 6:10
.
.
A314 23:55
A315 0:00
A316 0:05
A317 0:10
.
A3386 5:55

オートフィルで入力する場合は、A315 0:00までいったん入力する
その後、A316 0:05, A317 0:10を手で入力しオートフィルで入力する

標準モジュール

Option Explicit
Private Gyo As Long
Private 呼び出し回数 As Long
Private 呼び出し時間 As Date

Sub Auto_Open()
　　If 呼び出し回数 > 0 Then Exit Sub '二重呼び出しを避ける
　　呼び出し回数 = 0
　　Call 呼び出し時間と行のセット2

　　If 呼び出し回数 < 0 Then Exit Sub
　　Application.OnTime 呼び出し時間, "繰り返し用サブ2"
　　呼び出し回数 = 1
　　Application.StatusBar = _
　　　　　"次回の呼び出し時間... " & 呼び出し時間
End Sub

Private Sub 繰り返し用サブ2() '自分自身を呼び出す
　　'やりたい処理をする　ここから
　　Dim ymd As Date
　　If 呼び出し時間 < TimeValue("6:00:00") Then
　　　　ymd = Date - 1
　　Else
　　　　ymd = Date
　　End If
　　With ThisWorkbook.Sheets("Sheet1")
　　　　If .Range("B99").Value <> ymd Then
　　　　　　.Range("B99").Value = ymd
　　　　　　.Range("B100").Resize(287, 1).Copy .Range("C100")
　　　　　　.Range("B100").Resize(287, 1).ClearContents
　　　　End If
　　　　.Range("B" & Gyo).Value = .Range("A99").Value
　　End With
　　'やりたい処理をする　ここまで
　　Call 呼び出し時間と行のセット2
　　If 呼び出し回数 < 0 Then Exit Sub
　　Application.OnTime 呼び出し時間, "繰り返し用サブ2"
　　呼び出し回数 = 呼び出し回数 + 1
　　Application.StatusBar = _
　　　　　"次回の呼び出し時間... " & 呼び出し時間
End Sub

Sub Auto_Close()
　　呼び出し回数 = -1000
　　On Error Resume Next
　　Application.OnTime 呼び出し時間, "繰り返し用サブ", , False
　　Application.StatusBar = False
　　On Error GoTo 0
End Sub

Private Sub 呼び出し時間と行のセット2()
　　Dim s As Single
　　With ThisWorkbook.Sheets("Sheet1")

　　s = Time + TimeValue("0:00:02")
　　If s < TimeValue("0:05:00") Then
　　　　Gyo = 316
　　ElseIf s < TimeValue("5:55:00") Then
　　　　Gyo = Application.WorksheetFunction.Match _
　　　　　　　(s, .Range("A316:A386"), 1) + 316
　　ElseIf s < TimeValue("6:05:00") Then
　　　　Gyo = 100
　　Else
　　　　Gyo = Application.WorksheetFunction.Match _
　　　　　　　(s, .Range("A100:A315"), 1) + 100
　　End If
　　呼び出し時間 = .Range("A" & Gyo)

　　End With
End Sub

お礼

ご回答いただき、大変ありがとう御座います。

大変素晴らしいです。
完璧に動いてくれています。
こんなに長々とコードまで書いていただき、ありがとう御座います。
物凄く今後活躍してくれそうです。
私事ですが、今こうしてみると、たいして知識もないのに、難しい事をやろうとしていたのだと痛感させられます。
やはり、もっと勉強が必要ですね。
大変勉強になりました。
本当に、どうも、ありがとう御座いました。

回答No.7

keithin

補足質問がありましたので，ちょうどよかったので少し追加します。
一つ命令を書き足して
private sub Worksheet_Calculate()
Cells(99 + Application.Round((Range("E3") - TimeSerial(6, 0, 0)) / TimeSerial(0, 5, 0), 0) + iif(time < timeserial(6, 3, 0),288,0), "A") = Range("A99").value
end sub
としておいてください。
日付を超えて朝まで連続使用することを見落としていました。失礼しました。

＞前の補足でもありましたようにＥ3の時刻の更新の部分が必要みたい（たぶん？）ですね。

いいえ，そうではありません。

先にお話しした通り，回答しているマクロは「ＤＤＥを組み込んだシートで，ＤＤＥの更新をマクロが自動で受けとって動作する」ように仕込んだ物です。Ｅ３の更新を調べているのではありません。
こう言えば判るでしょうか，Ｅ３が更新されるのと，このマクロが動作するのは，どちらもＤＤＥが値を更新した結果の現れです。
ＤＤＥが動作していないブックにただ組み込んでも，そこにＥ３の式があっても，何も起こりません。

お礼

何度もお返事ありがとう御座います。
＞Ｅ３が更新されるのと，このマクロが動作するのは，どちらもＤＤＥが値を更新した結果の現れです。
そうだったんですか。
申し訳ありません。間違えてました。
マクロをＶＢＡのシート1に入れて動かしていたんですけど、「更新していかないので」
おかしいな？Ｅ3セルをカチカチいろいろ動かしていたら「下に記録して行ったので」この辺かな、と勝手に思ってました。
「土日でＤＤＥの更新が止まっている状態」だったので、動かなくて当然ですね。

私自身、もっとコードもすらすら読めて、理解できるようにならないとダメですね。

大変、今回の質問に対するみなさんの回答は勉強になりました。

わざわざ貴重なお時間を割いていただき本当にありがとう御座いました。

回答No.6

keithin

ん？
DDEってホントにDDEの事だったんですね。
どっかから入手した取引ソフトの事だと思ってましたが，マーケットスピードの親戚でしたか。
＃いやスミマセン，株だのなんだのをやってる方からのご相談は，皆さん揃って「自分の知ってる事はみんなも知ってる（から説明したくない）」という方が多くて，DDEもそっち方面の何かソフトだと思いこんでました。

では回答４のマクロは

private sub Worksheet_Calculate()
Cells(99 + Application.Round((Range("E3") - TimeSerial(6, 0, 0)) / TimeSerial(0, 5, 0), 0), "A") = Range("A99").value
end sub

としてください。

あと私はスミマセン，rukukuさんじゃありませんので，rukukuさんの回答について質問をされても困ります。
このマクロは，DDEの数式を実際に組み込んであるシートで使うために回答した物です。

お礼

どうも、何度もご回答ありがとう御座います。

申し訳ありません。かなり説明不足の部分がありましたね。
＞マーケットスピードの親戚でしたか。
　そうですね。マケスピのＲＳＳ関数みたいなＤＤＥのデータ（セルの値）です。
とりあえず、マクロを動かしました。
前の補足でもありましたようにＥ3の時刻の更新の部分が必要みたい（たぶん？）ですね。
もう少しつけ加えれば、このマクロでも使えそうです。
コードが短いのでいいですけどね。

みなさんのおかげで、「やりたい事」ができました。
今後みなさんに教えてもらった事を生かしていきたいです。

どうも、ありがとう御座いました。

回答No.4

keithin

OnTime予約してしまうのは確かに一つの手ですが，状況に関して言うと若干問題の原因になるかもしれません。

●今作成しようとしている転記マクロとDDEの時刻予約が同期していないので，先に転記マクロが作動してしまい，その時はDDEがまだ値を更新していないような失敗も起こりえる
●6:05ジャスト狙いと言えば聞こえは良いですが，現実にはそのジャストタイミングで値が更新されている保証がない
●それに関連して，たとえば6:04に更新されてしまったり，6:07までずれ込んでたりしたのを一体どのセルに転記したいのか，方針が示されていない

一例です。
・DDEが５分おきにA99の値を更新する
・そのタイミングを受けて転記を作動させる
・転記先はそのタイミングでのE3時刻から割り出す
　（下記のマクロは仮に２捨３入として。５分ごと切り捨てとかも有りかも。ゲンミツを期すなら，B列にその時点での時刻を併せて転記するのもよし）

シート名タブを右クリックしてコードの表示を選ぶ
現れたシートに下記をコピー貼り付ける

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$99" Then Exit Sub
Cells(99 + Application.Round((Range("E3") - TimeSerial(6, 0, 0)) / TimeSerial(0, 5, 0), 0), "A") = Range("A99")
End Sub

補足

ご回答いただき、ありがとう御座います。

ちょっと解らないのでお聞きしたいのですが。
ＤＤＥのデータの更新の所なんですが、パソコン内部のデータのやり取りのがどうなっているのか、
詳しく分からないのですが
私の認識だとＡ99に為替のデータを　='MT4'|BID!USDJPY　こんな関数で取り込んで（常に値が入っている状態なんですけど）そのＡ99の値だけをほかのセルに送りたいのですが、
それと作っていただいたプログラムの動かし方が解らないのですが
ＶＢＡのSheet1にマクロを入れて
rukukuさんのAutosaveのマクロをThisworkbookに入れて5分おきにマクロを動かす
ということで宜しいのでしょうか？
やってみたのですけど、動かないのですが？
Ｅ3の時刻の所を私の認識不足で=NOW()-TODAY()の関数だけだと自動で更新されないのですね。
自分のプログラムを動かした時は5分間隔で、なぜかＥ3の=NOW()-TODAY()の値が更新するのですが、
この状態だとＥ3を自動で更新させる、やり方が分からなくなってしまいました。
もしその辺お分かりでしたら、よろしくお願いします。

回答No.3

rukuku

＞←ここから再スタート
ここは少々ヤッカイなところです。

そこで補足要求です。
A100はいつも「6：05」のデータでしょうか？（例えば、6:16にパソコンを起動したら、6:20のデータをA103に記録でOKですか？）

また、実際にどのような状況で使いたいのかも、もう少し具体的に教えてください。

補足

どうも何度もすいません。お手数かけます。

　＞A100はいつも「6：05」のデータでしょうか？　
　　　　　はい。各シートともＡ100は6：05データです。　　
　＞例えば、6:16にパソコンを起動したら、6:20のデータをA103に記録でOKですか？）　
　　　　　はい。EXCEl立ちあがた時からの記録をしていってくれればＯＫです。

このプログラムは為替のＤＤＥで取り込んだリアルタイムデータ（Ａ99）を5分おきに記憶させる為のものです。
一応、ワークブックの中に各シート（11枚あり）のＡ99にそれぞれ個別の為替データが入っています。
そのデータを時間指定でセルに振り分けたいのですが、可能でしょうか？
今の使い方としては、パソコンを動かした時にその時刻にＡ列のその時刻の所に自分で値を記入、それと時計を見ながら5分間隔の所でプログラムを手動でスタートのさせている感じです。
一度設定すればＰＣをつけっぱなしにすればいいのですが、
なかなかそういう訳にもいかず、一日に何回も値を入れなおしている状況です。
出来れば、ＥＸＣＥＬを立ち上げた時に勝手にスタートしてくれると
「物凄く助かるのですが」
今使っている自分で作ったプログラムでは、長時間（5時間以上）使っていると
5分間の繰り返しで値を記入しているみたいで、だんだん記入する時刻がずれてくるのですが
一応、記録したデータは後から消してしまうので、多少は気にしません。
（自分で適当に作っている物ですからね。）

もし、このような処理ができるのでしたら、どうかよろしくお願いします。

回答No.2

rukuku

＞「Ａ99データを時間ごとに振り分けるプログラム｝があって、
＞このプログラムを実行（入れておく）させる、と言う事でしょうか？
＞申し訳ありませんが、私としては「Ａ99データを時間ごとに振り分けるプログラム」
＞という処理をしたいのですが。　分かりますでしょうか？

別途、時刻を記録するプログラムが必要です。

以下は、アクティブブックのアクティブシートのA列の最後に現在時刻を記録するプログラムのサンプルです（マクロを使うとはブックやシートの指定に気をつけてください）。
先ほどの回答の"Autosave"を"現在時刻を記録"に変更しますと、５分ごとに時刻を記録します。

Sub 現在時刻を記録()
[A65536].End(xlUp).Offset(1, 0) = Time
End Sub

補足

何度もご回答いただきありがとうございます。
　私の説明不足だったかもしれません。
　Ａ99セルのデータをＡ列に最後に入れるのではなく、
　Ａ100セルには 6：05の（システム時計が）時のＡ99のデータを記入して
　Ａ101セルには 6：10の（システム時計が）時のＡ99のデータを記入していく
　Ａ102　　　　 6：15
　Ａ103　　　　 6：20
　Ａ104　　　　 6：25　
　途中やめたりして（一回パソコンを切ったりして）
　次始めた時にそこまでのデータはなかった物として
　　7時から始めた場合
6：40
　　 6：45
　　　　　　 　　　 6：50
　　　　　　　　　　　　6：55
　Ａ111　　←ここから再スタート　7：00　
　Ａ112　　　するような感じ　　 7：05
　Ａ113　　　　　　　　　　　　 7：10
　Ａ114　　　　　　　　　　　　 7：15

といった感じにしたいのですけど。可能でしょうか？
このrukukuさんの初めのAutosaveのマクロ使わせていただきました。
ちゃんとＰＣの時刻で記録するのでいい感じです。
私の作った「こじつけプログラム」だとinterval？を使っているからか、
かなり4,5時間がたつとものすごい誤差（2.3分変わってくる）が出てきてしまいます。

出来れば、このプログラムを使って完成させたいのでもう少しお付き合いお願いします。

回答No.1

rukuku

こんにちは
以下は、Autosaveというマクロを５分おきに実行するためのマクロです。
Thisworkbookに入れておくと起動時に実行されます。
Autosaveは同じブックの標準モジュールに置きます。
アドオンとして登録しておけばExcelを起動したときに自動的に実行されます。

Private Sub Workbook_Open()
Dim lngI As Byte
Dim lngJ As Byte
Dim ActionTime As String

For lngI = 0 To 23
For lngJ = 0 To 59 Step 5
ActionTime = Format(lngI, "0#") & ":" & Format(lngJ, "0#")
Application.OnTime ActionTime, "Autosave", TimeValue(ActionTime) + TimeValue("00:04:00")
Next lngJ
Next lngI

End Sub

補足

お早いご回答、ありがとうございます。
申し訳ありません、質問自体が少し間違ってましたね。
Ｅ3セルに　=NOW()-TODAY()　この部分が自分のプログラムを動かさないと、動かない事を忘れていました。
すいません。
それで、お答えいただいたプログラムの事なんですけど、「マクロを５分おきに実行するためのマクロ」と言う事なんですけども、「５分おきに実行」とは、「Ａ99データを時間ごとに振り分けるプログラム｝があって、このプログラムを実行（入れておく）させる、と言う事でしょうか？
申し訳ありませんが、私としては「Ａ99データを時間ごとに振り分けるプログラム」という処理をしたいのですが。　分かりますでしょうか？
せっかくですので、このプログラムを使って、いろいろやってみたいと思います。
（ちょっと使い方が良く分かりませんが、あんまり詳しくないので）

もし、お分かりになられる方が、何方でもいいので、宜しくお願いします。