Function is not functioning

[cmoore]

Why wont this work? Ideas on how to make it work?


Function Molecular_Wt(Component)

Range("C2:AI4").Select

Selection.Find(What:=Component, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
_
MatchCase:=False).Activate

ActiveCell.Offset(0, 1).Select

Molecular_Wt = ActiveCell.Value

End Functio

 

[Nikos Yannacopoulos]

Try to run in debug > step into (or F8). This will run the
code line by line, and help you locate the problem.

Nikos Y. (nyannaco at in dot gr)

 

[BrianB]

I think this line should be changed :-

Molecular_Wt = ActiveCell.Value
to
ActiveCell.Value = Molecular_Wt

 

[BrianB]

Sorry ! just realised that we cannot use Find in a function.
I think your best plan would be to convert your data to a lookup tabl
and make your function something like :-

'------------------------------------------------
Function Molecular_Wt(component)
Molecular_Wt = Application.WorksheetFunction.VLookup(component
Range("A1:B100"), 2, False)
End Function
'----------------------------------------------------

 

[cmoore]

Thank you BrianB. That solution works. Although I do think the Fin
will work in a Function if it is formated correctly.

This was in an example in the Excel Help Files... But it doesn't wor
either?!@#@?

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="Bromine", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Functio

 

[Dave Peterson]

When the function is called from a worksheet cell, then Find method doesn't work
in xl2k or previous.

It works fine in xl2002. (don't know if it was changed again in xl2003).