EXCELのVBAで循環参照について

質問

A1とB1を比較して、A1の値が小さいときにB1にA1を代入させたいのですが、
ゼロがB1に入ってきます。

Cells(1, 1).Value = 1
Cells(1, 2).Value = 5
Cells(1, 2).Formula = "=IF(A1<B1,A1,B1)"

上記の場合はB1に1ではなく、ゼロが入ってしまうんです。

よろしくお願いします。

回答No.6

Wendy02 · ベストアンサー

こんばんは。Wendy02です。

VBAのロジックがあまりよく出来ていないようですね。

こちらで類推させていただきました。

Cells(1, 2).Formula = "=IF(A1<B1,A1,B1)"

この式で臨んでいることは、分りました。たぶん、それは、ここの部分だけが、リアルタイム表示をしてほしいということですね。ただし、この式が違っていますね。最初は、「転記マクロ」だけで、試してみてください。データを全部消去すると、回数がリセットされます。タイマー設定で、第0回の初期設定を含めて、第13回目まで行われます。

循環参照される式は、
　"=IF(A1<" & CStr(Buf) & ",A1," & CStr(Buf) & ")"
このような式になります。Buf は、前の値を確保して、数式に含まれます。

Sub SetOnTime()
　 'タイマー設定
　 Dim m_Now As Date
　 m_Now = TimeValue("00:09:00")
　 For i = 0 To 13
　　　'30分ごとに設定
　　　Application.OnTime m_Now + TimeSerial(0, 30, 0) * i, "TransValue"
　 Next i
End Sub
'---------------------------
Sub TransValue()
　'転記マクロ
Dim Buf As Double
Static i As Integer
　'A1,B1 に何も入っていない状態
　If WorksheetFunction.Count(Range("A1:B1")) = 0 Then
　　Range("A1").Value = 999999
　　Range("B1").FormulaLocal = "=IF(A1<999999,A1,999999)"
　　i = 0 'カウンターを初期化
　　Exit Sub
　End If
　i = i + 1
　Buf = Range("B1").Value

　'第13回目
　If i > 12 Then
　　 Range("C65536").End(xlUp).Offset(1).Value = Buf
　　 MsgBox "終了しました。"
　　 Exit Sub
　End If

　'A1,B1 が、999999の第1回目
　If Range("A1").Value <> 999999 Or Range("A1") < Buf Then
　　Range("B1").FormulaLocal = "=IF(A1<" & CStr(Buf) & ",A1," & CStr(Buf) & ")"
　End If
　'1回目は、C1に転記しない

　If i < 2 Then Exit Sub
　　'第2回目
　If IsEmpty(Range("C1").Value) Then
　　 Range("C1").Value = Buf

　　'第3回目～第12回目
　Else

　　 Range("C65536").End(xlUp).Offset(1).Value = Buf
　End If

End Sub

お礼

本日の相場の急落に茫然自失し、お礼が遅くなり失礼しました。
Wendy02さんのおかげで、きれいなVBAが書けました。
貴重なお時間を回答に割いていただき、非常にありがとうございました。

回答No.5

BLUEPIXY

>それ以降はA1セルに手入力をしてB1のセルの値をかえさせたいので
B1の値の決定にB1を参照する式を入れると循環参照になるのは免れません。
なので、《要望されるような式をB1に設定することはできません。》
＃４で提案されているような、A1の値が設定された時にB1の値を変更するマクロにするということになるでしょう。

回答No.4

Wendy02

こんばんは。Wendy02です。

補足のご説明では、よく分りません。

もう少し、具体的に、その手順と目的を説明してくださらないと、おっしゃっている説明では分らないです。

例えば、A1 とB1 を比較して、A1 に、1 を入れ、B1 に5があれば、B1 は、1になる。
A1 に、6 を入れ、B1に1があれば、B1 は、1のままになる

というように説明してくださいませんか？

ちなみに、この説明の場合は、

'<シートモジュール>にこのように入れます。それで、B1 に数字がはいっていて、A1 に数字を入れれば、B1 の数字が変ります。

Private Sub Worksheet_Change(ByVal Target As Range)
　 Application.EnableEvents = False
　 If Target.Address <> "$A$1" And _
　 VarType(Range("B1")) <> vbDouble Then Exit Sub
　 If Target.Value < Range("B1").Value Then
　　　Range("B1").Value = Target.Value
　 End If
　 Application.EnableEvents = True
End Sub

お礼

本来、お礼を書くところに恐縮なのですが、訂正をさせてください。

(2)　初回のデータがA1に入ってきた場合は、その値をB1に転記
　A1:100 B1:100　　この場合はA1(999999)>B1(999999)のため、B1に100を転記
　　　　　　　　↓
(2)　初回のデータがA1に入ってきた場合は、その値をB1に転記
　A1:100 B1:100　　この場合はA1(100)<B1(999999)のため、B1に100を転記

補足

説明不十分で申し訳ありません。

目的は、株式の３０分足をつくりたいと思っています。
３０分足とはA1のセルに不定期にデータがはいってきて、30分おきにA1セルの最大値と最小値を求め、その値を格納するものです。

