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
