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