Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Location
    Washington USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combining cells in Excel (Excel 2000)

    Hello all,

    This is probably an easy one but my feeble brain is having trouble so here goes...

    I have a row of data that describes the cost to purchase or manufacture a part. The data is organized into columns such as quantity, material cost, manufacturing hours, etc. The contents of these cells are simple number, not formulas. What I need to do is convert the numbers in the material cost and other columns to be formulas that are the present value times the quantity. The objective is that when the quantity is changed all of the other cells in the row will change accordingly.

    As their are hundreds of rows that need to be updated I am hoping for some enlightenment on a macro to automate the process.

    All help is gratefully acknowledged.

    John Davis

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Combining cells in Excel (Excel 2000)

    John

    OK here is an example, if you had given us the columns where the data is, very usefull, we could have given you the exact formula.

    Say you have hours worked in column A and Salary Rate in Column B in Column C you want the formula to calculate gross pay which is Hours worked X Salary rate per hour

    =A2*B2 will multiply A2 by B2, then you can fill down this formula and you are done. To fill down you look at the lower right hand corner of the cell when it is selected and you will see a black square. Grab this black square and pull down as far as you have data. Make sure you hover the mouse pointer until the cursor changes into a black Plus sign.

    A macro is not needed here, unless you have something in mind that I missed, very possible.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    Nov 2001
    Location
    Washington USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    Thanks for the reply; but it's not quite what I'm looking for.

    In the attached sample file you can see what I have to work with. What I need is to be able to enter a new value in the Quantity column (Column [img]/forums/images/smilies/cool.gif[/img] and have all of the other columns update accordingly. So if the quantity becomes 2 all of the values in the other columns double.

    Put another way, cell E5 is currenlty "80". What I would like it to be is "=80*E5". I'm looking for a way to do this automagically as there are hundreds of rows that need to be updated in this way.

    John Davis
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Combining cells in Excel (Excel 2000)

    John

    Check the Red Cells for the formula.

    Then Check the Fill handle behavior. This will let you do this "hundreds of rows" in a jiffy. Just grab it and pull down as much as you can, work it slow first so that you see what is going on.

    Wassim
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    New Lounger
    Join Date
    Nov 2001
    Location
    Washington USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    Thanks again Wassim. Maybe I'm not explaining this clearly. I want to REPLACE the numbers that are currently in the cells with the formula.

    John

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    The VBA routine below will take all of the cells in the current selection and replace ther contents with a formula that multiplies the current contents by the cell in column B in the same row. Is that what you are looking for?

    <pre>Public Sub InsertFormula()
    Dim oCell As Range
    For Each oCell In Selection
    oCell.Formula = "=" & oCell.Value & "*B" & oCell.Row
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Combining cells in Excel (Excel 2000)

    John

    Now that I have a sample, how about telling me what numbers in what cells do you want to replace.
    For example, cell D5 = 200 what happens.

    Did you check the effect of the Fill Handle?

    Why do you have 3 blank rows on the top?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Combining cells in Excel (Excel 2000)

    John

    Now that I have a sample, how about telling me what numbers in what cells do you want to replace.
    For example, cell D5 = 200 what happens to that cell?

    Did you check the effect of the Fill Handle?

    Why do you have 3 blank rows on the top?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  9. #9
    New Lounger
    Join Date
    Nov 2001
    Location
    Washington USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    Yep. This is what I am trying to do. The code that you sent works as long as there are values in every cell; which is MOSTLY, but not alwyas true. I can stick zeros into the empty cells and it will work great.

    Thanks!

    John

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    If you have empty cells in the range, then you could change the macro to this:

    <pre>Public Sub InsertFormula()
    Dim oCell As Range
    For Each oCell In Selection
    If oCell.Value <> "" Then
    oCell.Formula = "=" & oCell.Value & "*B" & oCell.Row
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

  11. #11
    New Lounger
    Join Date
    Nov 2001
    Location
    Washington USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    AWESOME!! Thank you!

    John

  12. #12
    New Lounger
    Join Date
    Nov 2001
    Location
    Washington USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    This little macro has been working great. I need to take it to the next level and automate it so that it executes whenever the content of a cell in a defined range changes. How might I do this?

    Thanks again.

    John

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combining cells in Excel (Excel 2000)

    If the "defined range" is named DefinedRange, then something like this should do what you want:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Worksheets("Sheet1").Range("DefinedRange")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("DefinedRange"))
    If oCell.Value <> "" Then
    Application.EnableEvents = False
    oCell.Formula = "=" & oCell.Value & "*B" & oCell.Row
    Application.EnableEvents = True
    End If
    Next oCell
    End If
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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