Need a type of lookup / find formula PLEASE
in [Functions]
|
Prev: Lookup function question
Next: Protected Sheets
From: nelly on 30 Mar 2010 12:25 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 30 Mar 2010 12:54 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 30 Mar 2010 12:58 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 30 Mar 2010 13:00 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 30 Mar 2010 13:03
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 |