Hi,

I'm having trouble with my code. It works when I step through it but not
otherwise.
So I'm trouble shooting the references with Debug.Print.

Heres a couple of lines that I find inexplicable. They are right ontop of
each other as I've put them below:

Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address

The results in the immediates window are "starting $A$5" for both
debug.print lines which is as expected.
However when running it it makes $a$6 red in colour. When stepping through
it makes $a$5 red in color.

How could the reference change from one line to another. The code is in a
module if thats pertinent.

I'm totally confused as to how this could be possible

Any guidance would be greatly appreciated

Thanks
Suzanne

Hello,

I cannot repeat this:
Option Explicit
Sub test()
Dim theStartingCell As Range
Set theStartingCell = Range("A5")
Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address
End Sub

Do you have any event routines enabled?

Regards,
Bernd

I think people would have to see the whole procedure to see where there
might be a problem. Paricularly how you set variable cell refernece
theStartingCell and how you determine what to set it to. That is more than
likely where the problem lies.

You say the code is in a module, but what type of module - sheet module,
general/standard module, what?

--
Regards,
Tom Ogilvy


"Grd" wrote in message
...
Hi,

I'm having trouble with my code. It works when I step through it but not
otherwise.
So I'm trouble shooting the references with Debug.Print.

Heres a couple of lines that I find inexplicable. They are right ontop of
each other as I've put them below:

Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address

The results in the immediates window are "starting $A$5" for both
debug.print lines which is as expected.
However when running it it makes $a$6 red in colour. When stepping through
it makes $a$5 red in color.

How could the reference change from one line to another. The code is in a
module if thats pertinent.

I'm totally confused as to how this could be possible

Any guidance would be greatly appreciated

Thanks
Suzanne

On Feb 10, 8:52 pm, Grd wrote:

Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address


Hi,

I can't find anything wrong with your code, but nobody is
perfect. .... Is your theStartingCell the range object (the cell)?

for me the following code works fine:

' Create a yellow interior for cell A5.
Range("A5").Select
With Selection.Interior
'.ColorIndex = 6
' or
.Color = vbYellow
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A1").Select
MsgBox "Cell A5 has a yellow interior."

Compare the "Color Property" and "Interior Property" help and check if
the interior property is available to your object theStartingCell.

I hope it helps.

Hi,

I think I have found out the problem and why I'm so confused.
The startingcell that I refer to is where I'm brining in some data using
MSQuery.
Using activesheet.querytables.add.

Now immediately after bringing in the data I'm trying to figure out where
the end of the data is. I keep getting into trouble here as I can't seem to
locate it. Using things like the usedrange.rows.count and
selection.end(xldown).select etc seen to send me the wrong info.

I did a debug.print on startingcell.value and found the following
ExternalData_1: Getting Data ...

Meaning that it hadn't yet got the data before I'm onto the next line of
code trying to find the end of that data. Thats why it was working when I was
stepping into it as I was giving it enough time to bring in the data. It
wasn't an issue of referening to the wrong cells or sheets.

So my problem is now how do I give MSQuery enough time to execute before I
go looking for the end of the data.

I tried application.wait (for a second) but that just suspends the bringing
in of the data.

Thanks very much for your help on this

Suzanne

"Tom Ogilvy" wrote:

I think people would have to see the whole procedure to see where there
might be a problem. Paricularly how you set variable cell refernece
theStartingCell and how you determine what to set it to. That is more than
likely where the problem lies.

You say the code is in a module, but what type of module - sheet module,
general/standard module, what?

--
Regards,
Tom Ogilvy


"Grd" wrote in message
...
Hi,

I'm having trouble with my code. It works when I step through it but not
otherwise.
So I'm trouble shooting the references with Debug.Print.

Heres a couple of lines that I find inexplicable. They are right ontop of
each other as I've put them below:

Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address

The results in the immediates window are "starting $A$5" for both
debug.print lines which is as expected.
However when running it it makes $a$6 red in colour. When stepping through
it makes $a$5 red in color.

How could the reference change from one line to another. The code is in a
module if thats pertinent.

I'm totally confused as to how this could be possible

Any guidance would be greatly appreciated

Thanks
Suzanne

Hi Bernd,

Thanks for your help.
I figured out the source of my confusion.
See my answer to TOms email

Thanks
Suzanne

"Bernd" wrote:

Hello,

I cannot repeat this:
Option Explicit
Sub test()
Dim theStartingCell As Range
Set theStartingCell = Range("A5")
Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address
End Sub

Do you have any event routines enabled?

Regards,
Bernd

Hi Riso,

Thanks but I think I figured it out.
I explained this in my reply to Tom's email.

Thanks again
S

"riso" wrote:

On Feb 10, 8:52 pm, Grd wrote:

Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address


Hi,

I can't find anything wrong with your code, but nobody is
perfect. .... Is your theStartingCell the range object (the cell)?

for me the following code works fine:

