Searching a range of cells for a cell with a value greater than 0 then placing the adjacent cell to another cell

[woodsm]

Sorry for the long title.

I have two columns, the first column is of numbers, the second column is text. I want to search the first column for a cell with a value that is greater than 0. Then place the adjacent cell on a separate sheet, which is the an invoice.

I know how to search the first column for a specific number value. I do not know how to search the first column for a single cell that has a value "greater than 0".

The first column has 10 cells, only one of them will have a value, but the value could be "1", "2", "3" etc. All the other cells will have an "0" in them.

The second row will have a name that needs to be placed into a different sheet. The name will be adjacent to the cell with a value greater than "0".

Any help will be appreciated. Thanks.

 

[jolivanes]

Change Sheet and Cell references as required

Sub Maybe()
    Dim MaxVal As Long
    MaxVal = Application.WorksheetFunction.Max(Sheets("Sheet1").Range("A1:A10"))
    Sheets("Sheet2").Range("B3").Value = Range("A1:A10").Find(MaxVal).Offset(, 1).Value
End Sub

 

[Jonmo1]

Try

=LOOKUP(2,1/(A1:A100>0),B1:B100)

Assuming the number in Column A, and the text to return in column B

 

[Joe4]

Cross-posted: Searching a range of cells for a cell with a value greater than 0 then placing...

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links to the other thread (see rule #13 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[woodsm]

Thanks Jonmo1,

I am looking to take the answer from column b and place it in a different column. I am getting a #DIV/0! error also.

A B

0 Sally
0 Mike
0 Tom
2 Fred
0 Steve

I want to retrieve Fred's name and place it in column "f" or on a different page by searching the first column. The number "2" may be a "1", "3", "4" etc.

 

[woodsm]

Thanks Jolivanes,

I am a newbie and do not understand anything you typed. The formula I used was =vlookup(1,A2:A10,2,0). But this would only return values of 1, again the lookup value of "1" can be anything greater than 0. I was trying to put ">0" where the 1 is in the formula. I do not know if this is even possible.

 

[jolivanes]

Please read Post #4 and provide the links as requested on both sites.
After that, google on how to insert code into excel and how to use a macro in Excel.
I am sure that you'll get a site that is way better at explaining this then I can.
The code I gave you does what you want but if you want/need a formula, you'll have to get it from someone else.

BTW, If you change jonmo's formula to "=LOOKUP(2,1/(Sheet1!A1:A10>0),Sheet1!B1:B10)" and put it anywhere in another sheet, also in Column F, it'll give you the desired result.
Of course, the ranges A1:A10 and B!:B10 need to be just that and that data has to be in Sheet1. If not, change accordingly.

 

[Jonmo1]

I am looking to take the answer from column b and place it in a different column.

That's what the formula I posted does

Excel 2013
	A	B	C	D
1	0	Sally		Fred
2	0	Mike
3	0	Tom
4	2	Fred
5	0	Steve
Sheet1

Cell Formulas
Range	Formula
D1	=LOOKUP(2,1/(A1:A100>0),B1:B100)

 

[jolivanes]

@Jonmo1
Good evening.
I think he/she wants it in another sheet according to his/her first post.
No biggie.
Have a good remainder of your evening.
John