Again VBA question

[PeCoNe]

Hi There,

I am working with W7 64 bit and Excel 2010 NL and encounters the
following problem:

F8 gives: After the first 2 if's the program continues with if Time >

F5 gives:
Fout -2147417848 (80010108) tijdens uitvoering
Methode Range van object_Worksheet is mislukt

Why is that?



Private Sub Worksheet_Calculate()

With Sheets("Monitor")
If Time() > .Range("BEGtime") Then
If .Range("FTItime") > .Range("BEGtime") Then
.Range("CURtime") = .Range("FTItime")
.Range("CURprice") = .Range("FTIprice")
If IsEmpty(.Range("LOWtime")) Then
.Range("LOWprice") = .Range("CURprice")
.Range("LOWtime") = .Range("CURtime")
End If
If IsEmpty(.Range("HGHtime")) Then
.Range("HGHprice") = .Range("CURprice")
.Range("HGHtime") = .Range("CURtime")
End If
If .Range("CURprice") < .Range("LOWprice") Then
.Range("LOWprice") = .Range("CURprice")
.Range("LOWtime") = .Range("CURtime")
End If
If .Range("CURprice") > .Range("HGHprice") Then
.Range("HGHprice") = .Range("CURprice")
.Range("HGHtime") = .Range("CURtime")
End If
End If
End If
End With

End Sub


How and where can i solve that?

 

[PeCoNe]

If i remove next statement problem is gone.

.Range("CURprice") = .Range("FTIprice")

What can be wrong with this statement?

 

[Puppet_Sock]

If i remove next statement problem is gone.
             .Range("CURprice") = .Range("FTIprice")

What can be wrong with this statement?

Inigo Montoya has some advice: You keep using that word.
I do not think it means what you think it means.

What exactly do these named ranges refer to? For example,
are they the same size? Are their contents consistent?
Are they in fact both defined?

Also, since you are using something that is Time() sensitive
(and have not defined what Time() is) maybe when you
click F5 you get a different value than when you go through
line-by-line with F8? Maybe one of those ranges depends
on Time()? So, it could be invalid until the function Time()
goes forward far enough to let some other thing satisfy
some part of the code you have not shown us.
Socks

 

[GS]

If i remove next statement problem is gone.

What can be wrong with this statement?

Try specifying the *Value* property...

If .Range("FTItime") > .Range("BEGtime") Then
.Range("CURtime").Value = .Range("FTItime").Value
.Range("CURprice").Value = .Range("FTIprice").Value
....

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[PeCoNe]

Op 2013-04-17 18:28, Puppet_Sock schreef:

Inigo Montoya has some advice: You keep using that word.
I do not think it means what you think it means.

What exactly do these named ranges refer to? For example,
are they the same size? Are their contents consistent?
Are they in fact both defined?

Also, since you are using something that is Time() sensitive
(and have not defined what Time() is) maybe when you
click F5 you get a different value than when you go through
line-by-line with F8? Maybe one of those ranges depends
on Time()? So, it could be invalid until the function Time()
goes forward far enough to let some other thing satisfy
some part of the code you have not shown us.
Socks

I have 2 excel files X1 and X2
A shortcut was created for both excels S1 and S2
I start by clicking S1, then X1 starts running.
X1 is updated every minute by web query.
Then i start X2 by clicking S2
Excel 2 starts and when X1 is updated it comes to the front Excel 2 is
then not on screen anymore So i want a new start of excel for the second
excel so it does not
connect to excel 1.
At end i have 2 excels running.

I hope it is clear now.
How do i do that?