General Question on one line of code - parentheses and quotation marks

[Jim Berglund]

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]

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

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

 

[Jim Berglund]

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

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

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]

Ah, that's what you meant by curved brackets???

These are used to show that you're processing a constant array.

{"a","b","C"}
is a 3 element array of constants.

You can use them in formulas that you type instead of putting the values in a
range of cells.

They are also used by excel to indicate that the formula is an array formula.
In this case, you don't actually type the {}'s, you use ctrl-shift-enter to
enter the formula. Excel will wrap the formula with those {}'s.

If you want to learn more about these array formulas:

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

And Chip Pearson has notes here:
http://www.cpearson.com/excel/ArrayFormulas.aspx

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

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

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