Looping in a user defined Range of Cells

[dhatul]

In my program, when I search currentregion for a value and then depending on a condition change the value of the cell it works, as in the following code -

Please Login or Register  to view this content.

However, when I want to limit the search to a smaller Range defined by me, say MyRange, the program gives error in the second line - "Object required"

Please Login or Register  to view this content.

Can someone guide me on how to write this code for a user defined range of cells.

[WinteE]

Please Login or Register  to view this content.

[dhatul]

Please Login or Register  to view this content.

Now the error message is - Method 'Range' of Object '_Global' failed

[WinteE]

Sorry, forgot the quotes around the range name.

Please Login or Register  to view this content.

[dhatul]

Sorry, forgot the quotes around the range name.

Please Login or Register  to view this content.

The error remains the same.

[WinteE]

Is there a named range 'myrange' ?

Insert, Name, Define

[Mordred]

Perhaps if you post your whole sub so that people here can see how you came up with MyRange. How did you declare it? How was it Set?

[snb]

Please Login or Register  to view this content.

you can reduce a 'currentregion' very simply, using 'resize':

Please Login or Register  to view this content.

[dhatul]

The entire code is given below. The error message is as indicated in my earlier post.

Please Login or Register  to view this content.

[Mordred]

I don't know if this matters but it may,

Please Login or Register  to view this content.

may need to be

Please Login or Register  to view this content.

and then

Please Login or Register  to view this content.

[davegugg]

A few things to note:
This line:

Please Login or Register  to view this content.

=

Please Login or Register  to view this content.

Since you want them all as integers, you should use:

Please Login or Register  to view this content.

VBA is stupid like that.

I would dim myRange as a Range in this case. There may be times when it would be beneficial to dim it as a variant (I'm not sure, maybe snb could say?), but in this case I believe a Range would be better. When setting a Range variable, you have to use Set, as Mordred already said.

Since myRange is a variable and not a named range, you don't need quotes around it in this line:

Please Login or Register  to view this content.

should be:

Please Login or Register  to view this content.

I also don't see where you declare AsOfFlag.

[Mordred]

A few things to note:

Please Login or Register  to view this content.

Is it okay to code this as

Please Login or Register  to view this content.

or do you have to code it your way because it is declared a Range instead of a Variant?

[dhatul]

Thanks Mordred. It worked. I had to make one more change (see the second line of the code below).

Please Login or Register  to view this content.

[davegugg]

Good catch.

Please Login or Register  to view this content.

will do the job since myRange is a variable in our code.

Please Login or Register  to view this content.

would be needed if we are refering to a named range on the worksheet.

[snb]

But why would you....

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

But nothing fails if we let the VBA-compiler do it's job:

Please Login or Register  to view this content.

[dhatul]

Please Login or Register  to view this content.

But nothing fails if we let the VBA-compiler do it's job:

Please Login or Register  to view this content.

Heartfelt Thanks snb. Thanks for taking the trouble to explain the underlying issue in such a simple and concise manner.