Entering Formulas in VBA
 

Hi
I have what I thought was a simple problem, but it isn't. Within a VBA
subroutine I choose a cell address, say $B$10 and wish to place a formula
in it - say "=$A10-$A$9". (Not manually, but within VBA). can anyone help??

thanks in advance.

Brian Wild

Entering Formulas in VBA
 

from Immediate window:
activesheet.range("$B$10").formula = "=$A$10-$A$9"
HTH

"Brian Wild" wrote in message
...
Hi
I have what I thought was a simple problem, but it isn't. Within a VBA
subroutine I choose a cell address, say $B$10 and wish to place a formula
in it - say "=$A10-$A$9". (Not manually, but within VBA). can anyone
help??

thanks in advance.

Brian Wild

Entering Formulas in VBA
 

Thanks for the remarkably quick response. However, it indicates that I
have not expressed the problem clearly enough. I don't know in advance
that the target cell will be (e.g.) $B$10 - but once it has been
determined the desired formula to be entered is calculated using the
details of its address. I can set up a string variable to represent the
formula, but can't see how to get VBA to accept this as a formula in the
chosen cell. I could of course enter the formula manually via a dialog
box - but there are several such cases to be handled and it would become
laborious.

Brian Wild

*** Sent via Developersdex http://www.developersdex.com ***

Entering Formulas in VBA
 

Brian
If the formula is suppose to work on cells that are always in a "relative"
position to the target cell, you can use the .formulaR1C1 function.
ie ActiveCell.FormulaR1C1 = "=R[-2]C[-1]+R[-1]C[-1]"


"Brian Wild" wrote:


Thanks for the remarkably quick response. However, it indicates that I
have not expressed the problem clearly enough. I don't know in advance
that the target cell will be (e.g.) $B$10 - but once it has been
determined the desired formula to be entered is calculated using the
details of its address. I can set up a string variable to represent the
formula, but can't see how to get VBA to accept this as a formula in the
chosen cell. I could of course enter the formula manually via a dialog
box - but there are several such cases to be handled and it would become
laborious.

Brian Wild

*** Sent via Developersdex http://www.developersdex.com ***

Entering Formulas in VBA
 

Maybe I've missed the point, but how bout this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Target.Formula = "=$A$10-$A$9"
Cancel = True
End Sub


"Brian Wild" wrote in message
...

Thanks for the remarkably quick response. However, it indicates that I
have not expressed the problem clearly enough. I don't know in advance
that the target cell will be (e.g.) $B$10 - but once it has been
determined the desired formula to be entered is calculated using the
details of its address. I can set up a string variable to represent the
formula, but can't see how to get VBA to accept this as a formula in the
chosen cell. I could of course enter the formula manually via a dialog
box - but there are several such cases to be handled and it would become
laborious.

Brian Wild

*** Sent via Developersdex http://www.developersdex.com ***