Changed to allow for anoter sheet. Put both in a REGULAR module (not sheet
or ThisWorkbook).
To use the first use f5 while cursor is on the macro or if on the worksheet
alt f8 to see or assign to a button.

Sub findem()
x = Sheets("b").Range("a12:e21").Find(1111).Offset(-1)
MsgBox x
End Sub

To use this like any function, type =fd("yoursheet","yourrnage",number to
find)
=fd("sheet1","a12:e222",1111)

Function fd(s, x, y)
application.volatile
fd = Sheets(s).Range(x).Find(y).Offset(-1)
End Function

--
Don Guillett
SalesAid Software

"Kevin Rodriguez" wrote in
message ...
It's been several years since I last used Subs and Functions. I've
entered
this using the VB Editor. Now what?

"Don Guillett" wrote:

see if these ideas help

Sub findem()
x = Range("a12:e21").Find(1111).Offset(-1)
MsgBox x
End Sub
Function fd(x, y)
fd = Range(x).Find(y).Offset(-1)
End Function

--
Don Guillett
SalesAid Software

"Kevin Rodriguez" wrote in
message ...
I'm trying to find a value within an array, and then offset to a cell
above
the found cell to get a date.

To do this, I'm starting by trying to MATCH a value within an array and
getting an #N/A response.

My formula is:
=MATCH(A2,'Sheet1'!A1:N109,0)

A2, the value I'm trying to find, is on Sheet2. The array is
'Sheet1'!A1:N109. I committed by Ctrl-Shift-ENTER. Once I can get
this
to
work, I'll have no problem with the rest.

OK, I tried this without success.

My function is:
Public Function fd(s, x, y)
Application.Volatile
fd = Worksheets(s).Range(x).Find(y).Offset(-1)
End Function

The cell in my sheet is:
=fd("Master Field Allocation", "A1:N109",A2)

For some reason it looks to be treating the formula as a text value and not
performing a calculation. to find the value.

"Don Guillett" wrote:

Changed to allow for anoter sheet. Put both in a REGULAR module (not sheet
or ThisWorkbook).
To use the first use f5 while cursor is on the macro or if on the worksheet
alt f8 to see or assign to a button.

Sub findem()
x = Sheets("b").Range("a12:e21").Find(1111).Offset(-1)
MsgBox x
End Sub

To use this like any function, type =fd("yoursheet","yourrnage",number to
find)
=fd("sheet1","a12:e222",1111)

Function fd(s, x, y)
application.volatile
fd = Sheets(s).Range(x).Find(y).Offset(-1)
End Function

--
Don Guillett
SalesAid Software

"Kevin Rodriguez" wrote in
message ...
It's been several years since I last used Subs and Functions. I've
entered
this using the VB Editor. Now what?

"Don Guillett" wrote:

see if these ideas help

Sub findem()
x = Range("a12:e21").Find(1111).Offset(-1)
MsgBox x
End Sub
Function fd(x, y)
fd = Range(x).Find(y).Offset(-1)
End Function

--
Don Guillett
SalesAid Software

"Kevin Rodriguez" wrote in
message ...
I'm trying to find a value within an array, and then offset to a cell
above
the found cell to get a date.

To do this, I'm starting by trying to MATCH a value within an array and
getting an #N/A response.

My formula is:
=MATCH(A2,'Sheet1'!A1:N109,0)

A2, the value I'm trying to find, is on Sheet2. The array is
'Sheet1'!A1:N109. I committed by Ctrl-Shift-ENTER. Once I can get
this
to
work, I'll have no problem with the rest.