Prev: RowSource property in cbox
Next: I need VBA Help for Excel spreadsheet -- Please!
From: Jim Berglund on 21 May 2010 13:48
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

From: Dave Peterson on 21 May 2010 14:46
#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
 | 
Pages: 1
Prev: RowSource property in cbox
Next: I need VBA Help for Excel spreadsheet -- Please!