Select cell within range (Excel 2007)

Topic

New Reply

In visual basic, if I have a range of cells named “Something”, then how do I select the 5th cell or any cell within that range?
Example: For each item in range(“Something”)… selects each cell one by one, whereas I want to go directly to the 5th cell or someother cell.

Reply | Quote

Replies

range(“Something”).cells(5).select

Steve

Reply | Quote

Is there a way to do this outside of VB?

I would just like a cell to display a specific value from a range.

Reply | Quote

Is there a way to do this outside of VB?

I would just like a cell to display a specific value from a range.

Try a formula such as

=INDEX(Something,5)

where Something is a range reference or defined name.

If it is a rectangular range, you can use

=INDEX(Something,4,3)

to refer to the cell in the 4th row, 3rd column of the range Something.

Reply | Quote

Hans, you are beautiful! I couldn’t figure out what to search on and wasted 45 minutes trying to figure it out -thank you!

Reply | Quote

follow-on question: must named ranges always be referred to globally throughout the workbook? Or can there be the same range named on each tab, referring to the data in that tab?

Reply | Quote

follow-on question: must named ranges always be referred to globally throughout the workbook? Or can there be the same range named on each tab, referring to the data in that tab?

You can make a name local to a worksheet by prefixing the name with the name of the workbook worksheet and an exclamation mark.
E.g. you could have names Sheet1!MyName and Sheet2!MyName
Or use Jan Karel Pieterse’s excellent free Name Manager add-in – this lets you change defined names from global to local and back with ease.

Reply | Quote

You can make a name local to a worksheet by prefixing the name with the name of the workbook and an exclamation mark.

Hans
Did you not mean worksheet?

Reply | Quote

Hans
Did you not mean worksheet?

Yes, you are correct (as is clear from the examples I posted). I will correct my reply.

Thanks!

Reply | Quote