Thanks, Dave - enlightening!
How about the curly brackets? { } When/Why are they used?
Jim

"Dave Peterson" wrote in message
...
#1. When you're typing a formula into a cell in excel, you can use a
formula
like this into C1:
=a1+b1
when the column headers are letters.

If the column headers are numbers, then the formula in C1 would be:
=RC[-2]+RC[-1]

The different style for those cell references for the way you type the
formula
is determined by this setting:

Tools|Options|General Tab|Check or uncheck R1C1 Reference style.

The =a1+b1 formula is in A1 reference style.
The =rc[-2]+rc[-2] is in R1C1 reference style.

========
But you can write code in the VBE for your macro that uses either
reference
style (but not a mixture).

You'd use:

activesheet.range("c1").formula = "=a1+b1"
activesheet.range("c1").formulaR1C1 = "=RC[-2]+RC[-1]"

When the macro runs, excel will plop the formula into the cell. But it'll
be
displayed in the formula by that setting (R1C1 reference style). You and
your
code don't need to worry about the setting.

Your expression was written as a .formulaR1C1 string.

The R[0]C[-2] portion says:
Stay on the same row (0 in those []'s) as the cell with the formula.
But use the cell two columns to the left (-2 in the []'s).

The cell that would be used depends on what cell the formula gets plopped
into.

You can add some test formulas to a worksheet and toggle this R1C1
reference
style setting to see how your formulas get converted from one reference
style to
the other.

#2. Those ()'s are required for some functions and other times, they're
used to
make the meaning of the expression less ambiguous to the human eye.

=sum(a1:a10)
requires the ()'s.

=a1+b1/c2*d3
doesn't need any ()'s
but I would find this easier to understand:
=a1+((b1/c2)*d3))

I wouldn't need to worry about what was intended and how excel will use
its
order of operator precedence to determine the result.

#3. When you build a string expression in code and that string includes
double
quotes, you have to double them up.

#4. Yep.

#5. the =find(...), is going to result in either a number (if it's found)
or an
error if it's not found. The expression used will result in an array of
those
numbers/errors.

So =isnumber(find(...)) will result in True's if it was found or false if
it
wasn't. (Errors are not numbers.) The expression used will result in an
array
of those True/False values.

=match(true,(thatarrayoftrue/false),0)
will pick out the position of the first match in that array.





Jim Berglund wrote:

I don't understand the following line of code.

"=MID(R[0]C[-2],INDEX(FIND({""(403"",""(587""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(403"",""(587""},R[0]C[-2])),0)),14)"

1. Why and when should square brackets be used?
2. Why & where should curved brackets be used?
3. Why are there double sets of quotation marks around the (403 and (587
4. I've never used the R[0]C[-2] nomenclature. Is this example
designating a
cell location that is on the same row bur two columns left?
5. I can't find reference to MATCH(TRUE,ISNUMBER. What is this doing?

Thanks to anyone that takes the time to answer this.

Jim Berglund

--

Dave Peterson