[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Question about a formula

jln via OfficeKB.com

12/19/2006 12:50:00 PM

I trying to fig out what the formula is doing/

FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])"

TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C?

[2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP is
the work sheet. What is C? what is 40?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...
7 Answers

Nick Hodge

12/19/2006 12:57:00 PM

0

jln

The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B
and as it has no '[R]' refers to the whole column. (Lookup Range)

The 1 is the criteria and the second [C]40 is the range that is summed
Column 40

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"jln via OfficeKB.com" <u25956@uwe> wrote in message
news:6afe660e3e8b6@uwe...
>I trying to fig out what the formula is doing/
>
> FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])"
>
> TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C?
>
> [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP
> is
> the work sheet. What is C? what is 40?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...
>

Nick Hodge

12/19/2006 1:01:00 PM

0

jln

Just spotted I have brackets all over the place, they should be around the
number...sorry

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"Nick Hodge" <nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS> wrote in message
news:emkVw02IHHA.4112@TK2MSFTNGP04.phx.gbl...
> jln
>
> The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so
> B and as it has no '[R]' refers to the whole column. (Lookup Range)
>
> The 1 is the criteria and the second [C]40 is the range that is summed
> Column 40
>
> --
> HTH
> Nick Hodge
> Microsoft MVP - Excel
> Southampton, England
> nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
> www.nickhodge.co.uk
>
>
> "jln via OfficeKB.com" <u25956@uwe> wrote in message
> news:6afe660e3e8b6@uwe...
>>I trying to fig out what the formula is doing/
>>
>> FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[40])"
>>
>> TOEPIEXP!C i know that TOEPIEXP is the worksheet what is the C?
>>
>> [2],1,TOEPIEXP!C[40])" What is the [2] ? The 1 is the criteria. TOEPIEXP
>> is
>> the work sheet. What is C? what is 40?
>>
>> --
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...
>>
>

jln via OfficeKB.com

12/19/2006 1:17:00 PM

0

Nick that what i thought but im not using column 2 im using 6. I have
recorded for about 20 lines all using column 6 and they all say 2. SO how can
that be the column number?

Nick Hodge wrote:
>jln
>
>The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so B
>and as it has no '[R]' refers to the whole column. (Lookup Range)
>
>The 1 is the criteria and the second [C]40 is the range that is summed
>Column 40
>
>>I trying to fig out what the formula is doing/
>>
>[quoted text clipped - 5 lines]
>> is
>> the work sheet. What is C? what is 40?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...

Nick Hodge

12/19/2006 1:28:00 PM

0

jln

Using R1C1 notation, everything is relative to the range the formula is in.
you have clipped the part before

FormulaR1C1

If this was Range("C1").FormulaR1C1 then

C[2] would refer to Column E

Does that make sense?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"jln via OfficeKB.com" <u25956@uwe> wrote in message
news:6afea1e594442@uwe...
> Nick that what i thought but im not using column 2 im using 6. I have
> recorded for about 20 lines all using column 6 and they all say 2. SO how
> can
> that be the column number?
>
> Nick Hodge wrote:
>>jln
>>
>>The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so
>>B
>>and as it has no '[R]' refers to the whole column. (Lookup Range)
>>
>>The 1 is the criteria and the second [C]40 is the range that is summed
>>Column 40
>>
>>>I trying to fig out what the formula is doing/
>>>
>>[quoted text clipped - 5 lines]
>>> is
>>> the work sheet. What is C? what is 40?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...
>

jln via OfficeKB.com

12/19/2006 1:38:00 PM

0

Here is what it looks like when i record it

Range("D27").Select
ActiveCell.FormulaR1C1 = "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[34])-R[-2]C-R
[-1]C"

Nick Hodge wrote:
>jln
>
>Using R1C1 notation, everything is relative to the range the formula is in.
>you have clipped the part before
>
>FormulaR1C1
>
>If this was Range("C1").FormulaR1C1 then
>
>C[2] would refer to Column E
>
>Does that make sense?
>
>> Nick that what i thought but im not using column 2 im using 6. I have
>> recorded for about 20 lines all using column 6 and they all say 2. SO how
>[quoted text clipped - 15 lines]
>>>> is
>>>> the work sheet. What is C? what is 40?

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...

Mike Fogleman

12/19/2006 1:42:00 PM

0

The formula is displaying a relative relationship to the column that the
formula is in. For example: If your formula is in column 4 then 2 columns to
the right is column 6. 40 columns to the right is column 44.

Mike F
"jln via OfficeKB.com" <u25956@uwe> wrote in message
news:6afea1e594442@uwe...
> Nick that what i thought but im not using column 2 im using 6. I have
> recorded for about 20 lines all using column 6 and they all say 2. SO how
> can
> that be the column number?
>
> Nick Hodge wrote:
>>jln
>>
>>The [C]2 is R1C1 notation, the C is for 'Column' and the 2 is Column 2 so
>>B
>>and as it has no '[R]' refers to the whole column. (Lookup Range)
>>
>>The 1 is the criteria and the second [C]40 is the range that is summed
>>Column 40
>>
>>>I trying to fig out what the formula is doing/
>>>
>>[quoted text clipped - 5 lines]
>>> is
>>> the work sheet. What is C? what is 40?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...
>


Nick Hodge

12/19/2006 1:50:00 PM

0

jln

Then [2] refers to F, 2 columns to the right of D (D27)
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
www.nickhodge.co.uk


"jln via OfficeKB.com" <u25956@uwe> wrote in message
news:6afed105b6b42@uwe...
> Here is what it looks like when i record it
>
> Range("D27").Select
> ActiveCell.FormulaR1C1 =
> "=SUMIF(TOEPIEXP!C[2],1,TOEPIEXP!C[34])-R[-2]C-R
> [-1]C"
>
> Nick Hodge wrote:
>>jln
>>
>>Using R1C1 notation, everything is relative to the range the formula is
>>in.
>>you have clipped the part before
>>
>>FormulaR1C1
>>
>>If this was Range("C1").FormulaR1C1 then
>>
>>C[2] would refer to Column E
>>
>>Does that make sense?
>>
>>> Nick that what i thought but im not using column 2 im using 6. I have
>>> recorded for about 20 lines all using column 6 and they all say 2. SO
>>> how
>>[quoted text clipped - 15 lines]
>>>>> is
>>>>> the work sheet. What is C? what is 40?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-programmin...
>