Method 'Range' of Object_ Worksheet' failed

[YellowLabPro]

I have a two part question on this post please.
1) Solve- explain why the .Range is failing
2) Are any of the debugging options available to me here on such an error? I have looked through all the items in the Watches Window and nothing there seems to present a clue where to look.
I placed this line in the Immediate Window and it returned a false, not sure if it is asking if the interior is red, but my thought was to tell it to change the range of cells to red,
[vba]?activesheet.Range("A1:K20").Interior.Color = vbred
False[/vba]
The error message does offer a subtantial clue, Method 'Range' of Object'_ Worksheet' failed.

However, for the code to see the Range as a Method, (If I am understanding the error message correctly), is throwing me off. It seems to be a valid statement for the Range to be an Object of the Worksheet, wsSource

[vba]
Sub SheetUtilities()
Dim wbSource As Workbook, wsSource As Worksheet
Dim lrwSource As Long
Dim wbn As String

wbn = "TGSProductsAttribPrep.xls"

Set wbSource = Workbooks(wbn)

For Each wsSource In Workbooks(wbn).Worksheets
With wsSource

With .Range("A1:K" & lrwSource)
.Sort Key1:=.Range("B2"), Order1:=xlAscending, Key2:=.Range("C2") _
, Order2:=xlAscending, Key3:=.Range("A2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End With
.Range("A2:J" & lrwSource).ClearFormats
End With
Next wsSource
End Sub

[/vba]

thanks....

[Bob Phillips]

1) I would guess that it is because lrwSource is 0, and there is no K0 cell

2) I start by extracting the ob ject in question, and get some info in the immediate window. Here I would do a

?.Range("A1:K" & lrwSource).Address

and then work on from there. As lrwSource is the only variable there, inspecting that is the next obvious thing to do.

I don't how that colour b it fits in with the rest, but you are checking if all those cells were red, only true if ALL are.

To set them, you need a loop, which you can do in the immediate window

For i = 1 to 20:for j=1 to 11:activesheet.cells(i,j).Interior.Color=vbred:next j:next i

[rory]

Why do you need a loop - you can just do:
[VBA]activesheet.Range("A1:K20").Interior.Color = vbred [/VBA]

[YellowLabPro]

Good Morning/Afternoon Bob,
So the problem was the variable.
Using your approach- placing the ?.Range("A1:K" & lrwSource).Address into the Imm.Window gave me an error right off.
(I only provide the details here to show a progression of thought)

Invalid Watch Expression.... a little head scratching here...

So now I have something else to look for.
But at this point that was not the reason for the Method Range Object error, but led me to what was...

So I looked back the code and sure enough my statement to define the variable was missing- damn Gremlins again- need a new software program to protect me from myself....
[VBA]Application.Knucklehead(Protect.fromonesself).Run = AllTheTime[/VBA]

Ok so enough messing around-

Using the IMM. Window to test that line I needed the program to read in the variable, and this variable did not exist.... at all....
This led me to create the variable.
Still same error though when I ran the line ?.Range("A1:K" & lrwSource).Address in the Imm. Window.
It became evident quickly that the code neede to read in the variable for the Imm. Window to process it..... problem resolved.

So to follow up on your debugging point- I need to get around the idea where to start and what tools to use. I realize it is a vast question, no one right way/answer- but it would help if there are some guidelines to follow....
So like you wrote- start w/ variables....
A statement so simple which has such a huge impact.

I am going to compile a list of such troubleshooting steps, and add it to the error code list in the sticky here on the board. It should prove very handy...

Bob,
If you, or anyone else have compiled such a list and would not mind sharing, I would be indebted to you. If not, I will have started one today.

Last point,
I only through the color in so I could actually see something, the visual helps me... and color is the easiest thing to use. The color line you provided would not directly apply here, but that is my fault for not being more clear. However it does offer some other significant benefit- for the sheer fact of seeing how to use the IM Window to test a loop, and using color is a great way to see something, either in a range, a valid range, if it runs at all. It was also cool to see how you used the : to separate the two loops and keep it on the same line.


Thanks Bob,

Doug

[rory]

Doug,
If you have the Locals window open, you can easily see the values of all your variables and examine all the properties of any object variables too.

[Bob Phillips]

Bob,
If you, or anyone else have compiled such a list and would not mind sharing, I would be indebted to you. If not, I will have started one today.

Sorry mate. Since I went out on my own, I am far more organised than I was when others paid for my lack of organisation, but I am nowhere near that organised.

[YellowLabPro]

I am far more organised than I was when others paid for my lack of organisation

Rory,
You are totally correct. But.... when I did not know what to look for, the Locals window was not on my radar.... One reason was the code had run a few days prior w/out a hitch. This was not a newly created a procedure where I would have thought that something like a declared variable would have been missing.
A good reason for me to have some sound troubleshooting/debugging skills, which I sorely lack.
"To the one w/ the trained eye it is quite clear, to the one requiring glasses it appears quite fuzzy"

[rory]

Quite often the simplest thing in Debug mode is to hover your cursor over the variables in the code and wait for the tooltip to pop up showing the current value. Or bung a load of Debug.Print (or Debug.Assert) statements in there.

[YellowLabPro]

Rory,
Per your comment about bung... not familiar w/ that term...
Would you mind providing some examples of the Debug.Print stmts. pertaining to the issue at hand? I have seen this used, but don't follow how to incorporate it to debug.

thanks

[rory]

Bung = chuck = throw

You could use:
[VBA] For Each wsSource In Workbooks(wbn).Worksheets
With wsSource
Debug.Print lrwSource
With .Range("A1:K" & lrwSource)
[/VBA]

[YellowLabPro]

Thanks rory....