Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a type of lookup / find formula PLEASE
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a type of lookup / find formula PLEASE
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" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a type of lookup / find formula PLEASE
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a type of lookup / find formula PLEASE
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a type of lookup / find formula PLEASE
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a type of lookup / find formula PLEASE
Gary''s Student wrote:
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)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need a type of lookup / find formula PLEASE
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. -- Biff Microsoft Excel MVP "nelly" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup formula & an error to find the match | Excel Worksheet Functions | |||
If / Lookup / Filter type formula ,..? | Excel Discussion (Misc queries) | |||
i need help with a lookup and/or array type formula | Excel Worksheet Functions | |||
Lookup type formula | Excel Worksheet Functions | |||
Can't find the right lookup formula for this | Excel Worksheet Functions |