From: nelly on
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
From: Per Jessen on
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

"nelly" <nelly(a)discussions.microsoft.com> skrev i meddelelsen
news:C29B06BF-9A91-4653-AC2E-3112021DD2F7(a)microsoft.com...
> 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

From: Gary''s Student on
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
--
Gary''s Student - gsnu201001


"nelly" wrote:

> 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
From: Dave Peterson on
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.)




nelly wrote:
>
> 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

--

Dave Peterson
From: Dave Peterson on
Oops. I see you wanted a worksheet formula for your code.

Ignore my response.

Dave Peterson wrote:
>
> 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.)
>
>
> nelly wrote:
> >
> > 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
>
> --
>
> Dave Peterson

--

Dave Peterson
 |  Next  |  Last
Pages: 1 2
Prev: Lookup function question
Next: Protected Sheets