implement test="=(SUM(R[-181]C:R[-170]C)-string1)/string2 "inVBA?

How do I implement: test="=(SUM(R[-181]C:R[-170]C)-string1)/string2 "in excel
VBA ?

Seems like VBA understands test="=SUM(R[-181]C:R[-170]C)-string1 but not
when dividing by one more string

HELP

Assuming strings resolve to numerical values....

test = Application.WorksheetFunction.Sum(string1 + string2) / string3


--
Cheers
Nigel



"Monika" <Monika@discussions.microsoft.com> wrote in message
news:1F8CAACA-0007-47D2-8F6F-570A0C6F0802@microsoft.com...
> How do I implement: test="=(SUM(R[-181]C:R[-170]C)-string1)/string2 "in
excel
> VBA ?
>
> Seems like VBA understands test="=SUM(R[-181]C:R[-170]C)-string1 but not
> when dividing by one more string
>
> HELP

I think you would get a better answer if you gave a specific example that
works and a specific example that does not work. Also, once assigned to the
variable "test", what do you intend to do with "test".

--
Regards,
Tom Ogilvy


"Monika" wrote:

> How do I implement: test="=(SUM(R[-181]C:R[-170]C)-string1)/string2 "in excel
> VBA ?
>
> Seems like VBA understands test="=SUM(R[-181]C:R[-170]C)-string1 but not
> when dividing by one more string
>
> HELP

This works:
teststreng = "=SUM(R[" & s - 183 & "]C:R[" & s - 172 & "]C)-" & Costs &""

This does not work:
teststreng = "=(SUM(R[" & s - 183 & "]C:R[" & s - 172 & "]C)-" & Costs &
")/" & Depreciation & ""


This is what teststreng is for( just pasting the value in to cells):
Worksheets("VaR").Cells((p), (q)).Activate ' p and q are counting varaibles
ActiveCell.FormulaR1C1 = teststreng


But it does accept depreciation and assigns it the right number, teststreng
accepts it but the trouble is in ActiveCell. Formula part.

Thank you:-)

"Tom Ogilvy" wrote:

> I think you would get a better answer if you gave a specific example that
> works and a specific example that does not work. Also, once assigned to the
> variable "test", what do you intend to do with "test".
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Monika" wrote:
>
> > How do I implement: test="=(SUM(R[-181]C:R[-170]C)-string1)/string2 "in excel
> > VBA ?
> >
> > Seems like VBA understands test="=SUM(R[-181]C:R[-170]C)-string1 but not
> > when dividing by one more string
> >
> > HELP

demo'd from the immediate window:
costs = 100
Depreciation = .5
s = 200
activeCell.FormulaR1C1 = "=(SUM(R[" & s - 183 & "]C:R[" & s - 172 & "]C)-" &
Costs & ")/" & Depreciation & ""
? activecell.Formula
=(SUM(A18:A29)-100)/0.5
? activecell.FormulaR1C1
=(SUM(R[17]C:R[28]C)-100)/0.5

so that formula worked fine for me as long as the variables were defined.

--
Regards,
Tom Ogilvy


"Monika" wrote:

>
> This works:
> teststreng = "=SUM(R[" & s - 183 & "]C:R[" & s - 172 & "]C)-" & Costs &""
>
> This does not work:
> teststreng = "=(SUM(R[" & s - 183 & "]C:R[" & s - 172 & "]C)-" & Costs &
> ")/" & Depreciation & ""
>
>
> This is what teststreng is for( just pasting the value in to cells):
> Worksheets("VaR").Cells((p), (q)).Activate ' p and q are counting varaibles
> ActiveCell.FormulaR1C1 = teststreng
>
>
> But it does accept depreciation and assigns it the right number, teststreng
> accepts it but the trouble is in ActiveCell. Formula part.
>
> Thank you:-)
>
> "Tom Ogilvy" wrote:
>
> > I think you would get a better answer if you gave a specific example that
> > works and a specific example that does not work. Also, once assigned to the
> > variable "test", what do you intend to do with "test".
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Monika" wrote:
> >
> > > How do I implement: test="=(SUM(R[-181]C:R[-170]C)-string1)/string2 "in excel
> > > VBA ?
> > >
> > > Seems like VBA understands test="=SUM(R[-181]C:R[-170]C)-string1 but not
> > > when dividing by one more string
> > >
> > > HELP

If there are decimal separators in Depreciation, they should be the International ones, not your local ones.
Or use the FormulaR1C1Local property instead.

--
Kind regards,

Niek Otten


"Monika" <Monika@discussions.microsoft.com> wrote in message news:DD65C067-3AFD-4315-B45E-8E12FCEE7E37@microsoft.com...
>
> This works:
> teststreng = "=SUM(R[" & s - 183 & "]C:R[" & s - 172 & "]C)-" & Costs &""
>
> This does not work:
> teststreng = "=(SUM(R[" & s - 183 & "]C:R[" & s - 172 & "]C)-" & Costs &
> ")/" & Depreciation & ""
>
>
> This is what teststreng is for( just pasting the value in to cells):
> Worksheets("VaR").Cells((p), (q)).Activate ' p and q are counting varaibles
> ActiveCell.FormulaR1C1 = teststreng
>
>
> But it does accept depreciation and assigns it the right number, teststreng
> accepts it but the trouble is in ActiveCell. Formula part.
>
> Thank you:-)
>
> "Tom Ogilvy" wrote:
>
>> I think you would get a better answer if you gave a specific example that
>> works and a specific example that does not work. Also, once assigned to the
>> variable "test", what do you intend to do with "test".
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Monika" wrote:
>>
>> > How do I implement: test="=(SUM(R[-181]C:R[-170]C)-string1)/string2 "in excel
>> > VBA ?
>> >
>> > Seems like VBA understands test="=SUM(R[-181]C:R[-170]C)-string1 but not
>> > when dividing by one more string
>> >
>> > HELP