Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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





  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •