search a range of cells for text and return that text

Hi everyone

I need to use a function or a macro that will search a range of cells, one only of which will contain a number, and when if finds it, it copies that number to a certain cell. The problem is the number could be anything so I need it to search for any cell that has a value really and return that value. I hope the example below helps.PS there will always be a number in some of the cells in the range and there will only ever be one number.


A B C
1 29 300
2 15 35
3 11 14 2
4 18 200
5 125 420

In the above example I want excel to search in range C1-C5 and if it finds a value ( in this case 2 ) then return the value in say cell D3. I cant tell it to look for 2 in the range as the next time the number could be anything, all that remain constant is that it will be a single value in an otherwise blank range

Thanks for the help

Not sure I understand, but does this help??

Please Login or Register  to view this content.

Thanks john, I see my example didn't really show up well. See if this example makes sense.

Column A
blank cell
blank cell
blank cell
4
blank cell
blank cell

I have written blank cell here so it shows up but in the spreadsheet it would just be a blank cell. Now I need the macro or formula to look down the range from A1:A6 and see which one has a number in it, and return that value (in this case 4) in another cell say D3. Does this make sense?

Your macro dosen't work as I dont know what the number is going to be, all I need it to do is to copy the number from one cell to another but I need it to look through the range of cells to see where the number is as it could be anywhere in the range and could be any number.
In plain speak I want it to look through a list of cells and whichever cell has a value in it then show that value on another place on the spreadsheet

Maybe:

Please Login or Register  to view this content.

Thanks John for all your help

I got it sorted in the meantime with a google search and came up with this =LOOKUP(REPT("z",255),A1:A9)

You're welcome. Glad you got a solution.