Forum home » Delegate support and help forum » Microsoft Access VBA Training and help » Referring to cells in a range
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Referring to cells in a range
Resolved · Low Priority · Version 2007
Reply Sun 22 Jan 2012, 19:34Delegate Roger said...
Roger has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Referring to cells in a range
I'm trying to avoid using VBA for something...
I want to find the value in the first cell in a named range. I think I can do this with Range.Cells in VBA, but is there a way to do it on the spreadsheet alone?
If it's called TestRange, I guess I am looking for something like "=TestRange(Cell1)", but I can find no evidence that such a thing exists.
Any suggestions gratefully received.
Roger
For upcoming training course dates see: Pricing & availability
RE: referring to cells in a range
Morning Roger
Yes, you can find a value in a range without using VBA by using the INDEX function.
In any cell of your workbook containing a range called TestRange try typing:
=INDEX(TestRange,1,1)
The Index function returns the value at the position row 1, col 1 within the range TestRange.
Hope that helps.
Other example of Index
Index is sometimes used to find a value if a position in a range in known (throught the Match function)
A B
John 250
Sue 400
Rich 120
Val 300
Example
To find the person with the highest value without sorting type:
=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0),1)
The Match function finds the postion 2 for Max value in B1:B4 and the Index function returns the value in that row of the range A1:A4 which is Sue!
Regards
Doug
Doug Dunn
Best STL
Reply Mon 23 Jan 2012, 16:58Delegate Roger said...
RE: referring to cells in a range
Magic.
Thanks, Doug.
Roger
|