Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperately Seeking FirstCell Find !
I am trying to search in the fist column of a range using the following
code. The problem is that the first item is never searched as it allways starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to 16 and I want to find 1, it starts at cell 2 and therfore never find the 1 Any Ideas ??? Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As Presets) As Variant On Error Resume Next Dim mr As Range Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns) lookupRange = mr.Offset(0, col) End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperately Seeking FirstCell Find !
why not just use MATCH to find the 1 and incorporate into an INDEX formula
-- Don Guillett SalesAid Software "Goofy" wrote in message ... I am trying to search in the fist column of a range using the following code. The problem is that the first item is never searched as it allways starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to 16 and I want to find 1, it starts at cell 2 and therfore never find the 1 Any Ideas ??? Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As Presets) As Variant On Error Resume Next Dim mr As Range Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns) lookupRange = mr.Offset(0, col) End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperately Seeking FirstCell Find !
What is MATCH a property or method of ?
"Don Guillett" wrote in message ... why not just use MATCH to find the 1 and incorporate into an INDEX formula -- Don Guillett SalesAid Software "Goofy" wrote in message ... I am trying to search in the fist column of a range using the following code. The problem is that the first item is never searched as it allways starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to 16 and I want to find 1, it starts at cell 2 and therfore never find the 1 Any Ideas ??? Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As Presets) As Variant On Error Resume Next Dim mr As Range Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns) lookupRange = mr.Offset(0, col) End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperately Seeking FirstCell Find !
"LookIn:=xlFormulas" seems to work better
Rgds J On Oct 23, 1:13 pm, "Don Guillett" wrote: why not just use MATCH to find the 1 and incorporate into an INDEX formula -- Don Guillett SalesAid Software "Goofy" wrote in .. . I am trying to search in the fist column of a range using the following code. The problem is that the first item is never searched as it allways starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to 16 and I want to find 1, it starts at cell 2 and therfore never find the 1 Any Ideas ??? Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As Presets) As Variant On Error Resume Next Dim mr As Range Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns) lookupRange = mr.Offset(0, col) End Function- Hide quoted text -- Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperately Seeking FirstCell Find !
But what I have is not a formula, its a simple value. What I have ended up
doing is specifying my range as the first column only, this way it wrapos around to the first cell. Thanks for your help anyway "WhytheQ" wrote in message oups.com... "LookIn:=xlFormulas" seems to work better Rgds J On Oct 23, 1:13 pm, "Don Guillett" wrote: why not just use MATCH to find the 1 and incorporate into an INDEX formula -- Don Guillett SalesAid Software "Goofy" wrote in .. . I am trying to search in the fist column of a range using the following code. The problem is that the first item is never searched as it allways starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to 16 and I want to find 1, it starts at cell 2 and therfore never find the 1 Any Ideas ??? Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As Presets) As Variant On Error Resume Next Dim mr As Range Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns) lookupRange = mr.Offset(0, col) End Function- Hide quoted text -- Show quoted text - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperately Seeking FirstCell Find !
Thanks Don, I'll take a look at this. There seems to be many ways of
skinning the cat in Excel VBA "Don Guillett" wrote in message ... It is a worksheet function x=application.match(etc -- Don Guillett SalesAid Software "Goofy" wrote in message ... What is MATCH a property or method of ? "Don Guillett" wrote in message ... why not just use MATCH to find the 1 and incorporate into an INDEX formula -- Don Guillett SalesAid Software "Goofy" wrote in message ... I am trying to search in the fist column of a range using the following code. The problem is that the first item is never searched as it allways starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to 16 and I want to find 1, it starts at cell 2 and therfore never find the 1 Any Ideas ??? Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As Presets) As Variant On Error Resume Next Dim mr As Range Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns) lookupRange = mr.Offset(0, col) End Function |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Desperately Seeking FirstCell Find !
You don't need vba to create what exists. What you were doing was creating a
new function when you could just use the functions already in existance. -- Don Guillett SalesAid Software "WhytheQ" wrote in message oups.com... "LookIn:=xlFormulas" seems to work better Rgds J On Oct 23, 1:13 pm, "Don Guillett" wrote: why not just use MATCH to find the 1 and incorporate into an INDEX formula -- Don Guillett SalesAid Software "Goofy" wrote in .. . I am trying to search in the fist column of a range using the following code. The problem is that the first item is never searched as it allways starts 'AFTER' the top left hand cell, so I have a column of numbers 1 to 16 and I want to find 1, it starts at cell 2 and therfore never find the 1 Any Ideas ??? Public Function lookupRange(ByVal rangeName As Range, key As Variant, col As Presets) As Variant On Error Resume Next Dim mr As Range Set mr = rangeName.Find(what:=key, LookIn:=xlValues, lookat:=xlWhole, SearchDirection:=xlNext, SearchOrder:=xlByColumns) lookupRange = mr.Offset(0, col) End Function- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Desperately seeking ACTIVE.WORKBOOK function for XLM | Excel Worksheet Functions | |||
DESPERATELY NEED HELP | Excel Discussion (Misc queries) | |||
Desperately Seeking Offset Formula!! | Excel Discussion (Misc queries) | |||
Desperately need help!! | New Users to Excel | |||
Desperately seeking some help! (Please) | Excel Programming |