Calculate cell formula changed by VB

  • Thread starter Thread starter Nicolae Panait
  • Start date Start date
N

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
 
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
 
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.
Click to expand...

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
 
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
 
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
 
Hi Nicolae,
=SUM('monthlyreport_2003_09.xls]MASTER'!B$8:B$11)
Click to expand...

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
 
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
 
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
 
Back
Top