VB and Find worksheet function

[Guest]

am new to VB, but have progrmmed before. could not find 'find' in vb and
tried to use
worksheet function following examples in VB help.
am trying to get one cell to work before I attack b1:bnnn range.
each row in the worksheet 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 worksheet 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

 

[Tom Ogilvy]

position = Application.WorksheetFunction.Find(delcode, validcodetable, 1)

could be

position = Instr(1,validcodetable,delcode,vbTextCompare)

 

[Nigel]

Since the FIND function returns an error value if it fails to find the
required search value you should place an error trap before the command..

On Error Resume Next
position = Application.WorksheetFunction.Find(delcode, validcodetable,
1)

Hope this helps

 

[Guest]

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.

 

[Tom Ogilvy]

application.Worksheetfunction.Find isn't the Find method of the Range
object. It is the worksheet function equivalent (somewhat) to the vba instr
function.

 

[Guest]

worth the admission price, many thanks.

Since the FIND function returns an error value if it fails to find the
required search value you should place an error trap before the command..

On Error Resume Next
position = Application.WorksheetFunction.Find(delcode, validcodetable,
1)

Hope this helps

 

[Guest]

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

 

[Guest]

Superb; and I looked pretty hard in the docum. I could have missed a
reference to instr when I was looking for 'find' stuff and string stuff.
Thanks.

 

[Guest]

Tom -
Maybe you could shed some light on a conceptual problem I'm having trying
to learn vb 'on the fly'. I've done a fair bit of programming in the batch
world.
When I see a range bn:bnnn in some of the vb code examples, I don't know
'how' that is processed, i.e., I'm used to thinking in array terms where for
a 20 row array I would do something like: for index = 1 to 20
if yatta yatta array(index) is yatta yatta
next index
to address all the data elements in that row.


Can it be that bn:bnnn is doing that 'behind the scenes'?
If so, that's powerful stuff.
Thanks again, Neal

 

[Tom Ogilvy]

Range("b1:b200").Formula = "=Sum(C1:J1)"

does that - equivalent to

for i = 1 to 2000
cells(i,2).Formula = "=Sum(C" & i & ":J" & i & ")"
Next

and Range("B1:B200").ClearContents

is similar.

Those things you can do manually by selecting a range applying a change to
all cells at once usually similarly able to be accomplished as one command
in Excel VBA.

In other cases you have to loop

for each cell in Range("B1:B200")
cell.Value = Cells(int(rnd()*100+1),5).Value
Next

for example. (there are clever ways to do this particular example without
looping, but this is for illustration).

 

[Tom Ogilvy]

It executes immediately. It is the same as manually selecting B1:B200 (with
B1 as the active cell), then going to the formula bar, entering =Sum(C1:J1)
and doing Ctrl + Enter rather than Enter. This action simultaneously enters
the formula in all cells in B1:B200. Since the cell references are
relative, the formula will be adjusted in each cell automatically, so B38
will hold the formula =Sum(C38:J38)

Again, the entry is almost instantaneous, so I am not sure what you are
asking about identifying or operating on B38.

 

[Tom Ogilvy]

Just one additional thought, if you have coded some worksheet level events,
perhaps you are getting some type of interaction between the entry of the
formula and the firing of your event code.

 

[Guest]

re: event firing, nothing nearly that sophisticated. My b38 text was a poorly
written attempt to give an example of not being able to access an individual
cell in a 'range' execution which I should have been able to get from your
explanation. Thanks.