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

fdavidgolf

New Member
Joined
Apr 23, 2002
Messages
26
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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top