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

[Monika]

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

 

[Nigel]

Assuming strings resolve to numerical values....

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

 

[Tom Ogilvy]

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

 

[Monika]

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

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

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

 

[Tom Ogilvy]

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

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

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

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

 

[Niek Otten]

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

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

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

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