Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locate values in range
Not sure if this can be done with cell formulas...
I want to create a function so that given a range that is 20 x 30, it would return the row and column coordinated for each cell containing a particular value (the letter "N"). Any idea if this doable with functions? or would this require VBA? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locate values in range
Try this small UDF:
Function nFinder(r As Range) As String nFinder = "" For Each rr In r If rr.Value = "N" Then If nFinder = "" Then nFinder = rr.Address Else nFinder = nFinder & ", " & rr.Address End If End If Next End Function UDFs are very easy to install and use: 1. ALT-F11 brings up the VBE window 2. ALT-I ALT-M opens a fresh module 3. paste the stuff in and close the VBE window If you save the workbook, the UDF will be saved with it. To use the UDF from the normal Excel window, just enter it like a normal Excel Function. For example: =nFinder(A1:Z100) To remove the UDF: 1. bring up the VBE window as above 2. clear the code out 3. close the VBE window To learn more about UDFs, see: http://www.cpearson.com/excel/Writin...ionsInVBA.aspx -- Gary''s Student - gsnu200817 "Matt" wrote: Not sure if this can be done with cell formulas... I want to create a function so that given a range that is 20 x 30, it would return the row and column coordinated for each cell containing a particular value (the letter "N"). Any idea if this doable with functions? or would this require VBA? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
locate a cell value within a range and return its reference | Excel Discussion (Misc queries) | |||
Need a formula to locate the blank cells within the range | Excel Discussion (Misc queries) | |||
locate and SUM values from LOOKUP rows? | Excel Worksheet Functions | |||
Locate month n year from range of date | New Users to Excel | |||
How can I locate the second to last value in a range? | Excel Worksheet Functions |