RE: VB and Find worksheet function

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Neal Zimm (nealzimm_at_yahoo.com)
Date: 01/14/05 

Date: Fri, 14 Jan 2005 05:13:08 -0800

Yes, thanks gocush, but I kinda knew that, hence trying to use the work***
function. nz

"gocush" wrote:

> The vba FIND method returns a RANGE object not an integer as you have
> declared "position". Check Help in the VB Editor. search in the index for
> FindNext. In the related articles look for "Find Method"
>
> Try:
> Dim position as range
> Set position = Range("B:B").Find(what)
>
> However, upon closer reading of you code I see that what you are trying to
> do is not Find a Cell within a range; but rather Find a string within another
> string. For this you need the Instr function.
>
>
> "Neal Zimm" wrote:
>
> > am new to VB, but have progrmmed before. could not find 'find' in vb and
> > tried to use
> > work*** function following examples in VB help.
> > am trying to get one cell to work before I attack b1:bnnn range.
> > each row in the work*** one stop on a delivery route.
> > there will be one more test on same row, column D, if delcode is valid.
> > got run time error 1004.
> > 'unable to get find property of the work*** function class'
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >
> > Dim position As Integer
> > Dim delcode, validcodetable As String
> >
> > validcodetable = " 3 4 5 s/s SO <<"
> > msg1 = "Fix delivery code; blank, 3, 4, 5, s/s, SO, < are OK."
> > delcode = Trim(Range("b5"))
> >
> > ' this next statement is getting the error
> > position = Application.WorksheetFunction.Find(delcode, validcodetable, 1)
> > If position < 1 Then MsgBox msg1, , "Column B"
> >
> > End Sub
> >
> > this is all the code, don't know how to proceed.
> > Thanks.
> > Neal Z
> > --
> > Neal Z

Quantcast