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
--
Neal Z

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

could be

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


--
Regards,
Tom Ogilvy

"Neal Zimm" wrote in message
...
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
--
Neal Z

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

--
Cheers
Nigel



"Neal Zimm" wrote in message
...
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
--
Neal Z

worth the admission price, many thanks.

"Nigel" wrote:

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

--
Cheers
Nigel



"Neal Zimm" wrote in message
...
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
--
Neal Z

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
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
--
Neal Z

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

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
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
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
--
Neal Z

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
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
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
--
Neal Z

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" wrote:

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

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
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
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
--
Neal Z

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).

--
Regards,
Tom Ogilvy


"Neal Zimm" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
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
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
--
Neal Z

I owe you lots of beers; you live anywhere near Atlanta?
Seriously tho', your explanation and examples, which I have
already executed have cleared away considerable fog.
For most of what I have to do, without doubt, the looping method will be best.

What's even better, is that now I can put into the 'online' worksheet code,
e.g. selection change, what it is best suited for. Next, by making a
'general' macro out of vb code, rather than 'tape recording' mouse clicks, I
think I can do exactly what I want.

Your examples were terrific.
Warmest regards, and many thanks.
Neal

"Tom Ogilvy" wrote:

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).

--
Regards,
Tom Ogilvy


"Neal Zimm" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy

"gocush" /delete wrote in message
...
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
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
--
Neal Z

Gonna beat a dead horse here, but just to make
sure I understand, in your example:

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

when the above is actually executing, I cannot
identify, nor operate on, cell b38 easily,
if at all.

Thanks again.
NZ

Yes, thanks gocush, but I kinda knew that, hence trying to use the worksheet
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
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
--
Neal Z