search value in a worksheet

Posted by Artem on April 10, 2001 10:53 AM

hi,

is there a VBA procedure to search for a value in a worksheet (equivalent to menu commands Edit>Find or Ctrl+F) and return a value from a cell just below it?

THANKS!
ps. for XL97. vollups, hlookups don't work.

Posted by anon on April 10, 2001 5:23 PM

You can get the VBA code for Find by using the macro recorder.
Code for returning the value of the cell below the active cell :
Range("A1")=ActiveCell.Offset(1,0)

Posted by David Hawley on April 10, 2001 10:06 PM

Hi Atrtem

You can use Worksheet Functions in VBA, but they must be preceded with:

WorksheetFunction.
The your WantedFunction

To use the find method use this:

Sub TryThis()
Dim ReturnValue As String
ReturnValue = Sheets("Sheet1").Cells.Find _
(What:="Dog", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, MatchCase:=False).Offset(1, 0)

MsgBox ReturnValue

End Sub

I also suggest you select the word "Find" and Push F1 while in the VBE. Then read up on it's arguments.

Dave

OzGrid Business Applications