' Create a yellow interior for cell A5.
Range("A5").Select
With Selection.Interior
'.ColorIndex = 6
' or
.Color = vbYellow
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A1").Select
MsgBox "Cell A5 has a yellow interior."

Compare the "Color Property" and "Interior Property" help and check if
the interior property is available to your object theStartingCell.

I hope it helps.

This is usually caused because you have the BackGroundQuery property of the
querytable set to True. You need to set it to false and you code will wait
for the query to complete. If you record a macro it usually looks
something like

With Activesheet.QueryTables.Add( . . . )
' then a bunch of properties

one of the properties should be BackGroundQuery

--
regards,
Tom Ogilvy


"Grd" wrote in message
...
Hi,

I think I have found out the problem and why I'm so confused.
The startingcell that I refer to is where I'm brining in some data using
MSQuery.
Using activesheet.querytables.add.

Now immediately after bringing in the data I'm trying to figure out where
the end of the data is. I keep getting into trouble here as I can't seem
to
locate it. Using things like the usedrange.rows.count and
selection.end(xldown).select etc seen to send me the wrong info.

I did a debug.print on startingcell.value and found the following
ExternalData_1: Getting Data ...

Meaning that it hadn't yet got the data before I'm onto the next line of
code trying to find the end of that data. Thats why it was working when I
was
stepping into it as I was giving it enough time to bring in the data. It
wasn't an issue of referening to the wrong cells or sheets.

So my problem is now how do I give MSQuery enough time to execute before I
go looking for the end of the data.

I tried application.wait (for a second) but that just suspends the
bringing
in of the data.

Thanks very much for your help on this

Suzanne

"Tom Ogilvy" wrote:

I think people would have to see the whole procedure to see where there
might be a problem. Paricularly how you set variable cell refernece
theStartingCell and how you determine what to set it to. That is more
than
likely where the problem lies.

You say the code is in a module, but what type of module - sheet module,
general/standard module, what?

--
Regards,
Tom Ogilvy


"Grd" wrote in message
...
Hi,

I'm having trouble with my code. It works when I step through it but
not
otherwise.
So I'm trouble shooting the references with Debug.Print.

Heres a couple of lines that I find inexplicable. They are right ontop
of
each other as I've put them below:

Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address

The results in the immediates window are "starting $A$5" for both
debug.print lines which is as expected.
However when running it it makes $a$6 red in colour. When stepping
through
it makes $a$5 red in color.

How could the reference change from one line to another. The code is in
a
module if thats pertinent.

I'm totally confused as to how this could be possible

Any guidance would be greatly appreciated

Thanks
Suzanne

Immediately solved the problem

Thanks
S

"Tom Ogilvy" wrote:

This is usually caused because you have the BackGroundQuery property of the
querytable set to True. You need to set it to false and you code will wait
for the query to complete. If you record a macro it usually looks
something like

With Activesheet.QueryTables.Add( . . . )
' then a bunch of properties

one of the properties should be BackGroundQuery

--
regards,
Tom Ogilvy


"Grd" wrote in message
...
Hi,

I think I have found out the problem and why I'm so confused.
The startingcell that I refer to is where I'm brining in some data using
MSQuery.
Using activesheet.querytables.add.

Now immediately after bringing in the data I'm trying to figure out where
the end of the data is. I keep getting into trouble here as I can't seem
to
locate it. Using things like the usedrange.rows.count and
selection.end(xldown).select etc seen to send me the wrong info.

I did a debug.print on startingcell.value and found the following
ExternalData_1: Getting Data ...

Meaning that it hadn't yet got the data before I'm onto the next line of
code trying to find the end of that data. Thats why it was working when I
was
stepping into it as I was giving it enough time to bring in the data. It
wasn't an issue of referening to the wrong cells or sheets.

So my problem is now how do I give MSQuery enough time to execute before I
go looking for the end of the data.

I tried application.wait (for a second) but that just suspends the
bringing
in of the data.

Thanks very much for your help on this

Suzanne

"Tom Ogilvy" wrote:

I think people would have to see the whole procedure to see where there
might be a problem. Paricularly how you set variable cell refernece
theStartingCell and how you determine what to set it to. That is more
than
likely where the problem lies.

You say the code is in a module, but what type of module - sheet module,
general/standard module, what?

--
Regards,
Tom Ogilvy


"Grd" wrote in message
...
Hi,

I'm having trouble with my code. It works when I step through it but
not
otherwise.
So I'm trouble shooting the references with Debug.Print.

Heres a couple of lines that I find inexplicable. They are right ontop
of
each other as I've put them below:

Debug.Print "starting " & theStartingCell.Address
theStartingCell.Interior.Color = vbRed
Debug.Print "starting " & theStartingCell.Address

The results in the immediates window are "starting $A$5" for both
debug.print lines which is as expected.
However when running it it makes $a$6 red in colour. When stepping
through
it makes $a$5 red in color.

How could the reference change from one line to another. The code is in
a
module if thats pertinent.

I'm totally confused as to how this could be possible

Any guidance would be greatly appreciated

Thanks
Suzanne