MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Simple Worksheet find question


Posted by Skip on June 04, 2001 11:45 AM

What is the easiest way to search across a range of
Cells in a row and return the 1st cell in the range
that contains data. For example:
Col a b c d e f g h i j
Row 3 2 4 5 3 2

Need a worksheet function/equation to look across
row 3, cells A thru j and return cell (C3)
Similar function to return last cell containing
datain the row i.e return (G3)


Posted by Tuc on June 04, 2001 12:24 PM

This function will do what you want:
Function GiveMeACorner(rng1 As Range, intCorner As Integer) As String
'***********************************************************************
' Author: Tuc Goodwin
' Date: January, 2001
' Purpose: This function returns the corner address of the range that is
' passed.
' The Corners are numbered as follows:
' Upper Left Hand Corner = 1
' Upper Right Hand Corner = 2
' Lower Left Hand Corner = 3
' Lower Right Hand Corner = 4
'
' 1-----2
' | |
' 3-----4
'***********************************************************************

Dim ws1 As Worksheet

' Initialize variables
GiveMeACorner = ""

' Get the parent worksheet
Set ws1 = rng1.Parent

Select Case intCorner
Case 1 ' Upper Left Hand Corner
GiveMeACorner = ws1.Cells(rng1.Row, rng1.Column).Address(False, False)
Case 2 ' Upper Right Hand Corner
GiveMeACorner = _
ws1.Cells(rng1.Row, _
rng1.Column + rng1.Columns.Count - 1).Address(False, False)
Case 3 ' Lower Left Hand Corner
GiveMeACorner = _
ws1.Cells(rng1.Row + rng1.Rows.Count - 1, rng1.Column) _
.Address(False, False)
Case 4 ' Lower Right Hand Corner
GiveMeACorner = _
ws1.Cells(rng1.Row + rng1.Rows.Count - 1, rng1.Column + _
rng1.Columns.Count - 1).Address(False, False)
End Select

' Reclaim Memory
Set ws1 = Nothing

End Function

Posted by Mark W. on June 04, 2001 2:03 PM

1st cell...

{=CELL("address",INDEX(3:3,,MIN(IF(ISBLANK($A3:$J3),"",COLUMN($A3:$J3)))))}

last cell...

{=CELL("address",INDEX(3:3,,MAX(IF(ISBLANK($A3:$J3),"",COLUMN($A3:$J3)))))}

Note: these are array formulas which must be entered
using the Control+Shift+Enter key combination. The
braces, {}, are not entered by you. They are supplied
by Excel as an acknowledgement that you've entered
an array formula.


Posted by Skip on June 04, 2001 4:42 PM


Posted by Skip on June 04, 2001 5:41 PM


Mark,
Thanks - the array formula you provided did exactly what I ask for, however I guess I did not think through exactly what else I needed.

I need to not only find the 1st cell in a row that contains data, but the data must be a number greater than zero. I don't want to include any cells that contain zero, text or blanks. I know this would prorably be easier to implement with a macro but I would prefer a formula if possible.
Thanks

Posted by Mark W. on June 04, 2001 6:15 PM

Okay, use...


{=CELL("address",INDEX(3:3,,MIN(IF(ISNUMBER($A3:$J3),IF($A3:$J3>0,COLUMN($A3:$J3))))))}
{=CELL("address",INDEX(3:3,,MAX(IF(ISNUMBER($A3:$J3),IF($A3:$J3>0,COLUMN($A3:$J3))))))}

...for 1st and last cell respectively.

Posted by Mark W. on June 04, 2001 6:17 PM

Oops! Pasted the formulas in the wrong place...


{=CELL("address",INDEX(3:3,,MIN(IF(ISNUMBER($A3:$J3),IF($A3:$J3>0,COLUMN($A3:$J3))))))}
{=CELL("address",INDEX(3:3,,MAX(IF(ISNUMBER($A3:$J3),IF($A3:$J3>0,COLUMN($A3:$J3))))))}

...for 1st and last cell respectively.

Posted by Skip on June 05, 2001 5:07 AM

Re: Okay, use...

Mark,
As expected, this worked as requested. Thanks for your help.
Skip