VBA Range navigation

[SteveSproul]

Hello,

I have a VBA macro that selects a range (A part if a column of data) and then looks into the range for a specific value. the problem is How do I select the 4th value down in the column

I am only familiar with the For each loop used to traverse a range, how do I go directly to a cell within a range (based on relative reference)?

Thanks,

Steve

[barryleajo]

Try

Please Login or Register  to view this content.

[Colin Legg]

Welcome to the forum, Steve.

The range class has an Offset property which has this syntax:
Offset( [RowOffset] , [ColumnOffset] )


so, once you've found the anchor cell, you can find the cell 4 rows down like this:

Please Login or Register  to view this content.

The 0 is optional - I included it for clarity.


One thing to note is that Offset returns a range with the same dimensions as the anchor range:

Please Login or Register  to view this content.

Another way to do it is with the Cells property which has this (simplified) syntax:
Cells( [RowIndex] , [ColumnIndex] )

Please Login or Register  to view this content.

Note that the cells property will always only return a single cell (this is one thing that differentiates it from the Offset property):

Please Login or Register  to view this content.

[SteveSproul]

Thanks for the explanation. Very thorough - I need to review it to see how it will work for me in my code. The offset seems to be most useful when a single cell is identified (Which is exactly what I have.)

Steve

[royUK]

Are you sure that you need a Loop, also it's generally unnecesssary to select a range in VBA.

Maybe a better explanation of what you are doing & posting your code will get you better code.