Calculate cell formula changed by VB

[Nicolae Panait]

Hi guys,
Ever encountered this problem in displaying the formula result afte
modifying the cell formula with a VB code?
The code is trivial:

Sub SetFormulas()
Dim startRow As Integer
Dim endRow As Integer
startRow = InputBox("Enter start row to be processed", "Pars
formulas")
endRow = InputBox("Enter end row to be processed", "Parse formulas")
For i = startRow To endRow
ActiveSheet.Range("I" & i).Formula = ActiveSheet.Range("H" & i)
ActiveSheet.Range("M" & i).Formula = ActiveSheet.Range("L" & i)
Next i
ActiveSheet.Range("I:M").Calculate
End Sub

Still, the modified cells display formula string and not the result!
Any idea?!
Many thanks in advance
Nicola

 

[Frank Kabel]

Hi
try
ActiveSheet.Range("I" & i).Formula = "=H" & i
ActiveSheet.Range("M" & i).Formula = "=L" & i

--
Regards
Frank Kabel
Frankfurt, Germany

"Nicolae Panait >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Jan Karel Pieterse]

Hi Nicolae,

Still, the modified cells display formula string and not the result!
Any idea?!

Maybe the cells that are receiving the formulas are formatted as text?

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Nicolae Panait]

Thanks guys,
Unfortunately it didn't worked.
More info:
- Version:Excel 97
- Modified Cell Formating: General
- Link Cell Formating: General
It only shows the value if manualy entering the formula bar and hi
<Enter>, which becomes a real problem if working with many cells.

I wanted to use the VB because I want to address and SUM cell value
belonging to different documents. I know the format of the document
name (contain the year and month) so within my worksheet I want to ge
those values by looking at my Date column.
May be there is another simpliest way.

Cheers,
Nicola

 

[Jan Karel Pieterse]

Hi Nicolae,

Unfortunately it didn't worked.
More info:
- Version:Excel 97
- Modified Cell Formating: General
- Link Cell Formating: General
It only shows the value if manualy entering the formula bar and hit
<Enter>, which becomes a real problem if working with many cells.

Could you post the content of a cell that is in column H?
I assumed columns H and L contained a formula as a string and you want
that formula to appear in the other columns.

Maybe you need the FormulaLocal propery instead of the Formula
property.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Charles Williams]

Maybe this:

ActiveSheet.Range("I" & i).Formula = ActiveSheet.Range("H" & i)
ActiveSheet.Range("M" & i).Formula = ActiveSheet.Range("L" & i)

should be this?

ActiveSheet.Range("I" & i).Formula = ActiveSheet.Range("H" & i).Formula
ActiveSheet.Range("M" & i).Formula = ActiveSheet.Range("L" & i).Formula


regards

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Nicolae Panait]

Sure Jan,
Here are the relevant info:
Cell:B Content=Sep-2003
Cell:H Formula="="&F3&YEAR($B3)&"_"&RIGHT("0"&MONTH($B3),2)&G3
Expected Value=SUM('monthlyreport_2003_09.xls]MASTER'!B$8:B$11)
Cell:F_First_part_of_formula Content=SUM('monthlyreport_
Cell:G_Last_part_of_formula Content=.xls]MASTER'!B$8:B$11)

Another way I'm thinking to try is to do the SUMs inside the VB an
only set the cell value instead of the formula.
This way the verification of the references will be hiden to athe
users (not what I want)
Regards,
Nicola

 

[Jan Karel Pieterse]

Hi Nicolae,

=SUM('monthlyreport_2003_09.xls]MASTER'!B$8:B$11)

There is a syntax error in the formula, I guess it should become:

=SUM('[monthlyreport_2003_09.xls]MASTER'!B$8:B$11)

So you need to adjust the concatenating formulas in column H to include
another "[".

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Nicolae Panait]

Hi Jan,
You are right. I've adapted the info from my document (don't want t
publish company's info) and I have mistaken the formula. In reality i
is as you said, with the brackets.
I don't think it is a problem in the formula because it displays th
value if I go, after running the code, in the formula bar and hi
<Enter> without changing anything (remember my first post?).

PS: how do you connect to this newsgroup? What's its address? I've bee
trying with my outlook client with no effect!

Cheers
Nicola

 

[Tom Ogilvy]

news://msnews.microsoft.com/microsoft.public.excel.programming

 

[Jan Karel Pieterse]

Hi Nicolae,

So try this then:


Sub SetFormulas()
Dim startRow As Integer
Dim endRow As Integer
startRow = InputBox("Enter start row to be processed", "Parse
formulas")
endRow = InputBox("Enter end row to be processed", "Parse formulas")
For i = startRow To endRow
ActiveSheet.Range("I" & i).Formula = ActiveSheet.Range("H" & i)
ActiveSheet.Range("M" & i).Formula = ActiveSheet.Range("L" & i)
ActiveSheet.Range("I" & i).Formula = ActiveSheet.Range("I" & i).Formula
ActiveSheet.Range("M" & i).Formula = ActiveSheet.Range("M" & i).Formula
Next i
ActiveSheet.Range("I:M").Calculate
End Sub

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Nicolae Panait]

THANK YOU!
It works!

Nicola