最小値を求めるほうの例を書きます。
(1)　データの初期設定
　A1:999999 B1:999999　A1とB1には想定される最大値を格納
(2)　初回のデータがA1に入ってきた場合は、その値をB1に転記
　A1:100 B1:100　　この場合はA1(999999)>B1(999999)のため、B1に100を転記
(3)　2回目のデータ以降は、A1とB1を比較し小さいほうをB1に転記
　A1:150 B1:100　　この場合はA1(150)>B1(100)のため、B1は変更なし
　A1:80 B1:80　　　この場合はA1(80)<B1(100)のため、B1は100から80に変更
　　・・・・
　A1:80 B1:80　　　この場合はA1(80)=B1(80)のため、B1は変更なし
　A1:50 B1:50　　　この場合はA1(50)<B1(80)のため、B1は80から50に変更

(4)　(3)を30分繰り返します
(5)　30分を過ぎると、C1にデータの転記

(6)　(1)から(2)の処理
(7)　(3)を30分繰り返します
(8)　30分を過ぎると、C2にデータの転記

(9)　(1)から(2)の処理
(10)　(3)を30分繰り返します
(11)　30分を過ぎると、C3にデータの転記

(9)から(11)までを9回繰り返し、C4,C5,C6,C7,C8,C9,C10,C11,C12にデータを格納します。

Application.OnTime TimeValue("9:00:00"), "AAA"
Application.OnTime TimeValue("9:30:00"), "BBB1"
Application.OnTime TimeValue("10:00:00"), "BBB2"
Application.OnTime TimeValue("10:30:00"), "BBB3"
Application.OnTime TimeValue("11:00:00"), "BBB4"
Application.OnTime TimeValue("11:30:00"), "BBB5"
Application.OnTime TimeValue("12:00:00"), "BBB6"
Application.OnTime TimeValue("12:30:00"), "BBB7"
Application.OnTime TimeValue("13:00:00"), "BBB8"
Application.OnTime TimeValue("13:30:00"), "BBB9"
Application.OnTime TimeValue("14:00:00"), "BBB10"
Application.OnTime TimeValue("14:30:00"), "BBB11"
Application.OnTime TimeValue("15:00:00"), "CCC"

Sub AAA()
　　Cells(1, 1).Value = 999999
　　Cells(1, 2).Value = 999999
　　Cells(1, 2).Formula = "=IF(A1<B1,A1,B1)"
End Sub

Sub BBB1()
Cells(1, 3).Value = Cells(1, 2).Value

　　Cells(1, 1).Value = 999999
　　Cells(1, 2).Value = 999999
　　Cells(1, 2).Formula = "=IF(A1<B1,A1,B1)"
End Sub

Sub BBB2()
Cells(2, 3).Value = Cells(1, 2).Value

　　Cells(1, 1).Value = 999999
　　Cells(1, 2).Value = 999999
　　Cells(1, 2).Formula = "=IF(A1<B1,A1,B1)"
End Sub

Sub BBB3()
Cells(3, 3).Value = Cells(1, 2).Value　　　

　　Cells(1, 1).Value = 999999
　　Cells(1, 2).Value = 999999
　　Cells(1, 2).Formula = "=IF(A1<B1,A1,B1)"
End Sub

BBB4からBBB11まではＣ列の行番号を変更

Sub CCC()
Cells(12, 3).Value = Cells(1, 2).Value　　　
End Sub

長文になりましたが、よろしくご教授お願いします。

回答No.3

Wendy02

こんばんは。

循環参照は、ワークシート上の数式の内容の問題で、マクロの問題ではありません。

Sub Test1()
　Dim A As Double
　Dim B As Double
　A = 1: B = 5
Cells(1, 1).Value = A '←リテラル値ではなく、変数を使うほうが良い
Cells(1, 2).Value = B '　　　　　''
Cells(1, 3).FormulaLocal = "=IF(A1<B1,A1,B1)"
　 '　　↑　単に場所を変えることと、なるべく、FormulaLocalプロパティの方がよい
End Sub

B1 に入れる場合は、あくまでも、VBA内で処理することですね。

Sub Test2()
　 Dim A As Double
　 Dim B As Double
　 '数値代入
'---------------------------------
　Range("A1:B1").Value = Array(1,5)
'---------------------------------
　 'マクロ
　 A = Cells(1, 1).Value
　 B = Cells(1, 2).Value
　 If A < B Then Cells(1, 2).Value = A Else: Cells(1, 2).Value = B
End Sub

実際には、このようなマクロを組むことはないでしょうから、あくまでも、実験の範囲です。

お礼

回答ありがとうございます。
最初に１回だけVBAで比較の処理を行い、それ以降はA1セルに手入力をしてB1のセルの値をかえさせたいので、EXCELの数式で処理を行いたかったのです。
質問が不明瞭で申し訳ありません。

回答No.2

merlionXX

B1にB1を参照する式を入れるのですから循環参照になると思います。

No1さんのが正解です。

お礼

回答ありがとうございます。
最初に１回だけVBAで比較の処理を行い、それ以降はA1セルに手入力をしてB1のセルの値をかえさせたいので、EXCELの数式で処理を行いたかったのです。
質問が不明瞭で申し訳ありません。

回答No.1

anotu_kage

Sub a()

If cells(1, 1).Value < cells(1, 2).Value Then
cells(1, 2).Value = cells(1, 1).Value
End If

End Sub
--------------
こんな感じじゃだめなんですかね？

お礼

回答ありがとうございます。
最初に１回だけVBAで比較の処理を行い、それ以降はA1セルに手入力をしてB1のセルの値をかえさせたいので、EXCELの数式で処理を行いたかったのです。
質問が不明瞭で申し訳ありません。