Need a type of lookup / find formula PLEASE

[nelly]

HI does anyone know of a workaround as a formula for this VBA code.

Range("A1:U200").Select
test = Selection.Find(What:="40237", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Address

I thought it easier to explain this way.

40237 is a date and can be anywhere in the above range. I need to get the
value from the cell below.

Thanks in advance

Nelly

 

[Per Jessen]

Hi Nelly

See if this can help you:

Sub aaa()
Dim SearchRng As Range
Dim SearchValue As Long
Dim f

SearchValue = 40237
Set SearchRng = Range("A1:U200")
Set f = SearchRng.Find(What:=SearchValue, After:=Range("A1"),
LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If f Is Nothing Then
msg = MsgBox("No match")
Else
Result = f.Offset(1, 0).Value
End If
End Sub

Regards,
Per

 

[Gary''s Student]

Without VBA. Say B2 contains your value (40237)
In V2 enter the following array formula:

=ADDRESS(MAX((A1:U200=40237)*ROW(A1:U200)),MAX((A1:U200=40237)*COLUMN(A1:U200)),4)

This must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

The formula will display "B2". To get the contents of the cell BELOW B2:
=OFFSET(INDIRECT(V3),1,0)

You may be able to combine these into a single formula

 

[Dave Peterson]

Dates are mysterious things. Sometimes you can just look for them--like:

Option Explicit
Sub testme()

Dim Wks As Worksheet
Dim FoundCell As Range
Dim myRng As Range
Dim WhatToFind As Date

Set Wks = ActiveSheet
Set myRng = Wks.Range("A1:U200")

WhatToFind = DateSerial(2010, 2, 28)

With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox WhatToFind & " wasn't found"
Else
With FoundCell.Offset(1, 0)
MsgBox .Value & vbLf & .Address
End With
End If

End Sub

Sometimes, you need to use:
Set FoundCell = .Cells.Find(what:=clng(WhatToFind), _

or maybe
Set FoundCell = .Cells.Find(what:=format(WhatToFind, "mm/dd/yyyy"), _

(match the numberformat that you use.)

 

[Dave Peterson]

Oops. I see you wanted a worksheet formula for your code.

Ignore my response.

 

[Glenn]

Without VBA. Say B2 contains your value (40237)
In V2 enter the following array formula:

=ADDRESS(MAX((A1:U200=40237)*ROW(A1:U200)),MAX((A1:U200=40237)*COLUMN(A1:U200)),4)

This must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

The formula will display "B2". To get the contents of the cell BELOW B2:
=OFFSET(INDIRECT(V3),1,0)

You may be able to combine these into a single formula

Nice. Just add one to the row and wrap with INDIRECT (still an array formula):

=INDIRECT(ADDRESS(MAX((A1:U200=40237)*ROW(A1:U200))+1,
MAX((A1:U200=40237)*COLUMN(A1:U200)),4))

 

[T. Valko]

As long as there is only a single instance of the date...

Array entered** :

=INDEX(A1:U200,MAX(IF(A1:U200=40237,ROW(A1:U200)))-1,MAX(IF(A1:U200=40237,COLUMN(A1:U200))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.