VBA Formula Syntax

[Guest]

Excel 2000
I'm new to using VBA with Excel.
What I am trying to do is change the formula in a cell based on which cell
is active (has focus) Such As:


Private Sub Worksheet_Selection Change( byval target as range)
If Target.Address = "$A$1" Then
Range("E1") = (B1+C1)/D1
Elseif Target.Address = "$A$2" Then
Range("E1") = (B2+C2)/D2
End If

The part I'm having problem with is the syntax for the formula:
Range("E1") = (B1+C1)/D1

Any help appreciated!
Dale

 

[Soo Cheon Jheong]

Dale,


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$A$1" Then
Range("E1").Formula = "=(B1+C1)/D1"
ElseIf Target.Address = "$A$2" Then
Range("E1").Formula = "=(B2+C2)/D2"
End If

End Sub

--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

 

[Bob Phillips]

Hi Dale,

Try this

Private Sub Worksheet_Selection Change( byval target as range)
If Target.Address = "$A$1" Then
Range("E1").Formula = "=(B1+C1)/D1"
Elseif Target.Address = "$A$2" Then
Range("E1").Formula = "=(B2+C2)/D2"
End If


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Harald Staff]

Hi Dale

You're pretty close:

Range("E1").Formula = "=(B1+C1)/D1"

HTH. Best wishes Harald

 

[Don Guillett]

try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
With Target
Range("e1") = (.Offset(0, 1) + .Offset(0, 2)) / .Offset(0, 3)
End With
End Sub

 

[Dana DeLouis]

Just another idea. Not sure if you want to insert a formula, of just the
value.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(0, 4).FormulaR1C1 = "=(RC[-3]+RC[-2])/RC[-1]"
End If
End Sub