How to add formula to Column E when Column B is entered

[fdavidgolf]

When the Asset Description is changed, I want Column E (Extended Price) to be a formula Unit Price * Quantity.

There could be any number of assets to enter.

Earlier John McGraw sent in a solution for Kourada on 4-23. This did not work for me.

The formula field is protected; however, I have tried it unprotected.

I thought it was working at first, now it doesn't.

The data starts on row 14.

Here is my code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$B$14" Then
If Target.Address.Value < "$B$64000" Then
FillExtPrice
End If
End If

End Sub

[jrnyman]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range, Cancel as Boolean)

If Target.Column= 2 Then
Target.Offset(0,3).Formula = "PutYourFormulaHere"
End If

End Sub

[fdavidgolf]

That should work, but I can't get it yet.
Here is what I tried. I also tried using Columns(3) * Columns(4). Also tried just putting text there.

Trying this with unprotected sheet. But the sheet will be protected when finished.

Private Sub Worksheet_Change(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 2 Then
Target.Offset(0, 3).Formula = "=Target.Offset(0,1) * Target.Offset(0,2)"

End If

End Sub

[jrnyman]

Go with this:

Private Sub Worksheet_Change(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 2 Then
Target.Offset(0, 3).FormulaR1C1 = "=RC[1]* RC[2]"
End If

End Sub


As a rule of thumb, when you're having problems entering formulas with a macro, stop the macro after it enters the formula, and then go manually see what formula it entered. You'd probably be surprised with the way you had it.


hth

[fdavidgolf]

The WorksheetChange doesn't work, but If I run this, it does.

Sub FillExtPrice()

'Target.Offset(0, 3).FormulaR1C1 = "=RC[1]* RC[2]"
ActiveCell.Offset(0, 3).FormulaR1C1 = "=RC[1]* RC[2]"

End Sub


Private Sub Worksheet_Change(ByVal Target As Range, Cancel As Boolean)

If Target.Column = 2 Then
FillExtPrice
End If

End Sub

[fdavidgolf]

I used a worksheet change and I get in the routine but Target.Column does not work.
What do I use to address the cells?

Private Sub Workbook_SheetChange(ByVal AssetInventory As Object, _
ByVal Source As Range)