Trouble selecting a range using VBA

[demarc]

Hi all,

Why would it be that the first statement works fine however the second statement that is directly below it in the sub does not? It will select the cell but not the range.

Note: DWI is dimensioned as a worksheet and I have used it multiple times and works fine. Days_In always has a numerical value assigned to it.

I get a "method range of object _worksheet failed" message.

Please Login or Register  to view this content.

any suggestions?

Cheers,
Marcus

[6StringJazzer]

There is not an error internal to the statement, but it is impossible to diagnose without seeing all the code. It would be even better to attach the entire file and explain how to run the macro. I would need to know with certainty what the values of a, b, and Days_In are at the point this code is executed (not just what you think they should be).

For example, you will get an error if any of these conditions are true:

a < 6
b < 1
a + Days_In < -4

[demarc]

HI Jeff,

This is the rest of the code. Essentially what it is trying to do is when I double click on a cell in a row of data, some values are stored and using these values a search is conducted on another worksheet. Initially I was just highlighting a cell that contained a date and it worked fine. I have since decided instead of just selecting one cell I would like to highlight a range when I am taken to the new worksheet. The a and b are row and column references and a will never be less than 20 and b can never be less than 1. Days_In refers to the length of a trade and is never less than 1.

Thanks for your time.

Please Login or Register  to view this content.

Cheers,
Marcus

[6StringJazzer]

Which worksheet module is this code in?

There is nothing in the code that guarantees that the value of a and b are never less than 20. I am guessing that is an assumption, but somehow the assumption is being violated. If I had this file I would run it and diagnose it for you, but I can't set up a file and sample data based on this code. So I suggest you try my revision below, and let me know what happens. It detects errors that could occur and will report them to you to isolate where the problem is occurring.

The original code is depending on Activate, Select, and ActiveCell to manipulate the sheet, always a dicey proposition.

There are undeclared variables. Best practice is to use Option Explicit and require all variables to be declared.

Try this version of the code and let me know what you get:

Please Login or Register  to view this content.

[Special-K]

Can you say

Range( value1, value2).select

I know you can say Cells( value1, value2).select
I thought the range attribute had to be a cell address/range?

[6StringJazzer]

Can you say

Range( value1, value2).select

No you can't, but I see nowhere in the code where it attempts to do this.

[Norie]

What do you want to do with the selected range?

Is the code in the sheet 'DataWindc'?

If it isn't Cells will refer to the sheet the code is actually in and your Select will fail.

[Special-K]

No you can't, but I see nowhere in the code where it attempts to do this.

Isn't it the second line in the OP's original post?

Please Login or Register  to view this content.

(I don''t know a great deal about VBA)

[Norie]

Special_K

In that line of code Cells will refer to whichever sheet the code is in, if it's not the same sheet DWI refers to the code will fail.

[6StringJazzer]

Isn't it the second line in the OP's original post?

Please Login or Register  to view this content.

(I don''t know a great deal about VBA)

No. Cells(a - 5, b) is not a value, it's a Range. A Range refers to either a string giving a range address, or a Range.

In that line of code Cells will refer to whichever sheet the code is in, if it's not the same sheet DWI refers to the code will fail.

Norie, this is almost certainly the problem, I did not catch that in the first go-round.

[demarc]

Hi Jeff,

If I could write code as quick as you what has taken me months would probably be done in days!

Its getting late in my part of the world so will have to try your code tomorrow.

Just quickly however from reading all the replies above I think the problem might stem from that fact that the code is attached to another worksheet, not Sheets("DataWindc") hence for some reason it is not referencing the correct sheet when trying to select the range. Does this sound like it could be the problem?

from Norie:

In that line of code Cells will refer to whichever sheet the code is in, if it's not the same sheet DWI refers to the code will fail.

With regards to a, b and Days_In:

the Sheet DataWindic is populated with stock information and a whole bunch of indicators that are calculated based on the stock information. Generally I have about 50 stocks with Date, open, high, low, close and volume information for the last 1000 days. There are another 8 columns of indicator information for each stock. I then run some code that makes trades within this historical information based on certain criteria and then prints them out onto a new worksheet with info like open date, close date, PnL etc. If I double click on a row of data (a trade) in this worksheet the code above which is attached to this worksheet will take the ticker, open date and days in the trade (close date-open date) and search for the ticker and then the date in the DataWindc worksheet (the raw data so to speak). The a will never be less than 25 because when I search for trades in the raw data I use a loop (i.e. for I = 25 to LastRow) hence if I double click on the first trade in the trade info worksheet and it was opened on the first day of testing its row will be 25 and can never be less than 25. Obviously the column number will never be less than 1 and the days in will always be >=1 as the close date will always be after the open date of the trade.

[Norie]

Do you actually want to go the range you are selecting?

If you are I would suggest using Application.Goto.

Please Login or Register  to view this content.

[demarc]

Norie,

Works perfectly!

One question though, why does this statement work? Should it not have the same issues as the range statement?

Please Login or Register  to view this content.

Thanks everyone who helped with the problem.

Cheers,
Marcus

[6StringJazzer]

If I may step in to answer--Range and Cells are both objects that require a worksheet to be its parent. VBA figures out who the parent is in these ways:

If you just say Range or just say Cells, then VBA looks at where this code is located.
- If it's in a Worksheet module, then it uses that worksheet as the parent.
- If it's in a Module, then it uses the active worksheet as the parent.

If you explicitly qualify it with the parent, like the example you just showed, then it uses that as the parent, no matter where it's located.

The error you had arose because you qualified Range with its parent DWI, but inside Range you used Cells with no qualification. The Cells took on a parent according to the location of the code, and the parent is not DWI. When you use Range and have references to Cells inside, the Cells must be in the same worksheet as the Range. But in this case, they are not. Range has parent DWI but the Cells have some other parent, and you can't reasonably have a Range in one worksheet that includes Cells in another. So VBA gave you a runtime error.

The code you just showed works because Cells is qualified, and there are no conflicts.