find function

B

Byers

I'm trying to do a similair process to the lookup function, but the dat
isn't sorted, and due to some cross sheet links, I am unable to sor
the data. So I'm looking for a function to find the correct company i
one range and then return the correct multiplier in the correspondin
range... I will comment my function with missing code

Function FindReturn(Company)

Set Foundcell = ActiveSheet.Range("B62:B69").Find_
What:="end", LookIn:=xlValues, LookAt:=xlWhole)
If Foundcell Is Nothing Then
FindReturn = 1
Else
r = Foundcell.Row
' FindReturn = value in cell with that row and in column C

End Function

I don't know if there are errors in my current code, but I don't kno
visual basic so what I have is what I've learned from working with it.
I think it should work if I can just figure out how to return the valu
of the cell
 
A

AlfD

Hi!

FWIW here's one which seems to do the job you want:

Function FindReturn(Company As Variant)
Dim foundrow As Long
On Error GoTo errorline
foundrow = Cells.Find(Company, Range("B61"), xlFormulas
xlByColumns, xlNext).Row
FindReturn = Range("C" & foundrow).Value
errorline:
End Function

1. The return you were seking is Range("C" & r).Value
2. Mine returns 0 if it fails (e.g. company name not there). You coul
add a line at line 3 : FindReturn=1 to set a default value.

Al
 
B

Byers

I tried copying yours directly and it didn't work, it always returned 0
then I tried altering your code some, and then my code and it stil
wasn't working, here is my final unworking code:

Function FindReturn(Company As Variant)

Set Foundcell = ActiveSheet.Range("B62:B69").Find_
(What:=Company, LookIn:=xlFormulas, LookAt:=xlWhole)
If Foundcell Is Nothing Then
FindReturn = 1

Else
r = Foundcell.Row
FindReturn = Cells(r, 3).Value
End If

End Function

it seems as though it always enters the if statement to the foundcel
is always nothing. could if have to do with the fact that the valu
being taken in is coming from another cell. i.e. when the functio
gets called it's called like: FindReturn(E23
 
A

AlfD

Hi!

My routine still works for me.

Are you putting it in a separate module (not the worksheet module)?

Are you saving/closing the workbook after putting the function in i
and then reloading?

Is the function appearing on the list of available (user defined
functions when you use the function wizard?

Al
 
B

Byers

yes, yes, yes

Function FindReturn(Company As Variant)

Dim foundrow As Long
On Error GoTo errorline
'the range you have should be more like "B62:B69" I think
'since yours is only one cell won't it check the entire sheet
foundrow = Cells.Find(Company, Range("B61"), xlFormulas,_
ByColumns, xlNext).Row
'what I use is cells(row,column).value, and the row and column
'values are numbers
FindReturn = Range("C" & foundrow).Value
errorline:

End Function

perhaps it is always producing an error and therefore going straight t
the bottom, could protections have anything to do with the problem? m
workbook and sheet are both protected, but for now the sheet has bee
unprotected while I work on it
 
A

AlfD

Hi!

Yes: it's almost certainly throwing an error. Error 9 certainl
appears.

My function makes the (unwarranted?) assumption that column B contain
only the company names and nothing else (including blanks).

It looks at the whole column.

Al
 
D

Dave Peterson

How are you calling your function--from a formula in a worksheet cell or from a
macro?

xl2002 allows you use Find in a function from a worksheet cell, but previous
versions wouldn't.

(And it only matters if it originated in a cell--if you had a worksheet formula
call a function that called your function, excel still knows and won't allow
it!)

If it's from a worksheet formula, why not just:

=vlookup(a1,b62:c62,2,false)
or even
=vlookup("*"&a1&"*",b62:c62,2,false)

wrap it with some error checking (maybe?):
=if(iserr(vlookup(...)),"missing",vlookup(...))
 
Top